举个例子
lbid spwyh
1,6613161000000001
1,6613161000000002
1,6613161000000003
1,6613161000000004
1,6613161000000005
1,6613161000000007
2,6613161100000001
2,6613161100000002
2,6613161100000003
2,6613161100000004
查询结果
1,6613161000000001-6613161000000004
1,6613161000000005
1,6613161000000007
2,6613161100000001-6613161100000004
怎么写语句啊,求教大牛啊
lbid spwyh
1,6613161000000001
1,6613161000000002
1,6613161000000003
1,6613161000000004
1,6613161000000005
1,6613161000000007
2,6613161100000001
2,6613161100000002
2,6613161100000003
2,6613161100000004
查询结果
1,6613161000000001-6613161000000004
1,6613161000000005
1,6613161000000007
2,6613161100000001-6613161100000004
怎么写语句啊,求教大牛啊
我确实是在程序中实现这个功能的,java ext
SELECT b.lbid, min(b.spwyh) Start_HM, max(b.spwyh) End_HM
FROM
(SELECT a.*, CONVERT((a.spwyh-rownum),DECIMAL) cc FROM (SELECT @rownum:=@rownum+1 as rownum,spwyh, lbid FROM (SELECT @rownum:=0) r, spdetail ORDER BY lbid, spwyh) a) b
GROUP BY b.lbid, b.cc
不起作用啊,中间断了的那个号没有查出来,大概不是代表了实际的rownum吧
+------+------------------+
| lbid | spwyh |
+------+------------------+
| 1 | 6613161000000001 |
| 1 | 6613161000000002 |
| 1 | 6613161000000003 |
| 1 | 6613161000000004 |
| 1 | 6613161000000005 |
| 1 | 6613161000000007 |
| 2 | 6613161100000001 |
| 2 | 6613161100000002 |
| 2 | 6613161100000003 |
| 2 | 6613161100000004 |
+------+------------------+
10 rows in set (0.00 sec)mysql> set @x=0;
Query OK, 0 rows affected (0.00 sec)mysql> select lbid,if(min(spwyh)=max(spwyh),min(spwyh),concat(min(spwyh),'-',max(spwyh))) as k
-> from (
-> select if(spwyh!=@x+1,@y:=@y+1,@y:=@y) as p,
-> lbid,spwyh, @x:=spwyh
-> from ecton
-> order by lbid,spwyh
-> ) t
-> group by p;
+------+-----------------------------------+
| lbid | k |
+------+-----------------------------------+
| 1 | 6613161000000001-6613161000000005 |
| 1 | 6613161000000007 |
| 2 | 6613161100000001-6613161100000004 |
+------+-----------------------------------+
3 rows in set (0.00 sec)mysql>
效果不错,我也来一个:
SELECT lbid,REPLACE(GROUP_CONCAT(spwyh),',','-') FROM (
SELECT a.lbid,a.spwyh,CASE WHEN IFNULL(b.spwyh,c.spwyh) IS NOT NULL THEN 1 ELSE 0 END flag FROM ecton a
LEFT JOIN ecton b
ON a.lbid=b.lbid AND a.spwyh=b.spwyh+1
LEFT JOIN ecton c
ON a.lbid=c.lbid AND a.spwyh=c.spwyh-1
WHERE b.lbid IS NULL OR c.lbid IS NULL
ORDER BY 1,2)
a
GROUP BY lbid,flag
ORDER BY 1,2+------+--------------------------------------+
| lbid | REPLACE(GROUP_CONCAT(spwyh),',','-') |
+------+--------------------------------------+
| 1 | 6613161000000001-6613161000000005 |
| 1 | 6613161000000007 |
| 2 | 6613161100000001-6613161100000004 |
+------+--------------------------------------+
3 rows in set (0.01 sec)