Sql语句获取 统计值

[lzid2008]

现有张表 Table1
字段 [uId]
,[uName]
,[p1_weiBo]
,[p2_wangYi]
,[p3_renRen]
,[p4_douBan]
,[p5_youKu]

其中
[p1_weiBo] ,[p2_wangYi],[p3_renRen],[p4_douBan],[p5_youKu]
就2个数值 Yes 或者 No

我现在想用一句Sql语句，获取 用户P1...P5的 Yes 的数量.

请高人指点，谢谢

[中国风]

是Bit

直接換為int 用+就行了

[jwdream2008]

select [uId]
  ,[uName] 
,(case when [p1_weiBo]='Yes' then 1 else 0 end)+
(case when [p2_weiBo]='Yes' then 1 else 0 end)+
(case when [p3_weiBo]='Yes' then 1 else 0 end)+
(case when [p4_weiBo]='Yes' then 1 else 0 end)+
(case when [p5_weiBo]='Yes' then 1 else 0 end) as NUM
from Table1

[--小F--]

select
  sum(case weiBo when 'yes' then 1 else 0 end) as 数量
from
  (
   select  p1_weiBo from Table1
   union all
   select  p2_weiBo from Table1
   union all
   select  p3_weiBo from Table1
   union all
   select  p4_weiBo from Table1
   union all
   select  p5_weiBo from Table1
)t

[中国风]

SELECT *
[uId]
  ,[uName]  
  ,CAST([p1_weiBo]  AS int)+CAST([p2_wangYi]  AS int)+CAST([p3_renRen]  AS int)+CAST([p4_douBan]  AS int)+CAST([p5_youKu]  AS int)
FROM table1

[jwdream2008]

select [uId]
  ,[uName] 
,(case when [p1_weiBo]='Yes' then 1 else 0 end)+
(case when [p2_wangYi]='Yes' then 1 else 0 end)+
(case when [p3_renRen]='Yes' then 1 else 0 end)+
(case when [p4_douBan]='Yes' then 1 else 0 end)+
(case when [p5_youKu]='Yes' then 1 else 0 end) as NUM
from Table1

[中国风]

字符也可這樣用

SELECT 
	[uId]
	,[uName]  
	,(LEN(col)-REPLACE(col,'Yes',''))/3
FROM 
(SELECT 
	[uId]
	,[uName]
,[p1_weiBo]+[p2_wangYi]+[p3_renRen] +[p4_douBan]+[p5_youKu] AS col 
FROM table1
)t

[--小F--]

select
  uid,uname,sum(case weiBo when 'yes' then 1 else 0 end) as 数量
from
  (
   select  uid,uname,p1_weiBo from Table1
   union all
   select  uid,uname,p2_weiBo from Table1
   union all
   select  uid,uname,p3_weiBo from Table1
   union all
   select  uid,uname,p4_weiBo from Table1
   union all
   select  uid,uname,p5_weiBo from Table1
)t
group by
   uid,uname