sql查询不连续数据分组统计
create table t(stationno int,laneno int,invoiceid int,vehcount int) insert into t select 1,10,1 ,100insert into t select 1,10,2 ,100insert into t select 1,10,3 ,100insert into t select 1,10,4 ,100insert into t select 1,10,5 ,100insert into t select 1,10,10 ,100insert into t select 1,10,11 ,100insert into t select 1,10,12 ,100insert into t select 1,10,13 ,100insert into t select 1,10,14 ,100insert into t select 1,11,1 ,100insert into t select 1,11,2 ,100insert into t select 1,11,3 ,100insert into t select 1,11,4 ,100insert into t select 1,11,5 ,100insert into t select 1,11,10 ,100insert into t select 1,11,11 ,100insert into t select 1,11,12 ,100insert into t select 1,11,13 ,100insert into t select 1,11,14 ,100insert into t select 2,13,1 ,100insert into t select 2,13,2 ,100insert into t select 2,13,3 ,100insert into t select 2,13,4 ,100insert into t select 2,13,5 ,100insert into t select 2,13,10 ,100insert into t select 2,13,11 ,100insert into t select 2,13,12 ,100insert into t select 2,13,13 ,100insert into t select 2,13,14 ,100insert into t select 2,12,1 ,100insert into t select 2,12,2 ,100insert into t select 2,12,3 ,100insert into t select 2,12,4 ,100insert into t select 2,12,5 ,100insert into t select 2,12,10 ,100insert into t select 2,12,11 ,100insert into t select 2,12,12 ,100insert into t select 2,12,13 ,100insert into t select 2,12,14 ,100
stationno laneno 号段 sumvehcount1 10 1~5 5001 10 10~14 5001 11 1~5 5001 11 10~14 5002 13 1~5 5002 13 10~14 5002 12 1~5 5002 12 10~14 500
SELECT stationno ,laneno ,cast(MIN(invoiceid) as varchar(10)) + '~'+ cast(MAX(invoiceid) as varchar(10)) AS 号段,sum(vehcount) sumvehcountFROM (SELECT stationno ,laneno ,invoiceid,vehcount, invoiceid - ROW_NUMBER() OVER(partition by stationno ,laneno ORDER BY invoiceid) AS grp FROM dbo.T ) AS DGROUP BY stationno ,laneno,grp/*stationno laneno 号段 sumvehcount----------- ----------- --------------------- -----------1 10 1~5 5001 10 10~14 5001 11 1~5 5001 11 10~14 5002 12 1~5 5002 12 10~14 5002 13 1~5 5002 13 10~14 500(8 行受影响)*/
