DECLARE @tb TABLE([房间号] int, [桌号] int, [标志] varchar(100))
INSERT INTO @tb
SELECT 1, 101, '使用'
UNION ALL SELECT 1, 102, '未用'
UNION ALL SELECT 1, 103, '禁用'
UNION ALL SELECT 2, 201, '使用'
UNION ALL SELECT 2, 201, '使用'
SELECT 房间号, SUM(CASE 标志 WHEN '使用' THEN 1 ELSE 0 END) AS 使用,
SUM(CASE 标志 WHEN 未用 THEN 1 ELSE 0 END) AS 未用,
SUM(CASE 标志 WHEN 禁用 THEN 1 ELSE 0 END) AS 禁用
FROM @tb
GROUP BY 房间号
select 房间号 ,
sum(case when 标志='使用' then 1 else 0 end),
sum(case when 标志='未用' then 1 else 0 end),
sum(case when 标志='禁用' then 1 else 0 end)
from 表
group by 房间号
declare @t table(房间号 int,桌号 int,标志 varchar(4))
insert into @t select 1,101,'使用'
insert into @t select 1,102,'未用'
insert into @t select 1,103,'禁用'
insert into @t select 2,201,'使用'
insert into @t select 2,201,'使用'
select 房间号 ,
sum(case when 标志='使用' then 1 else 0 end) as 使用,
sum(case when 标志='未用' then 1 else 0 end) as 未用,
sum(case when 标志='禁用' then 1 else 0 end) as 禁用
from @t
group by 房间号
select 房间号 ,
sum(case when 标志='使用' then 1 else 0 end) as 使用,
sum(case when 标志='未用' then 1 else 0 end) as 未用,
sum(case when 标志='禁用' then 1 else 0 end) as 禁用
from 表
group by 房间号
declare @t table(房间号 int,桌号 int,标志 varchar(4))
insert into @t select 1,101,'使用'
insert into @t select 1,102,'未用'
insert into @t select 1,103,'禁用'
insert into @t select 2,201,'使用'
insert into @t select 2,201,'使用'
select
房间号,
sum(case 标志 when '使用' then 1 else 0 end) as 使用,
sum(case 标志 when '未用' then 1 else 0 end) as 未用,
sum(case 标志 when '禁用' then 1 else 0 end) as 禁用
from
@t
group by
房间号
select 房间号 ,
sum(case when 标志='使用' then 1 else 0 end),
sum(case when 标志='未用' then 1 else 0 end),
sum(case when 标志='禁用' then 1 else 0 end)
from 表
group by 房间号
select
房间号,
sum(case 标志 when '使用' then 1 else 0 end) as 使用,
sum(case 标志 when '未用' then 1 else 0 end) as 未用,
sum(case 标志 when '禁用' then 1 else 0 end) as 禁用
from
已知表
group by
房间号