假设mySql表中有数据,如下所示:
+------+-------+------+
| Name | Start | End |
+------+-------+------+
| A | 1 | null |
| A | 2 | null |
| A | null | 3 |
| B | 4 | null |
| B | 5 | null |
| B | null | 6 |
| A | 7 | null |
| A | 8 | null |
| A | 9 | null |
| A | null | 10 |
+------+-------+------+
现在我需要在结果中显示如下:
+------+-------+-----+-------+
| Name | Start | End | Count |
+------+-------+-----+-------+
| A | 1 | 3 | 3 |
| B | 4 | 6 | 3 |
| A | 7 | 10 | 4 |
+------+-------+-----+-------+
这里,第一个A以1开始,以3结束,因此,
结果的第一行应该是:A(名称(----1(开始(----3(结束(----3
结果的第二行应该是:B(名称(----4(开始(----6(结束(----3(计数(
然后当A再次出现时,起始值为7,结束值为10,所以
结果的第三行应该是:A(名称(----7(开始(----10(结束(----4(计数(
有人能帮我查询一下结果吗?
类似的东西?
SELECT filtered.name, MIN(filtered.start), MAX(filtered.end), COUNT(*)
FROM (
SELECT t.name, t.start,t.end, @curRank := IF(t.name = @letterINIT, @curRank , @curRank + 1) AS rank, @letterINIT := t.name as letter
FROM test as t, (SELECT @curRank := 0) r, (SELECT @letterINIT := null) l
ORDER BY COALESCE(t.start, t.end)
) as filtered
GROUP BY filtered.name, filtered.rank
ORDER BY filtered.rank
http://sqlfiddle.com/#!9/3cd215/1