create table t(主键ID int,回复主题ID int) insert t select 1,0 union all select 2,0 union all select 3,1 union all select 4,2 union all select 5,1 union all select 6,1select [主题的总数]=sum(case 回复主题ID when 0 then 1 else 0 end), [回复编号1主题总数]=sum(case 回复主题ID when 1 then 1 else 0 end), [回复编号2主题总数]=sum(case 回复主题ID when 2 then 1 else 0 end) from t
select case when 回复主题ID =0 then 主键ID else 回复主题ID end as 主题目的编号,sum(case when 回复主题ID =0 then 1 else 0 end) as 主题总数,sum(case when 回复主题ID =0 then 0 else 1 end) as 回复总数 from tablename group by case when 回复主题ID =0 then 主键ID else 回复主题ID end
case when 回复主题ID =0 then 主键ID else 回复主题ID end 也就是其他语言中的if else if(回复主题ID =0) 主键ID; else 回复主题ID
insert t select 1,0
union all select 2,0
union all select 3,1
union all select 4,2
union all select 5,1
union all select 6,1select [主题的总数]=sum(case 回复主题ID when 0 then 1 else 0 end),
[回复编号1主题总数]=sum(case 回复主题ID when 1 then 1 else 0 end),
[回复编号2主题总数]=sum(case 回复主题ID when 2 then 1 else 0 end)
from t
from tablename
group by case when 回复主题ID =0 then 主键ID else 回复主题ID end
也就是其他语言中的if else
if(回复主题ID =0)
主键ID;
else
回复主题ID