现有表
ID CHANNEL(网页上的链接) updatetime(插入的时间) adfrom(来自哪里)
1 笔记本 2008-9-10 baidu
2 键盘 2008-9-11 baidu
3 笔记本 2008-9-11 google
4 笔记本 2008-9-10 baidu
5 键盘 2008-9-11 google
6 笔记本 2008-9-11 google
7 笔记本 2008-9-10 baidu
8 键盘 2008-9-11 google
9 笔记本 2008-9-11 google
要得到以下数据
笔记本 键盘
baidu 3 1
google 3 2
就是要查出 来自baidu 的笔记本有几条记录,来自google的笔记本有几条记录,adfrom不固定,可能还会有163什么的, channel也不固定,也可能有"硬盘"什么的,求SQL高手帮忙,谢谢
ID CHANNEL(网页上的链接) updatetime(插入的时间) adfrom(来自哪里)
1 笔记本 2008-9-10 baidu
2 键盘 2008-9-11 baidu
3 笔记本 2008-9-11 google
4 笔记本 2008-9-10 baidu
5 键盘 2008-9-11 google
6 笔记本 2008-9-11 google
7 笔记本 2008-9-10 baidu
8 键盘 2008-9-11 google
9 笔记本 2008-9-11 google
要得到以下数据
笔记本 键盘
baidu 3 1
google 3 2
就是要查出 来自baidu 的笔记本有几条记录,来自google的笔记本有几条记录,adfrom不固定,可能还会有163什么的, channel也不固定,也可能有"硬盘"什么的,求SQL高手帮忙,谢谢
是这样么?
如果是只要计数前面加个COUNT(笔记本)就可以了吧应该。
create table #
(
[id] int,
CHANNEL varchar(10),
adfrom varchar(10)
)insert into #
select 1,'笔记本','baidu'
union all
select 2,'键盘','baidu'
union all
select 3,'笔记本','google'
union all
select 4,'键盘','baidu'
union all
select 5,'笔记本','google'select adfrom,
sum(case when CHANNEL = '笔记本' then 1 else 0 end) as 笔记本,
sum(case when CHANNEL = '键盘' then 1 else 0 end) as 键盘
from # group by adfromadfrom 笔记本 键盘
---------- ----------- -----------
baidu 1 2
google 2 0(所影响的行数为 2 行)
baidu 笔记本 2
google 笔记本 3
baidu 键盘 1
google 键盘 3
Select adfrom,case adfrom when 'baidu' then (Select count(1) from test where CHANNEL=N'笔记本') else 0 end as '笔记本',
case adfrom when 'google' then (Select count(1) from test where CHANNEL=N'键盘') else 0 end as '键盘'
from test
group by adfrom
declare @sql varchar(8000)
set @sql='select adfrom'
select @sql=@sql+','+channel+'=isnull(sum(case CHANNEL when '''+CHANNEL+''' then 1 end),0)'
from test group by channel
set @sql=@sql+' from test group by adfrom'
print @sql
exec(@sql)
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([CHANNEL]) from T group by [CHANNEL]
exec('select * from (select adfrom,channel from T) a pivot (count([CHANNEL]) for [CHANNEL] in('+@s+'))b')
--Drop
drop table T
--Result
/*
adfrom 笔记本 键盘
------ ----------- -----------
baidu 3 1
google 3 2
*/
set @sql='select adfrom'
select @sql=@sql+','+channel+'=isnull(sum(case CHANNEL when '''+channel+''' then 1 end),0)'
from test group by channel
set @sql=@sql+' from test group by adfrom'
print @sql
exec(@sql)
select CHANNEL,adfrom,count(*) from tablename group by CHANNEL,adfrom
其中的select @sql=@sql+','+channel+'=isnull(sum(case CHANNEL when '''+CHANNEL+''' then 1 end),0)' from test group by channel
如果改成:
select @sql=@sql+','+channel+'=sum(case CHANNEL when '''+CHANNEL+''' then 1 else 0 end)'
from test group by channel
好像更容易理解一点的,觉得没必要用isnull函数。另外还可以改成这样的:
select @sql=@sql+','+channel+'=(select count(channel) from test where c.adfrom=adfrom and channel='''+channel+''')'
from test group by channel set @sql=@sql+' from test c group by adfrom'
这样也行,就是连接成的字符串有357个长,比上面的多了100个