Question

请教一个SQL语句的写法

查询1：
select
a.CXMMC '项目名称',
sum(case c.XB when '男' then 1 else 0 end) '男检出数',
sum(case c.XB when '女' then 1 else 0 end) '女检出数',
sum(case when c.XB='男' or c.XB='女' then 1 else 0 end) '总检出数'
from table1 a
inner join table2 b on b.ZJJL like '%'+a.CXMMC+'%'
inner join table3 c on b.StudyID=c.ID
inner join table4 d on c.DWDM=d.DWDM
WHERE d.DWMC='XX单位'
group by a.CXMMC

查询2：
select
sum(case c.XB when '男' then 1 else 0 end) '男',
sum(case c.XB when '女' then 1 else 0 end) '女',
sum(case when c.XB='男' or c.XB='女' then 1 else 0 end) '总'
from table3 c
inner join table4 d on c.DWDM=d.DWDM
WHERE d.DWMC='XX单位'

我想将查询1增加'男检出率','女检出率','总检出率'，并用百分比表示，分母分别取自查询2中的'男','女','总',请问sql函数或者存储过程该怎么写？

Answer 1

以男检出率为例，其余同。

DECLARE
male_count int;
cursor c1 is
select a.CXMMC '项目名称',
sum(case c.XB when '男' then 1 else 0 end)/male_count '男检出率',
from table1 a inner join table2 b on b.ZJJL like '%'+a.CXMMC+'%' inner join table3 c on b.StudyID=c.ID
inner join table4 d on c.DWDM=d.DWDM WHERE d.DWMC='XX单位' group by a.CXMMC;
...
BEGIN
open c1;
//取游标结果。
END

追问

代码好像没写完啊？能不能发我一下