一个表一个字段有多种状态
table1
is_repeat
B
C
D
T
V
现在我想用一条SQL语句查出在B,C状态下的数量,和在BCDT状态下的数量,和总数量请问怎么做
table1
is_repeat
B
C
D
T
V
现在我想用一条SQL语句查出在B,C状态下的数量,和在BCDT状态下的数量,和总数量请问怎么做
解决方案 »
- 求高手或专职DBA解答库表结构设计问题!关于字段多少的。
- 如何运行保存为bas文件的DTS包
- A SQL Analysis Service error Ensure that 'SQL Browser' service is running.
- sql server 2005存储过程如何导入SQL2000中
- 请问如何通过Select语句得出这样的结果,急啊!!
- 某时间字段,想默认值为日期怎么做?(格式:yyyy-mm-dd)不要时间,谢谢
- sql mail的问题救急!!
- 存儲過程中用Order By查詢方式打開一個Cursor後,不能Update成功Cursor裡面的當前紀錄
- sql 数据导出/导入之特殊问题
- 关于SQL SERVER登录认证的问题
- 如何 过滤 汉字 数字。。。。
- 新手求sql语句(即时给分)
select
[數量BC]=(select count(*)from table1 where is_repeat in ('B','C')) ,
[數量BCDT]=(select count(*) from table1 where is_repeat in ('B','C','D','T')),
[總數量]=(select count(*) from table1)
insert table1 select
'B',30
union all select 'C',40
union all select 'D',60
union all select 'T',50
union all select 'V',100
union all select 'B',30
union all select 'C',45
union all select 'D',64
union all select 'T',55
union all select 'V',150select
B=sum(case when is_repeat='B' then num end),
C=sum(case when is_repeat='C' then num end),
BCDT=sum(case when is_repeat in('B', 'C', 'D', 'T') then num end)
from table1--result
B C BCDT
----------- ----------- -----------
60 85 374(1 row(s) affected)
这是聚合
insert table1 select 'B'
union all select 'C'
union all select 'D'
union all select 'T'
union all select 'V'
union all select 'B'
union all select 'C'
union all select 'D'
union all select 'T'
union all select 'D'
union all select 'T'
union all select 'V'select
B=sum(case when is_repeat='B' then 1 end),
C=sum(case when is_repeat='C' then 1 end),
BCDT=sum(case when is_repeat in('B', 'C', 'D', 'T') then 1 end)
from table1--result
B C BCDT
----------- ----------- -----------
2 2 10(1 row(s) affected)
(select count(*) as bc from tb where is_repeat in ('B','C')) a,
(select count(*) as bcdt from tb where is_repeat in ('B','C','D','T')) b,
(select count(*) as 总数量 from tb) c