有这样一个表
subs_log
pid, media, channel, recv, addtimepid的内容不是0就是9
现在要实现这样的统计:根据media和channel的不同统计有多少个pid为0的记录,多少个pid为9的记录,但是recv内容相同的记录要归为一条来统计(pid不同视为两条)。
比如说数据内容为:
pid media channel recv addtime
0 a ca 991 2006-1-1
0 a ca 991 2006-1-2
0 a cab 991 2006-1-1
0 b cb 991 2006 -1-1
0 b cb 991 2006-1-2
9 a ca 991 2006-1-2
0 a ca 881 2006-1-1统计结果应该是:
media channel pid为0的数量 pid为9的数量
a ca 2 1
a cab 1 0
b cb 1 0第一个结果中pid为0的数量为2是因为第一条记录和第二条记录的recv相同视为一条,而最后一条记录的recv内容不同视为第二条。
第三个结果中pid为0的数量为1也是因为recv的内容相同。
请各位大侠帮忙,谢谢啊,分不够再加
subs_log
pid, media, channel, recv, addtimepid的内容不是0就是9
现在要实现这样的统计:根据media和channel的不同统计有多少个pid为0的记录,多少个pid为9的记录,但是recv内容相同的记录要归为一条来统计(pid不同视为两条)。
比如说数据内容为:
pid media channel recv addtime
0 a ca 991 2006-1-1
0 a ca 991 2006-1-2
0 a cab 991 2006-1-1
0 b cb 991 2006 -1-1
0 b cb 991 2006-1-2
9 a ca 991 2006-1-2
0 a ca 881 2006-1-1统计结果应该是:
media channel pid为0的数量 pid为9的数量
a ca 2 1
a cab 1 0
b cb 1 0第一个结果中pid为0的数量为2是因为第一条记录和第二条记录的recv相同视为一条,而最后一条记录的recv内容不同视为第二条。
第三个结果中pid为0的数量为1也是因为recv的内容相同。
请各位大侠帮忙,谢谢啊,分不够再加
media,
channel,
SUM(Case pid When 0 Then 1 Else 0 End) As pid为0的数量,
SUM(Case pid When 9 Then 1 Else 0 End) As pid为9的数量
From
(Select Distinct pid,media,channel,recv From subs_log) A
Group By media,channel
create table tb(pid int,media varchar(10),channel varchar(10),recv int,addtime varchar(10))
insert into tb select 0,'a','ca',991,'2006-1-1'
union all select 0,'a','ca',991,'2006-1-2'
union all select 0,'a','cab',991,'2006-1-1'
union all select 0,'b','cb',991,'2006 -1-1'
union all select 0,'b','cb',991,'2006-1-2'
union all select 9,'a','ca',991,'2006-1-2'
union all select 0,'a','ca',881,'2006-1-1'declare @sql varchar(8000)
set @sql='select a.media,a.channel'
select @sql=@sql+',[pid为'+cast(pid as varchar)+'的数量]=sum(case a.pid when '+cast(pid as varchar)+' then 1 else 0 end)' from tb group by pid
exec(@sql+' from (select pid,media,channel,recv,max(addtime) as addtime from tb group by pid,media,channel,recv)a group by a.media,a.channel')drop table tb
Create Table subs_log
(pid Int,
media Varchar(10),
channel Varchar(10),
recv Int,
addtime Varchar(10))
--插入數據
Insert subs_log Select 0, 'a', 'ca', 991, '2006-1-1'
Union All Select 0,'a', 'ca', 991, '2006-1-2'
Union All Select 0,'a', 'cab', 991, '2006-1-1'
Union All Select 0,'b', 'cb', 991, '2006 -1-1'
Union All Select 0,'b', 'cb', 991, '2006-1-2'
Union All Select 9,'a', 'ca', 991, '2006-1-2'
Union All Select 0,'a', 'ca', 881, '2006-1-1'
--測試
Select
media,
channel,
SUM(Case pid When 0 Then 1 Else 0 End) As pid为0的数量,
SUM(Case pid When 9 Then 1 Else 0 End) As pid为9的数量
From
(Select Distinct pid,media,channel,recv From subs_log) A
Group By media,channel
--刪除測試環境
Drop Table subs_log
--結果
/*
media channel pid为0的数量 pid为9的数量
a ca 2 1
a cab 1 0
b cb 1 0
*/
insert into tb select 0,'a','ca',991,'2006-1-1'
union all select 0,'a','ca',991,'2006-1-2'
union all select 0,'a','cab',991,'2006-1-1'
union all select 0,'b','cb',991,'2006 -1-1'
union all select 0,'b','cb',991,'2006-1-2'
union all select 9,'a','ca',991,'2006-1-2'
union all select 0,'a','ca',881,'2006-1-1'select a.media,
a.channel,
pid为1数量=sum(case a.pid when 0 then 1 else 0 end),
pid为9数量=sum(case a.pid when 9 then 1 else 0 end)
from (select pid,media,channel,recv,max(addtime) as addtime from tb group by pid,media,channel,recv)a
group by a.media,a.channeldrop table tb
Declare @Tb table(pid int,media varchar(10),channel varchar(10),recv int,additem datetime)
insert into @Tb select 0,'a','ca',991,'2006-1-1'
union all select 0,'a','ca ',991,'2006-1-2'
union all select 0,'a','cab',991, '2006-1-1'
union all select 0,'b','cb',991,'2006 -1-1'
union all select 0,'b','cb',991,'2006-1-2'
union all select 9,'a','ca',991,'2006-1-2'
union all select 0,'a' ,'ca',881,'2006-1-1'select * from @Tb
--------------------------------------------------------------------
Select media,channel,
SUM(Case pid When 0 Then 1 Else 0 End) pid为0的数量,
SUM(Case pid When 9 Then 1 Else 0 End) pid为9的数量
From
(Select Distinct pid,media,channel,recv From @Tb) A
Group By media,channel
SUM(Case pid When 0 Then 1 Else 0 End) pid为0的数量,
SUM(Case pid When 9 Then 1 Else 0 End) pid为9的数量
From
(Select Distinct pid,media,channel,recv From @Tb) A
where
条件
Group By media,channel