如何返回在MySQL中实际上是“连续的” GROUP BY内容。换句话说,GROUP BY是否尊重记录集的顺序?1 E5 L' z+ B, z: m
例如,SELECT MIN(col1),col2,COUNT(*) FROM table GROUP BY col2 ORDER BYcol1从下表中,col1是唯一的有序索引: " R: {( r/ e! ~. ^3 c2分4例56返回: N6 N& z( |) t7 i' Q- S2 ^
1一43 b 2但我需要返回以下内容: - }. @# O' F0 i: Q% ^1一23 b 2 ?& x6 ^7 W" Y3 {) c. H, U( g 解决方案: % u" e* L+ v2 f% D- J, I( j$ K 使用: 7 t: T! j9 B' V, M k SELECT MIN(t.id) 'mi t.val, COUNT(*) FROM (SELECT x.id, x.val, CASE WHEN xt.val IS NULL OR xt.val != x.val THEN @rownum := @rownum ELSE @rownum END AS grp FROM TABLE x JOIN (SELECT @rownum := 0) r LEFT JOIN (SELECT t.id 1 'id', t.val FROM TABLE t) xt ON xt.id = x.id) t GROUP BY t.val,t.grp ORDER BY mi这里的关键是创建允许分组的人为值。 2 U) k9 G. K" Q9 |! Y( q: e以前,更正了Guffa的答案:% D% k0 x1 @- y, l. E7 f
SELECT t.id,t.val FROM TABLE tLEFT JOIN TABLE t2 on t2.id 1 = t.id WHERE t2.val IS NULL OR t.val t2.val