使用 max 函数的 SQL 内连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13899407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL inner join with max function
提问by Mufasil
i have some problem in this query i want to see who paid Max fees to which doctor? i have created 3 tables name are tblPatient,tblClinic and tblDoctor now i have set the foreign keys on tblClinic table in PID and DID column now what can i do in this query so i will get result which i want.
我在这个查询中遇到了一些问题，我想看看谁向哪个医生支付了最高费用？我已经创建了 3 个表名称，分别是 tblPatient、tblClinic 和 tblDoctor 现在我已经在 PID 和 DID 列中的 tblClinic 表上设置了外键，现在我可以在这个查询中做什么，所以我会得到我想要的结果。
create database Hospital
use Hospital
create table tblPatient
(
PID int identity(100,1) primary key not null,
PName varchar(20),
PPhone varchar(20)
)
create table tblClinic
(
CID int identity(300,1) primary key not null,
PID int,
DID int,
Fees int,
foreign key(PID) references tblPatient(PID),
foreign key(DID) references tblDoctor(DID)
)
create table tblDoctor
(
DID int identity(200,1) primary key not null,
DName varchar(20),
DPhone varchar(20),
)
insert into tblPatient values('Zeeshan',033314785)
insert into tblPatient values('Mufaddil',034521548)
insert into tblPatient values('Shayan',033236981)
insert into tblPatient values('Zahid',0334425632)
insert into tblPatient values('Afzaal',030178945)
insert into tblClinic values (100,201,1500)
insert into tblClinic values (102,202,1600)
insert into tblClinic values (103,204,700)
insert into tblClinic values (101,201,800)
insert into tblClinic values (101,200,600)
insert into tblClinic values (103,202,650)
insert into tblClinic values (104,202,550)
insert into tblClinic values (102,203,840)
insert into tblClinic values (101,203,2000)
insert into tblClinic values (100,204,250)
insert into tblClinic values (100,201,1700)
insert into tblClinic values (101,202,1650)
insert into tblClinic values (104,204,300)
insert into tblDoctor values ('Dr.Amir',033412345)
insert into tblDoctor values ('Dr.Bilal',034554125)
insert into tblDoctor values ('Dr.Saim',033358741)
insert into tblDoctor values ('Dr.Rizwan',033325871)
insert into tblDoctor values ('Dr.Kamran',030025874)
select * from tblPatient
select * from tblClinic
select * from tblDoctor
select p.PName,d.DName
from tblPatient p
inner join
tblClinic c
ON p.PID=c.PID
inner join
tblDoctor d
ON d.DID=c.DID
where PName in ('Zeeshan','Shayan','Afzaal')
select d.DName,p.PName
from tblDoctor d
inner join
tblClinic c
ON d.DID=c.DID
inner join
tblPatient p
ON p.PID=c.PID
where d.DName in ('Dr.Bilal','Dr.Kamran','Dr.Amir')
order by d.DName,p.PName asc
select d.DName,p.PName,c.Fees
from tblDoctor d
inner join
tblClinic c
ON d.DID=c.DID
inner join
tblPatient p
ON p.PID=c.PID
where d.DName in ('Dr.Bilal','Dr.Kamran','Dr.Amir')
order by d.DName,p.PName asc
回答by Terje D.
If you want to find which patient has paid the maximum fee for each of the doctors, this query should do:
如果你想找出哪个病人为每个医生支付了最高费用，这个查询应该做：
SELECT P.PName, D.DName, C.Fees
FROM tblClinic C
INNER JOIN tblPatient P ON P.PID = C.PID
INNER JOIN tblDoctor D ON D.DID = C.DID
WHERE C.Fees =
(SELECT max(Fees) FROM tblClinic C2
WHERE C2.DID = C.DID)
回答by Kaf
Try this. Here is the working example from your data
select pname,dname, fee
from (
select pname, dname, max(fees) fee,
rank() over (order by p.pname) rk,
row_number() over (order by p.pname,max(fees) desc) rn
from tblClinic c join tblPatient p on c.pid = p.pid
join tblDoctor d on c.did = d.did
group by pname,dname
) T
where rk = rn
Results
结果
Afzaal Dr.Saim 550
Mufaddil Dr.Rizwan 2000
Shayan Dr.Saim 1600
Zahid Dr.Kamran 700
Zeeshan Dr.Bilal 1700
