组合Select语句
组合Select语句
提问于 2012-08-08 03:57:24
我有三个select语句，除了每个事件的平均“分数”和每个事件的计数之外，还提供了参加特定活动的成员数量-医疗保健、宗教和体育。
每个事件的计数会有所不同。
每个查询都可以单独工作，但我想将它们组合成一个查询。
我该怎么做？
(select sum(case when Healthcare ='1' then 1 else 0 end) as [Healthcare_never],
sum(case when Healthcare ='2' then 1 else 0 end) as [Healthcare_not often],
sum(case when Healthcare ='3' then 1 else 0 end) as [Healthcare_average],
sum(case when Healthcare ='4' then 1 else 0 end) as [Healthcare_often],
sum(case when Healthcare ='5' then 1 else 0 end) as [Healthcare_very often]
,avg(Cast(Healthcare as float)) as Average
,count(Healthcare) as N_Healthcare
from Member
where Healthcare > '0' )
(select
sum(case when Religious ='1' then 1 else 0 end) as [Religious_never],
sum(case when Religious ='2' then 1 else 0 end) as [Religious_not often],
sum(case when Religious ='3' then 1 else 0 end) as [Religious_average],
sum(case when Religious ='4' then 1 else 0 end) as [Religious_often],
sum(case when Religious ='5' then 1 else 0 end) as [Religious_very often],
Avg(cast(Religious as float)) as Average
,count(Religious) as N_Religious
from Member
where Religious > '0' )
(select
sum(case when Sport ='1' then 1 else 0 end) as [Sport_never],
sum(case when Sport ='2' then 1 else 0 end) as [Sport_not often],
sum(case when Sport ='3' then 1 else 0 end) as [Sport_average],
sum(case when Sport ='4' then 1 else 0 end) as [Sport_often],
sum(case when Sport ='5' then 1 else 0 end) as [Sport_very often],
Avg(cast(Sport as float)) as Average
,count(Sport) as N_Sport
from Member
where Sport > '0' )回答 3
Stack Overflow用户
回答已采纳
发布于 2012-08-08 04:02:08
尝尝这个
select
sum(case when Healthcare ='1' then 1 else 0 end) as [Healthcare_never],
sum(case when Healthcare ='2' then 1 else 0 end) as [Healthcare_not often],
sum(case when Healthcare ='3' then 1 else 0 end) as [Healthcare_average],
sum(case when Healthcare ='4' then 1 else 0 end) as [Healthcare_often],
sum(case when Healthcare ='5' then 1 else 0 end) as [Healthcare_very often],
avg(Cast((case when Healthcare > 0 Then Healthcare Else Null end) as float)) as Healthcare_Average,
count(case when Healthcare > 0 Then Healthcare Else Null end) as N_Healthcare,
sum(case when Religious ='1' then 1 else 0 end) as [Religious_never],
sum(case when Religious ='2' then 1 else 0 end) as [Religious_not often],
sum(case when Religious ='3' then 1 else 0 end) as [Religious_average],
sum(case when Religious ='4' then 1 else 0 end) as [Religious_often],
sum(case when Religious ='5' then 1 else 0 end) as [Religious_very often],
Avg(cast((case when Religious > 0 Then Religious Else Null end) as float)) as Religious_Average,
count(case when Religious > 0 Then Religious Else Null end) as N_Religious,
sum(case when Sport ='1' then 1 else 0 end) as [Sport_never],
sum(case when Sport ='2' then 1 else 0 end) as [Sport_not often],
sum(case when Sport ='3' then 1 else 0 end) as [Sport_average],
sum(case when Sport ='4' then 1 else 0 end) as [Sport_often],
sum(case when Sport ='5' then 1 else 0 end) as [Sport_very often],
Avg(cast((case when Sport > 0 Then Sport Else Null end) as float)) as Sport_Average,
count(case when Sport > 0 Then Sport Else Null end) as N_Sport
from Member Stack Overflow用户
发布于 2012-08-08 04:04:16
只需将UNION ALL放在SELECT语句之间。但是您可能想要添加另一个列，以指示它来自哪个表。并且列标题需要相同。例如：
select 'Healthcare' as source
, sum(case when Healthcare ='1' then 1 else 0 end) as [never],
sum(case when Healthcare ='2' then 1 else 0 end) as [not often],
sum(case when Healthcare ='3' then 1 else 0 end) as [average],
sum(case when Healthcare ='4' then 1 else 0 end) as [often],
sum(case when Healthcare ='5' then 1 else 0 end) as [very often]
,avg(Cast(Healthcare as float)) as Average
,count(Healthcare) as N_Healthcare
from Member
where Healthcare > '0'
UNION ALL等。
Stack Overflow用户
发布于 2012-08-08 04:18:28
问题是where语句为每个事件提供了不正确的计数。医疗保健计数应该是54，体育54和其他13，而不是所有计数是55。
select
sum(case when Healthcare ='1' then 1 else 0 end) as [Healthcare_never],
sum(case when Healthcare ='2' then 1 else 0 end) as [Healthcare_not often],
sum(case when Healthcare ='3' then 1 else 0 end) as [Healthcare_average],
sum(case when Healthcare ='4' then 1 else 0 end) as [Healthcare_often],
sum(case when Healthcare ='5' then 1 else 0 end) as [Healthcare_very often],
avg(Cast(Healthcare as float)) as Average,
count(Healthcare) as N_Healthcare,
sum(case when Religious ='1' then 1 else 0 end) as [Religious_never],
sum(case when Religious ='2' then 1 else 0 end) as [Religious_not often],
sum(case when Religious ='3' then 1 else 0 end) as [Religious_average],
sum(case when Religious ='4' then 1 else 0 end) as [Religious_often],
sum(case when Religious ='5' then 1 else 0 end) as [Religious_very often],
Avg(cast(Religious as float)) as Average,
count(Religious) as N_Religious,
sum(case when Sport ='1' then 1 else 0 end) as [Sport_never],
sum(case when Sport ='2' then 1 else 0 end) as [Sport_not often],
sum(case when Sport ='3' then 1 else 0 end) as [Sport_average],
sum(case when Sport ='4' then 1 else 0 end) as [Sport_often],
sum(case when Sport ='5' then 1 else 0 end) as [Sport_very often],
Avg(cast(Sport as float)) as Average,
count(Sport) as N_Sport
from contacts_cstm
where Sport > '0' or Religious > '0' or Healthcare >'0'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11853147
复制相关文章
相似问题
腾讯云开发者
