try: select id,WebSite as 网站名, count(case GroupName when '学生组' then 1 when 'all' then 0 end)as 学生组访问次数, count(case GroupName when '教师组' then 1 when 'all' then 0 end)as 教师组访问次数, count(1)as 合计 from WEBACCESS group by id,WebSite
这个: select id,WebSite as 网站名, count(case GroupName when '学生组' then 1 when 'all' then 1 else 0 end)as 学生组访问次数, count(case GroupName when '教师组' then 1 when 'all' then 1 else 0 end)as 教师组访问次数, count(1)as 合计 from WEBACCESS group by id,WebSite
不好意思,应该是这个: select id,WebSite as 网站名, count(case GroupName when '教师组' then 0 else 1 end)as 学生组访问次数, count(case GroupName when '学生组' then 0 else 1 end)as 教师组访问次数, count(case GroupName when 'all' then 2 else 1 end)as 合计 from WEBACCESS group by id,WebSite
select id,WebSite as 网站名, count(case GroupName when '教师组' then 0 else 1 end)as 学生组访问次数, count(case GroupName when '学生组' then 0 else 1 end)as 教师组访问次数, count(case GroupName when 'all' then 2 else 1 end)as 合计 from WEBACCESS where AccessTimes between '20050412' and '20050425' group by id,WebSite
select id,WebSite as 网站名,
count(case GroupName when '学生组' then 1 when 'all' then 0 end)as 学生组访问次数,
count(case GroupName when '教师组' then 1 when 'all' then 0 end)as 教师组访问次数,
count(1)as 合计
from WEBACCESS
group by id,WebSite
select id,WebSite as 网站名,
count(case GroupName when '学生组' then 1 when 'all' then 1 else 0 end)as 学生组访问次数,
count(case GroupName when '教师组' then 1 when 'all' then 1 else 0 end)as 教师组访问次数,
count(1)as 合计
from WEBACCESS
group by id,WebSite
select id,WebSite as 网站名,
count(case GroupName when '教师组' then 0 else 1 end)as 学生组访问次数,
count(case GroupName when '学生组' then 0 else 1 end)as 教师组访问次数,
count(case GroupName when 'all' then 2 else 1 end)as 合计
from WEBACCESS
group by id,WebSite
count(case GroupName when '教师组' then 0 else 1 end)as 学生组访问次数,
count(case GroupName when '学生组' then 0 else 1 end)as 教师组访问次数,
count(case GroupName when 'all' then 2 else 1 end)as 合计
from WEBACCESS where AccessTimes between '20050412' and '20050425'
group by id,WebSite
SELECT WebSite as 网站名, '学生组访问次数' =sum(
case rtrim(GroupName)
when '教师组' Then 0
when 'none' Then 0
else AccessTimes
End ),
'教师组访问次数' =sum(
case GroupName
when '学生组' Then 0
when 'none' Then 0
else AccessTimes
End ),
Sum(AccessTimes) as 总访问次数
from WebAccess where RecordDate between '2005-1-1' and '2005-5-1' and block=0 group by WebSite
order by 总访问次数 desc