--一张表有字段 ItemID,Qtitle,Col3,QTypeID
--Qtitle是问题标题,Col3是评论的字段,如果为空就是未评论,不为空就是已评论。Col3的内容是1/2/3/4/5 1、为很差 2、差 3、一般 4、较满意 5、满意
--另外一张表 TypeID,TypeName,CreateDate TypeID与QtypeID是公共字段--查出的效果如下
TypeName 未评论 已评论 很差 差 一般 较满意 满意
住房保障管理类 10条 10条 2条 3条 1条 1条 3条
--Qtitle是问题标题,Col3是评论的字段,如果为空就是未评论,不为空就是已评论。Col3的内容是1/2/3/4/5 1、为很差 2、差 3、一般 4、较满意 5、满意
--另外一张表 TypeID,TypeName,CreateDate TypeID与QtypeID是公共字段--查出的效果如下
TypeName 未评论 已评论 很差 差 一般 较满意 满意
住房保障管理类 10条 10条 2条 3条 1条 1条 3条
sum(case when Col3 is null then 1 else 0 end) 未评论,
sum(case when Col3 is not null then 1 else 0 end) 已评论,
sum(case when Col3 =1 then 1 else 0 end) 很差,
sum(case when Col3 =2 null then 1 else 0 end) 差,
sum(case when Col3 =3 null then 1 else 0 end) 一般,
sum(case when Col3 =4 null then 1 else 0 end) 较满意,
sum(case when Col3 =5 null then 1 else 0 end) 满意
from 一张表 a,另外一张表 b where a.QtypeID=b.TypeID
group by TypeName
--修改
select TypeName,
sum(case when Col3 is null then 1 else 0 end) 未评论,
sum(case when Col3 is not null then 1 else 0 end) 已评论,
sum(case when Col3 =1 then 1 else 0 end) 很差,
sum(case when Col3 =2 then 1 else 0 end) 差,
sum(case when Col3 =3 then 1 else 0 end) 一般,
sum(case when Col3 =4 then 1 else 0 end) 较满意,
sum(case when Col3 =5 then 1 else 0 end) 满意
from 一张表 a,另外一张表 b where a.QtypeID=b.TypeID
group by TypeName
select Qtitle,
sum(case when Col3 is null then 1 else 0 end) as 未评论,
sum(case when Col3 is not null then 1 else 0 end) as 已评论,
sum(case when Col3 = 1 then 1 else 0 end) as 很差,
...
from table
group by Qtitle
如果我还要在最后面加个统计可以吗?
TypeName 未评论 已评论 很差 差 一般 较满意 满意
住房保障管理类 10条 10条 2条 3条 1条 1条 3条
统计 10 10 2 3 1 1 3
isnull(TypeName,'合计') as TypeName,
sum(case when Col3 is null then 1 else 0 end) 未评论,
sum(case when Col3 is not null then 1 else 0 end) 已评论,
sum(case when Col3 =1 then 1 else 0 end) 很差,
sum(case when Col3 =2 then 1 else 0 end) 差,
sum(case when Col3 =3 then 1 else 0 end) 一般,
sum(case when Col3 =4 then 1 else 0 end) 较满意,
sum(case when Col3 =5 then 1 else 0 end) 满意
from
一张表 a,另外一张表 b where a.QtypeID=b.TypeID
group by
TypeName
with rollup
--修改
select TypeName,
sum(case when Col3 is null then 1 else 0 end) 未评论,
sum(case when Col3 is not null then 1 else 0 end) 已评论,
sum(case when Col3 =1 then 1 else 0 end) 很差,
sum(case when Col3 =2 then 1 else 0 end) 差,
sum(case when Col3 =3 then 1 else 0 end) 一般,
sum(case when Col3 =4 then 1 else 0 end) 较满意,
sum(case when Col3 =5 then 1 else 0 end) 满意
from 一张表 a,另外一张表 b where a.QtypeID=b.TypeID
group by TypeName
union all
select '统计' TypeName,
sum(case when Col3 is null then 1 else 0 end) 未评论,
sum(case when Col3 is not null then 1 else 0 end) 已评论,
sum(case when Col3 =1 then 1 else 0 end) 很差,
sum(case when Col3 =2 then 1 else 0 end) 差,
sum(case when Col3 =3 then 1 else 0 end) 一般,
sum(case when Col3 =4 then 1 else 0 end) 较满意,
sum(case when Col3 =5 then 1 else 0 end) 满意
from 一张表 a,另外一张表 b where a.QtypeID=b.TypeID