34,590
社区成员
发帖
与我相关
我的任务
分享
declare @tab table(iRoom_ID int,iBed_Sex int,Bed_UseState int);
-- 加了点数据
insert into @tab
select 216,1,2 union all select 216,1,2 union all
select 216,0,0 union all select 217,2,2 union all
select 217,0,0 union all select 217,2,2 union all
select 218,0,0 union all select 218,0,0 union all
select 218,1,2 union all select 219,2,2 union all
select 219,2,2 union all select 219,0,0;
select Sex,SUM(unused) Patient from
(select iRoom_ID,
(select top 1 iBed_Sex from @tab where iRoom_ID=t.iRoom_ID and Bed_UseState=2) sex,
(select COUNT(*) from @tab where iRoom_ID=t.iRoom_ID and Bed_UseState=0) unused
from @tab t group by iRoom_ID) t
group by sex;
/*
Sex Patient
----------- -----------
1 3
2 2
*/
---测试数据---
if object_id('[Bed]') is not null drop table [Bed]
go
create table [Bed]([iBed_ID] int,[sBed_ID] varchar(3),[iSection_ID] int,[iRoom_ID] int,[iBed_State] int,[iBed_type] int,[iBed_Sex] int,[Bed_UseState] int)
insert [Bed]
select 92,'B34',179,216,1,0,1,2 union all
select 93,'B36',179,216,1,0,1,2 union all
select 232,'B35',179,216,1,0,0,0 union all
select 216,'B38',179,217,1,1,2,2 union all
select 94,'B37',179,217,1,0,0,0 union all
select 95,'B39',179,217,1,0,2,2
---查询---
select
sum(case when iBed_Sex=1 or iBed_Sex=0 then cnt else 0 end) as 男性可入住人数,
sum(case when iBed_Sex=2 or iBed_Sex=0 then cnt else 0 end) as 女性可入住人数
from
(
select
iRoom_ID ,max(iBed_Sex) as iBed_Sex,sum(case when Bed_UseState=0 then 1 else 0 end) cnt
from bed
group by iRoom_ID
) t
---结果---
男性可入住人数 女性可入住人数
----------- -----------
1 1
(1 行受影响)
select
iRoom_ID,
sum(case when Bed_UseState=0 and iBed_Sex=1 then 1 else 0 end) as '男性病人',
sum(case when Bed_UseState=0 and iBed_Sex=0 then 1 else 0 end) as '女性病人'
from
bed
group by
iRoom_ID
select
Bed_UseState,iRoom_ID,
sum(case when Bed_UseState=0 and iBed_Sex=1 then 1 else 0 end) as '男性病人',
sum(case when Bed_UseState=0 and iBed_Sex=0 then 1 else 0 end) as '女性病人'
from
bed
group by
Bed_UseState,iRoom_ID