22,210
社区成员
发帖
与我相关
我的任务
分享sql查询不连续数据分组统计
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
...全文
请发表友善的回复…
发表回复
gdhwj 2012-09-12
- 打赏
- 举报
[Quote=引用 2 楼 的回复:]
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,……
[/Quote]
能不能做成
stationno laneno 号段1 号段2 号段3 sumvehcount
----------- ----------- ------------------------- -----------
1 10 1~5 10~14 0 1000
这样的
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,……
[/Quote]
能不能做成
stationno laneno 号段1 号段2 号段3 sumvehcount
----------- ----------- ------------------------- -----------
1 10 1~5 10~14 0 1000
这样的
gdhwj 2012-09-12
- 打赏
- 举报
[Quote=引用 2 楼 的回复:]
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,……
[/Quote]
能不能查询到这样？
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 1000
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,……
[/Quote]
能不能查询到这样？
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 1000
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
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
DBA_磊仔 2012-09-06
- 打赏
- 举报
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 行受影响)
*/
问苍天人生几何 2012-09-06
- 打赏
- 举报
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
DBA_磊仔 2012-09-06
- 打赏
- 举报
[Quote=引用 4 楼 的回复:]你搞得测试数据太特殊了
导致一二楼都凑了一个结果出来
这也不能全怪他们
SQL code
;WITH cte AS
(
select stationno ,laneno ,invoiceid,vehcount,
min_vehcount=ISNULL((SELECT MIN(invoiceid) FROM t AS b
WHERE stationno……
[/Quote]似乎我的不是拼凑的...
汤姆克鲁斯 2012-09-06
- 打赏
- 举报
你搞得测试数据太特殊了
导致一二楼都凑了一个结果出来
这也不能全怪他们
导致一二楼都凑了一个结果出来
这也不能全怪他们
;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 行受影响)
*/
gdhwj 2012-09-06
- 打赏
- 举报
[Quote=引用 1 楼 的回复:]
SQL code
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
……
[/Quote]
您这个只针对这些数据有用，大批量不连续的数据就不行了
SQL code
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
……
[/Quote]
您这个只针对这些数据有用，大批量不连续的数据就不行了
