原帖没表述清楚,重新描述一下
原始需求:
条码110604442,发送给客户1025 三次,回收的时候2次是从客户1025本身手上回收,
另一次是从客户6002手上回收,但因为都是发送给客户1025 的,所以无论最终这个回收都算在客户1025 头上这个操作因为不正规,其实应该发给谁,从谁手上回收;但有时候气站会忘记去做记录,其实少了一个从客户1回收再发给客户2的操作数据:
guid barcode workid optdate cusid
C32091E86985BC4495313604118CBB57 110604442 7(发送) 2010-10-14 11:19:11 1025
804C540116AB994D9497095E6D2A279F 110604442 1(回收) 2010-10-15 11:19:11 1025
DE7E570757F6AE42A33A37A54163FDCF 110604442 7 2010-10-16 11:19:11 1025
89EC731146F94846998DEA71EAFCB0A1 110604442 1 2010-10-16 12:19:11 1025
5793538AC50E8241AE16DFB5060B481C 110604442 7 2010-10-17 12:19:11 1025
C4AAE1ABE70A9740B4A2484263CA16C6 110604442 1 2010-10-17 22:19:11 6002
4C314BBAB0551E4595F3CADBE1ED9423 110604442 7 2010-10-18 22:19:11 6003
查询结果
cusid 发送次数 回收次数
1025 3 3
6002 0 0
6003 1 0
原始需求:
条码110604442,发送给客户1025 三次,回收的时候2次是从客户1025本身手上回收,
另一次是从客户6002手上回收,但因为都是发送给客户1025 的,所以无论最终这个回收都算在客户1025 头上这个操作因为不正规,其实应该发给谁,从谁手上回收;但有时候气站会忘记去做记录,其实少了一个从客户1回收再发给客户2的操作数据:
guid barcode workid optdate cusid
C32091E86985BC4495313604118CBB57 110604442 7(发送) 2010-10-14 11:19:11 1025
804C540116AB994D9497095E6D2A279F 110604442 1(回收) 2010-10-15 11:19:11 1025
DE7E570757F6AE42A33A37A54163FDCF 110604442 7 2010-10-16 11:19:11 1025
89EC731146F94846998DEA71EAFCB0A1 110604442 1 2010-10-16 12:19:11 1025
5793538AC50E8241AE16DFB5060B481C 110604442 7 2010-10-17 12:19:11 1025
C4AAE1ABE70A9740B4A2484263CA16C6 110604442 1 2010-10-17 22:19:11 6002
4C314BBAB0551E4595F3CADBE1ED9423 110604442 7 2010-10-18 22:19:11 6003
查询结果
cusid 发送次数 回收次数
1025 3 3
6002 0 0
6003 1 0
--见之前那一个帖子,也有回答alter table [table] add [TID] int IDENTITY(1,1)with cte as
(select workid,cusid from [table] where TID%2=1
union
select a.workid,b.cusid
from [table] a,[table] b where a.TID=b.TID+1)select cusid,sum(case when workid=7 then 1 else 0 end) as 发送次数,
sum(case when workid=1 then 1 else 0 end) as 回收次数
from cte group by cusidalter table [table] drop column TID
--见之前那一个帖子,也有回答
--更正一下alter table [table] add [TID] int IDENTITY(1,1)with cte as
(select workid,cusid from [table] where TID%2=1 --取发送的客户代码
union
select a.workid,b.cusid
from [table] a,[table] b where a.TID=b.TID+1 and TID%2=0)--将回收的客户代码改成上一条发送的客户代码select cusid,sum(case when workid=7 then 1 else 0 end) as 发送次数,
sum(case when workid=1 then 1 else 0 end) as 回收次数
from cte group by cusidalter table [table] drop column TID
if object_id('tb') is not null drop table tb
go
create table tb (guid varchar(32),barcode int,workid int,optdate datetime,cusid int)
insert into tb
select 'C32091E86985BC4495313604118CBB57',110604442,7,'2010-10-14 11:19:11',1025 union all
select '804C540116AB994D9497095E6D2A279F',110604442,1,'2010-10-15 11:19:11',1025 union all
select 'DE7E570757F6AE42A33A37A54163FDCF',110604442,7,'2010-10-16 11:19:11',1025 union all
select '89EC731146F94846998DEA71EAFCB0A1',110604442,1,'2010-10-16 12:19:11',1025 union all
select '5793538AC50E8241AE16DFB5060B481C',110604442,7,'2010-10-17 12:19:11',1025 union all
select 'C4AAE1ABE70A9740B4A2484263CA16C6',110604442,1,'2010-10-17 22:19:11',6002 union all
select '4C314BBAB0551E4595F3CADBE1ED9423',110604442,7,'2010-10-18 22:19:11',6003-- 用个函数
if object_id('ff') is not null drop function ff
go
create function ff(@uid varchar(20))
returns int
as
begin
declare @n int select @n=count(* )
from tb t, tb b
where t.barcode=b.barcode
and b.workid=1 and t.workid=7
and t.cusid=@uid
and t.optdate<b.optdate
and not exists(select 1 from tb where barcode=t.barcode and cusid=t.cusid and optdate<t.optdate)
return @n
end
go
select cusid,
[发送次数]=sum(case when workid=7 then 1 else 0 end),
[回复次数]=dbo.ff(cusid)
from tb t
group by cusid
cusid 发送次数 回复次数
----------- ----------- -----------
1025 3 3
6002 0 0
6003 1 0(3 行受影响)
(
select *, row_number() over (order by barcode,optdate) as rowid
from tb
)select a.cusid, count(1) as '发送次数', sum(isnull(b.num,0)) as '回收次数'
from #t a
left join (select barcode,rowid,1 as num from #t where workid = 1) b on a.barcode = b.barcode and a.rowid = b.rowid - 1
where a.workid = 7
group by a.cusidcusid 发送次数 回收次数
----------- ----------- -----------
1025 3 3
6003 1 0(2 行受影响)