日期:2014-05-17 浏览次数:20422 次
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
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
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 行受影响) */