sql查询不连续数据分组统计解决方法
日期：2014-05-17 浏览次数：20588 次
sql查询不连续数据分组统计
需要查询到的结果是：
------解决方案--------------------
- SQL code
create table t(stationno int,laneno int,invoiceid int,vehcount int) insert into t select 1,10,1 ,100 insert into t select 1,10,2 ,100 insert into t select 1,10,3 ,100 insert into t select 1,10,4 ,100 insert into t select 1,10,5 ,100 insert into t select 1,10,10 ,100 insert into t select 1,10,11 ,100 insert into t select 1,10,12 ,100 insert into t select 1,10,13 ,100 insert into t select 1,10,14 ,100 insert into t select 1,11,1 ,100 insert into t select 1,11,2 ,100 insert into t select 1,11,3 ,100 insert into t select 1,11,4 ,100 insert into t select 1,11,5 ,100 insert into t select 1,11,10 ,100 insert into t select 1,11,11 ,100 insert into t select 1,11,12 ,100 insert into t select 1,11,13 ,100 insert into t select 1,11,14 ,100 insert into t select 2,13,1 ,100 insert into t select 2,13,2 ,100 insert into t select 2,13,3 ,100 insert into t select 2,13,4 ,100 insert into t select 2,13,5 ,100 insert into t select 2,13,10 ,100 insert into t select 2,13,11 ,100 insert into t select 2,13,12 ,100 insert into t select 2,13,13 ,100 insert into t select 2,13,14 ,100 insert into t select 2,12,1 ,100 insert into t select 2,12,2 ,100 insert into t select 2,12,3 ,100 insert into t select 2,12,4 ,100 insert into t select 2,12,5 ,100 insert into t select 2,12,10 ,100 insert into t select 2,12,11 ,100 insert into t select 2,12,12 ,100 insert into t select 2,12,13 ,100 insert into t select 2,12,14 ,100
需要查询到的结果是：
- XML code
stationno laneno 号段 sumvehcount 1 10 1~5 500 1 10 10~14 500 1 11 1~5 500 1 11 10~14 500 2 13 1~5 500 2 13 10~14 500 2 12 1~5 500 2 12 10~14 500
------解决方案--------------------
- SQL code
SELECT stationno ,laneno ,cast(MIN(invoiceid) as varchar(10))
+ '~'+ cast(MAX(invoiceid) as varchar(10)) AS 号段,sum(vehcount) sumvehcount
FROM (SELECT stationno ,laneno ,invoiceid,vehcount,
invoiceid - ROW_NUMBER() OVER(partition by stationno ,laneno ORDER BY invoiceid) AS grp
FROM dbo.T ) AS D
GROUP BY stationno ,laneno,grp
/*
stationno laneno 号段 sumvehcount
----------- ----------- --------------------- -----------
1 10 1~5 500
1 10 10~14 500
1 11 1~5 500
1 11 10~14 500
2 12 1~5 500
2 12 10~14 500
2 13 1~5 500
2 13 10~14 500
(8 行受影响)
*/
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
