22,302
社区成员




--更正一下,借用的数据
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,workid int,barcode varchar(4),cusid int)
insert into #tb
select 1,1,'0001',1 union all
select 2,7,'0001',1 union all
select 3,1,'0001',1 union all
select 4,7,'0001',2 union all
select 5,1,'0002',1 union all
select 6,7,'0002',3
with cte as
(select * from #tb where id%2=1
union
select a.id,a.workid,a.barcode,b.cusid
from #tb a,#tb b where a.id=b.id+1,a.barcode=b.barcode)
select cusid,sum(case when workid=1 then 1 else 0 end) as 发送次数,
sum(case when workid=7 then 1 else 0 end) as 回收次数
from cte group by cusid
cusid 发送次数 回收次数
----------- ----------- -----------
1 3 3
2 0 0
3 0 0
(3 行受影响)
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,workid int,barcode varchar(4),cusid int)
insert into #tb
select 1,1,'0001',1 union all
select 2,7,'0001',1 union all
select 3,1,'0001',1 union all
select 4,7,'0001',2 union all
select 5,1,'0002',1 union all
select 6,7,'0002',3
with cte as
(select * from [table] where id%2=1
union
select a.id,a.workid,a.barcode,b.cusid
from [table] a,[table] b where a.id=b.id+1,a.barcode=b.barcode)
select cusid,sum(case when workid=1 then 1 else 0 end) as 发送次数,
sum(case when workid=7 then 1 else 0 end) as 回收次数
from cte group by cusid
cusid 发送次数 回收次数
----------- ----------- -----------
1 3 3
2 0 0
3 0 0
(3 行受影响)
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,workid int,barcode varchar(4),cusid int)
insert into #tb
select 1,1,'0001',1 union all
select 2,7,'0001',1 union all
select 3,1,'0001',1 union all
select 4,7,'0001',2 union all
select 5,1,'0002',1 union all
select 6,7,'0002',3
select a.cusid,[发送次数],[回收次数]=isnull([回收次数],0) from
(
select cusid,
[发送次数]=sum(case when workid=1 then 1 else 0 end)
from #tb t
group by cusid
)a
left join
(
select t.cusid ,
[回收次数]= count(*)
from
(select distinct barcode,cusid from #tb where workid=1)t , #tb b
where t.barcode=b.barcode
and b.workid=7
group by t.cusid
)c
on a.cusid=c.cusid
cusid 发送次数 回收次数
----------- ----------- -----------
1 3 3
2 0 0
3 0 0
(3 行受影响)
with cte as
(select * from [table] where id%2=1
union
select a.id,a.workid,a.barcode,b.cusid
from [table] a,[table] b where a.id=b.id+1,a.barcode=b.barcode)
select cusid,sum(case when workid=1 then 1 else 0 end) as 发送次数,
sum(case when workid=7 then 1 else 0 end) as 回收次数
from cte group by cusid