22,209
社区成员
发帖
与我相关
我的任务
分享
declare @sql1 varchar(8000),@sql2 varchar(8000)
select @sql1='select [中段]=''数量'''
select @sql1=@sql1+',['+中段+']= isnull(sum(case 中段 when '''+中段+''' then 数量 end),0)' from (select count(1) 数量,g.f_GroupName 中段
from t_b_Consumer c left join InMine i on i.f_ConsumerID = c.f_ConsumerID
left join t_b_Group g
on c.f_GroupID = g.f_GroupID
group by g.f_GroupName) tb1
set @sql2=@sql1+' from (select count(1) 数量,g.f_GroupName 中段
from InMine i,t_b_Consumer c,t_b_Group g
where i.f_ConsumerID = c.f_ConsumerID and
c.f_GroupID = g.f_GroupID
group by g.f_GroupName) tb2'
exec(@sql2)
DECLARE @S NVARCHAR(1000)
SET @S=''
SELECT @S=@S+','+QUOTENAME(f_GroupName)+'=SUM(CASE WHEN f_GroupID='+RTRIM(f_GroupID)+' THEN 1 else 0 END)'
from t_b_Group
exec ('select [中段]=N''數量'''+@s+' from t_b_Consumer a where exists(select 1 from InMine where f_ConsumerID=a.f_ConsumerID)')