有两个表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
1 1
2 1
3 2
4 1
5 3
6 2我想得到的是
F_place_name(部位) count(F_stu_id)(检查人数) sum(f_price)(该部位合计金额)
如 胸部CT 3 900
请问如何写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
1 1
2 1
3 2
4 1
5 3
6 2我想得到的是
F_place_name(部位) count(F_stu_id)(检查人数) sum(f_price)(该部位合计金额)
如 胸部CT 3 900
请问如何写sql
from dict_place A, exam_place B
where A.F_place_id=B.F_place_id
group by A.F_place_name
from dict_place inner join exam_place on dict_place.F_place_id = exam_place.F_place_id
group by F_place_id
select max(F_place_name), count(*), sum(f_price)
from dict_place inner join exam_place on dict_place.F_place_id = exam_place.F_place_id
group by dict_place.F_place_id
from dict_place inner join exam_place on dict_place.F_place_id = exam_place.F_place_id
group by F_place_id
直接用
select F_place_name, Count(*) as 检查人数, sum(f_price) as 该部位合计金额 from dict_place group by f_place_Name
直接对表1里的相同检查部位的人数,和金额汇总就可以了
不连接exam_place怎么能够正确求出检查人数呢?
因为部位检查人数信息保存在exam_place表中