有两个表dict_place(字典表),exam_place(检查表)关联
dict_place里有
F_place_id F_place_name F_price
1 胸部Ct 300
2 脑部Ct 400
3 肺部Ct 200exam_place里有
F_stu_id(病人编号) F_place_id F_place_mas(阳性)
1 1 0
2 1 1(阳性)
3 2 0
4 1 0
5 3 1
6 2 0 我想统计得到的是
F_place_name(部位)count(F_stu_id)(检查人数)count(F_place_mas)阳性数 阳性率
如 胸部CT 3 1 33.3%
请问如何写sql
dict_place里有
F_place_id F_place_name F_price
1 胸部Ct 300
2 脑部Ct 400
3 肺部Ct 200exam_place里有
F_stu_id(病人编号) F_place_id F_place_mas(阳性)
1 1 0
2 1 1(阳性)
3 2 0
4 1 0
5 3 1
6 2 0 我想统计得到的是
F_place_name(部位)count(F_stu_id)(检查人数)count(F_place_mas)阳性数 阳性率
如 胸部CT 3 1 33.3%
请问如何写sql
(
Select a.F_place_id,Count(*) as JCRS from dict_place a,exam_place b
where a.F_place_id =b.F_place_id
Group by a.F_place_id
) a,
(
Select a.F_place_id,Count(*) as JCRS from dict_place a,exam_place b
where a.F_place_id =b.F_place_id and b.F_place_mas=1
Group by a.F_place_id
) b
where a.F_place_id=b.F_place_id
Select F_place_name,a.JCRS,b.YXS,IIF(a.JCRS = 0,0,b.YXS/a.JCRS) from
因为a.JCRS可能是0啊。
t2.F_place_name as 部位,
Count(t2.F_stu_id) as 检查人数,
(Select Count(t3.F_stu_id)
from exam_place t3
where t3.F_place_id = t2.F_place_id and F_place_mas = 1
) as 阳性数,
convert(varchar(8), '') as 阳性率
into #temp
from dict_place t1, exam_place t2
where t1.F_place_id = t2.F_place_iddelete from #temp where 阳性数 = 0update #temp
set 阳性率 = convert(varchar(5), convert(decimal(4,1), round((convert(float, 阳性数) / convert(float, 检查人数)), 3) * 100)) + '%'
--这里要将两个人数转换为浮点数相除,因为两个整数相除在sql查询中默认是执行整除操作select * from #tempdrop table #temp
如果是单机版的程序用SQL和用DELPHI做相同的工作速度基本一样。如果c/s服务器速度要是比单机快,那用sql快,但是会加重服务器的负荷。
DECLARE @YXS Int,@F_place_name varchar(10)Insert Into #Temp (检查人数,部位)
SELECT COUNT(exam_place.F_place_id) AS 检查人数, dict_place.F_place_name AS 部位
FROM exam_place INNER JOIN
dict_place ON exam_place.F_place_id = dict_place.F_place_id
GROUP BY dict_place.F_place_name, dict_place.F_place_idDECLARE YTemp CURSOR FOR
SELECT COUNT(exam_place.F_place_id) AS 阳性数,dict_place.F_place_name As 部位
FROM exam_place INNER JOIN
dict_place ON exam_place.F_place_id = dict_place.F_place_id
WHERE exam_place.F_place_mas = 1
GROUP BY exam_place.F_place_id, dict_place.F_place_name
Open YTemp
FETCH NEXT FROM YTemp Into @YXS,@F_place_name
WHILE @@FETCH_STATUS = 0
BEGIN
Update #Temp Set 阳性数=@YXS Where 部位=@F_place_name
FETCH NEXT FROM YTemp Into @YXS,@F_place_name
END
Close YTemp
Deallocate YTempUpdate #Temp Set 阳性率=阳性数*1.0/检查人数Select * From #Temp