count(CASE when dbo.ZY_病案库.出院标志 < 4 THEN dbo.ZY_病案库.出院标志 ELSE 0
END) AS 在院人数,
count(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 >= 0 THEN dbo.ZY_病案库.出院标志 ELSE 0 END) AS 正常出院人数,
count(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 < 0 THEN dbo.ZY_病案库.出院标志 ELSE 0 END) AS 欠费出院人数,
你据这个句改成如下:Sum(CASE when dbo.ZY_病案库.出院标志 < 4 THEN 1 ELSE 0
END) AS 在院人数,
Sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 >= 0 THEN dbo.1 ELSE 0 END) AS 正常出院人数,
Sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 < 0 THEN 1 ELSE 0 END) AS 欠费出院人数,
END) AS 在院人数,
count(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 >= 0 THEN dbo.ZY_病案库.出院标志 ELSE 0 END) AS 正常出院人数,
count(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 < 0 THEN dbo.ZY_病案库.出院标志 ELSE 0 END) AS 欠费出院人数,
你据这个句改成如下:Sum(CASE when dbo.ZY_病案库.出院标志 < 4 THEN 1 ELSE 0
END) AS 在院人数,
Sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 >= 0 THEN dbo.1 ELSE 0 END) AS 正常出院人数,
Sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 < 0 THEN 1 ELSE 0 END) AS 欠费出院人数,
如:ZY_病案库.dbo.出院标志 把COUNT里的ELSE 0去掉!
END) AS 在院人数,
sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 >= 0
and dbo.ZY_病案库.出院标志=1 then 1 ELSE 0 END) AS 正常出院人数,
sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 < 0
and dbo.ZY_病案库.出院标志=1 then 1 ELSE 0 END) AS 欠费出院人数,
sum(CASE when dbo.ZY_病案库.出院标志 < 4 then 1 ELSE 0
END) AS 在院人数,
sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 >= 0
then 1 ELSE 0 END) AS 正常出院人数,
sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 < 0
then 1 ELSE 0 END) AS 欠费出院人数,
请各位高手注意:我的问题不是count(case when...)的用法问题,而是:
我要加上的这三条分组条件和原来的不一致:原来的是GROUP BY 科室,材质分类,新加的三条COUNT是GROUP BY 科室,只要每个科室得到一个结果即可,
我原来是这样做的:先将第一段查询结果INSERT INTO 到一个表,再将要加的三条结果一个个的写进去,非常慢,我想如能合成一条语句最好。
请高手再帮忙看一下!!
“dbo.ZY_病案库”“dbo.ZY_记帐流水帐”各是一个表
(SELECT dbo.ZY_病案库.科室,dbo.ZY_记帐流水帐.材质分类,
SUM(CASE when dbo.ZY_病案库.出院标志 < 4 THEN dbo.ZY_记帐流水帐.实交金额 ELSE 0
END) AS 在院费用, SUM(CASE when (dbo.ZY_病案库.出院标志 >= 4 AND
dbo.ZY_病案库.出院交退款 >= 0)
THEN dbo.ZY_记帐流水帐.实交金额 ELSE 0 END) AS 正常出院费用,
SUM(CASE when (dbo.ZY_病案库.出院标志 >= 4 AND dbo.ZY_病案库.出院交退款 < 0)
THEN dbo.ZY_记帐流水帐.实交金额 ELSE 0 END) AS 欠费出院费用
FROM dbo.ZY_记帐流水帐 INNER JOIN
dbo.ZY_病案库 ON
dbo.ZY_记帐流水帐.病人编号 = dbo.ZY_病案库.病人编号
where dbo.ZY_记帐流水帐.月结帐日期 is null
GROUP BY dbo.ZY_病案库.科室,dbo.ZY_记帐流水帐.材质分类) a,
(select
sum(CASE when dbo.ZY_病案库.出院标志 < 4 then 1 ELSE 0
END) AS 在院人数,
sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 >= 0
then 1 ELSE 0 END) AS 正常出院人数,
sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 < 0
then 1 ELSE 0 END) AS 欠费出院人数 from from dbo.ZY_病案库
GROUP BY dbo.ZY_病案库.科室 ) b where a.科室=b.科室
我指本题中COUNT用法
因为count(0) 跟count(*)没有区别
谢谢您的解答,可我试了一下,出错:
服务器: 消息 207,级别 16,状态 3,行 1
Invalid column name '科室'.
sum(CASE when dbo.ZY_病案库.出院标志 < 4 then 1 ELSE 0
END) AS 在院人数,
sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 >= 0
then 1 ELSE 0 END) AS 正常出院人数,
sum(CASE when dbo.ZY_病案库.出院标志 = 4 and dbo.ZY_病案库.出院交退款 < 0
then 1 ELSE 0 END) AS 欠费出院人数 from from dbo.ZY_病案库
GROUP BY dbo.ZY_病案库.科室 )