mysql firstrow=2_按mysql查询的复杂分组

科技前沿 • 2025-01-17 18:52 • 阅读 91

大家好，我是讯享网，很高兴认识大家。

在SQL中这样做并不容易,因为它依赖于顺序,而SQL不太适合这样做。

这个查询相当笨拙,所以我将首先完整地给出它,然后给出一个分解图,显示它是如何组合在一起的。

SELECT @rownum:=@rownum+1 AS id, t.user_id, type, date, urls FROM

(SELECT MIN(ID) AS original_id, user_id, type, date, GROUP_CONCAT(url) urls FROM

(SELECT i1.*,

IF(i1.type='image',

IFNULL((SELECT MIN(i2.ID)-1 FROM Items i2

WHERE i2.ID>i1.ID AND

(i2.type!=i1.type OR i1.user_id!=i2.user_id OR i1.date!=i2.date)),

(SELECT MAX(id) FROM Items)),

i1.ID) AS lastRow,

IF (i1.type='image',

IFNULL(SELECT MAX(i3.ID)+1 FROM Items i3

WHERE i3.ID<=i1.ID AND

(i3.type!=i1.type OR i1.user_id!=i3.user_id OR i1.date!=i3.date)),

(SELECT MIN(id) FROM Items)),

i1.ID) AS firstRow) AS groupItems

GROUP BY user_id, type, date, firstRow, lastRow) t, (SELECT @rownum:=0) r

ORDER BY t.original_id;

查询使用相关的子查询查找每个图像组的开始和结束ID。组边界是一个类型、用户或日期不同的项。

SELECT i1.ID,

IF(i1.type='image',

IFNULL((SELECT MIN(i2.ID)-1 FROM Items i2

WHERE i2.ID>i1.ID AND

(i2.type!=i1.type OR i1.user_id!=i2.user_id OR i1.date!=i2.date)),

(SELECT MAX(id) FROM Items)),

i1.ID) AS lastRow,

IF (i1.type='image',

IFNULL(SELECT MAX(i3.ID)+1 FROM Items i3

WHERE i3.ID<=i1.ID AND

(i3.type!=i1.type OR i1.user_id!=i3.user_id OR i1.date!=i3.date)),

(SELECT MIN(id) FROM Items)),

i1.ID) AS firstRow

对于每个项目,第一行/最后一行列给出组的开始和结束。然后我们可以使用group_concat连接所有URL。为了保持顺序,输出min(id),给出每组的第一个id。

SELECT MIN(id) AS original_id, user_id, type, date, GROUP_CONCAT(url) urls FROM

讯享网

(SELECT i1.*,

IF(i1.type='image',

IFNULL((SELECT MIN(i2.ID)-1 FROM Items i2

WHERE i2.ID>i1.ID AND

(i2.type!=i1.type OR i1.user_id!=i2.user_id OR i1.date!=i2.date)),

(SELECT MAX(id) FROM Items)),

i1.ID) AS lastRow,

IF (i1.type='image',

IFNULL(SELECT MAX(i3.ID)+1 FROM Items i3

WHERE i3.ID<=i1.ID AND

(i3.type!=i1.type OR i1.user_id!=i3.user_id OR i1.date!=i3.date)),

(SELECT MIN(id) FROM Items)),

i1.ID) AS firstRow) AS groupItems

GROUP BY user_id, type, date, firstRow, lastRow

最后,要获取新表的连续ID,请使用变量计算排名:

SELECT @rownum:=@rownum+1 AS id, user_id, type, date, urls FROM

(SELECT MIN(ID) AS original_id, user_id, type, date, GROUP_CONCAT(url) urls FROM

(SELECT i1.*,

IF(i1.type='image',

IFNULL((SELECT MIN(i2.ID)-1 FROM Items i2

WHERE i2.ID>i1.ID AND

(i2.type!=i1.type OR i1.user_id!=i2.user_id OR i1.date!=i2.date)),

(SELECT MAX(id) FROM Items)),

i1.ID) AS lastRow,

IF (i1.type='image',

IFNULL(SELECT MAX(i3.ID)+1 FROM Items i3

WHERE i3.ID<=i1.ID AND

(i3.type!=i1.type OR i1.user_id!=i3.user_id OR i1.date!=i3.date)),

(SELECT MIN(id) FROM Items)),

i1.ID) AS firstRow) AS groupItems

GROUP BY user_id, type, date, firstRow, lastRow) t, (SELECT @rownum:=0) r

ORDER BY t.original_id;

SQL最适合处理无序的数据集,而不是像这里所说的序列。

如果您可以在表示代码中做到这一点,或者在应用程序层中做到这一点,我想这会更快、更灵活。手工编码的解决方案可以在一次数据传递中找到每个组的开始和结束。我怀疑SQL查询的执行效率是否如此之高。

mysql firstrow=2_按mysql查询的复杂分组

相关推荐