Question

sql 一个sql语句返回同一张表两个count

表ID 状态1 12 23 14 15 06 1 7 18 2 9 110 2 我想要的结果状态为1的总数 状态为2的总数 不知道能否在一个sql语句实现比如：slelect count(),count() from 表 where....................不知道该怎么写。。不要用 union all

Answer 1

select sum(case [状态] when 1 then 1 else 0 end) as [状态为1], sum(case [状态] when 2 then 1 else 0 end) as [状态为2] from [表]

Answer 2

可以这样： SELECT [状态],Count(*) as 总数 FROM [表] Where [状态]=0 or [状态]=1 Group by [状态]

Answer 3

select top 1
(select COUNT(state) from [表] where state=2),
(select COUNT(state) from [表] where state=1)
from [表]

Answer 4

declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql +
'状态' + cast(状态 as varchar(10)) + '=sum(coalesce(case 状态 when ' + cast(状态 as varchar(10)) + ' then 1 end, 0)),'
from 表 group by 状态
set @sql = left(@sql, LEN(@sql)-1) + ' from 表'
exec (@sql);
go

Answer 5

你说的select count(),count()有点难实现，试试我这句 select state, count(state) from 表名 where state = 1 or state = 2 group by state