几个关于mysql的问题

Pool

最近我开始买mysql的书来自学，我照这他们的练习来做，可是想来想去都想不到以下的几个COMMAND要怎样开始写

1. print the name of the patient that cost the most money (i.e sum of all treatments cost was hightest)最高时P4，因为他要付29.97
2. print the name of the patient that did not have treatment 11111. 我做了，但它显示所有的patient names，我要的答案是Alex, Albert, Sue, Richard 的名字
3.print the nme of the INS company that had to pay the most money.
4.print the name of the MD that prescibed the treatments that cost the most money

以下是我做的tables
INS

MDS

MP

TREATMENT

PATIENTS


有人指导下吗

blur_dolphin

试试以下的statements。可能不是完全对也不是最好的答案。希望会对你有帮助。

1。
SELECT p.pcode AS patient_code, p.name AS patient_name, SUM(mp.price) AS total FROM TREATMENT AS t LEFT JOIN PATIENTS AS p ON (t.pcode = p.pcode) LEFT JOIN MP AS mp ON (t.mdcode = mp.mpcode) GROUP BY patient_code, patient_name ORDER BY total DESC LIMIT 1;

2。
SELECT pcode AS patient_code, name AS patient_name FROM PATIENTS WHERE pcode not in (SELECT pcode FROM TREATMENT WHERE mpcode = '1111');

3。
SELECT ins.inscode AS ins_code, ins.name AS ins_name, SUM(mp.price) AS total FROM TREATMENT AS t LEFT JOIN PATIENTS AS p ON (t.pcode = p.pcode) LEFT JOIN INS AS ins ON (p.inscode = ins.inscode) LEFT JOIN MP AS mp ON (t.mdcode = mp.mpcode) GROUP BY ins_code, ins_name ORDER BY total DESC LIMIT 1;

4。
SELECT mds.mdcode AS mds_code, mds.name AS mds_name, SUM(mp.price) AS total FROM TREATMENT AS t LEFT JOIN MDS AS mds ON (t.mdcode = mds.mdcode) LEFT JOIN MP AS mp ON (t.mpcode = mds.mpcode) GROUP BY mds_code, mds_name ORDER BY total DESC LIMIT 1;