有一sql求助

czwily 2010-10-13 01:58:10

表数据
id workid barcode cusid(客户编号)
1 1(发送) 0001 1
2 7（回收） 0001 1
3 1 0001 1
4 7 0001 2
5 1 0002 1
6 7 0002 3

barcode是条码，一个条码发送给一个客户1后，无论他是从其他客户2或3手上回来的，
只要它回来了，就算在客户一头上

要求查询汇总结果
cusid 发送次数回收次数
1 3 3
2 0 0
3 0 0

...全文

89 点赞收藏 10

写回复

10 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

fpzgm 2010-10-13



--更正一下，借用的数据

--> 测试数据: #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 行受影响)

回复赞

fpzgm 2010-10-13



--> 测试数据: #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 行受影响)

回复赞

SQL2088 2010-10-13

--> 测试数据: #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 行受影响)

回复赞

fpzgm 2010-10-13



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