select stationno,laneno,grp 号段,sum(_sum) sum from ( select stationno,laneno,case when invoiceid%5<=5 and invoiceid/5<=1 then '1~5' else '10~14' end grp,sum(vehcount) _sum from t group by stationno,laneno,invoiceid/5,invoiceid%5 )tt group by stationno,laneno,grp
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 行受影响)*/
你搞得测试数据太特殊了 导致一二楼都凑了一个结果出来 这也不能全怪他们;WITH cte AS ( select stationno ,laneno ,invoiceid,vehcount, min_vehcount=ISNULL((SELECT MIN(invoiceid) FROM t AS b WHERE stationno=a.stationno AND laneno=a.laneno AND invoiceid>a.invoiceid AND NOT EXISTS (SELECT 1 FROM t WHERE stationno=b.stationno AND laneno=b.laneno AND invoiceid=b.invoiceid+1) AND EXISTS (SELECT 1 FROM t WHERE stationno=b.stationno AND laneno=b.laneno AND invoiceid=a.invoiceid+1) ),invoiceid) from t AS a ) SELECT stationno ,laneno ,LTRIM(MIN(invoiceid))+ '~'+ LTRIM(MAX(invoiceid)) AS 号段,sum(vehcount)sumvehcount FROM cte GROUP BY stationno ,laneno,min_vehcount /* 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 行受影响) */
能不能查询到这样? stationno laneno 号段 1 号段2 号段3 号段4 sumvehcount ----------- ----------- ------------------------- ----------- 1 10 1~5 10~14 7-9 18-19 1500 1 11 1~5 0~14 0 0 1000 1 12 1~5 10~14 0 0 1000 1 13 1~5 10~14 0 0 1000create 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 insert into t select 1,10,07 ,100 insert into t select 1,10,08 ,100 insert into t select 1,10,09 ,100 insert into t select 1,10,18 ,100 insert into t select 1,10,19 ,100
select stationno,laneno,grp 号段,sum(_sum) sum from
(
select stationno,laneno,case when invoiceid%5<=5 and invoiceid/5<=1 then '1~5' else '10~14' end grp,sum(vehcount) _sum from t
group by stationno,laneno,invoiceid/5,invoiceid%5
)tt
group by stationno,laneno,grp
+ '~'+ 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 行受影响)*/
导致一二楼都凑了一个结果出来
这也不能全怪他们;WITH cte AS
(
select stationno ,laneno ,invoiceid,vehcount,
min_vehcount=ISNULL((SELECT MIN(invoiceid) FROM t AS b
WHERE stationno=a.stationno AND laneno=a.laneno AND invoiceid>a.invoiceid
AND NOT EXISTS (SELECT 1 FROM t WHERE stationno=b.stationno AND laneno=b.laneno AND invoiceid=b.invoiceid+1)
AND EXISTS (SELECT 1 FROM t WHERE stationno=b.stationno AND laneno=b.laneno AND invoiceid=a.invoiceid+1)
),invoiceid)
from t AS a
)
SELECT stationno ,laneno ,LTRIM(MIN(invoiceid))+ '~'+ LTRIM(MAX(invoiceid)) AS 号段,sum(vehcount)sumvehcount
FROM cte
GROUP BY stationno ,laneno,min_vehcount
/*
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 行受影响)
*/
stationno laneno 号段 1 号段2 号段3 号段4 sumvehcount
----------- ----------- ------------------------- -----------
1 10 1~5 10~14 7-9 18-19 1500
1 11 1~5 0~14 0 0 1000
1 12 1~5 10~14 0 0 1000
1 13 1~5 10~14 0 0 1000create 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
insert into t select 1,10,07 ,100
insert into t select 1,10,08 ,100
insert into t select 1,10,09 ,100
insert into t select 1,10,18 ,100
insert into t select 1,10,19 ,100
stationno laneno 号段1 号段2 号段3 sumvehcount
----------- ----------- ------------------------- -----------
1 10 1~5 10~14 0 1000这样的