sql使用连接在sql中选择一对多的最大值记录 [英] sql select maximum value record in one to many in sql using joins

问题描述

我有3张桌子： -

表1：a（申请人）

Id |名称| DOB

__________________

1 | xyz | 12/01/1990

2 | pqr | 06/02/1995



表2：b（课程）

Id |课程

____________

1 | Btech

2 | Mtech

3 |博士



表3：c

a_Id | b_Id |完成年份

_______________________________

1 | 1 | 2008

1 | 3 | 2015

1 | 2 | 2012

2 | 2 | 2008

2 | 1 | 2006



我需要检索两个申请人使用联接的最高资格



预期结果：

名称|课程|完成年份

________________________________

xyz |博士学位2015

pqr | Mtech | 2008



我正在使用以下脚本：

 选择 * 
 从 a   t1 
  left  外部  join  
（选择 * 来自 C 其中年 in （ SELECT  MAX（年）年 FROM  C  GROUP   BY  a_Id）） as  t2 >  t1.Id = t2.a_Id 
  left   outer   join  b  as  t3  on  t2 。出价 = t3.Id 

但我得到以下结果



实际结果：



名称|课程|完成年份

________________________________

xyz | Mtech | 2012

pqr | Btech | 2006



请帮助

解决方案

除了我使用sql得到完全不同的结果这一事实你到目前为止，Wombaticus已经提出了一个非常有效的观点，你不应该认为在较低的资格后获得更高的资格。



我会使用像< pre lang =sql> CTE as （
选择最大值（b_id） 最高，a_Id
来自 c group by a_Id
）
选择 a。[名称]，b。课程，c.Year_Completed
来自 CTE
内部 加入 a a.Id = CTE.a_Id
内部 加入 b b.Id = CTE.Highest
inner join c on a.Id = c.a_Id 和 c.b_Id = b.Id

公用表表达决定了每个申请人获得的最高资格。然后我可以用它来确定课程的名称并链接到表c以找到申请人获得它的年份。表a仅用于获取申请人的姓名。

试试这个：

 选择 a.name，b.course，c.year_completed 
 来自 a  join  c   a.id = c.a_id  join  b  on  b.id = c.b_id 
 其中​​ c.b_id =（选择 max（b_id）来自 c 其中 c.a_id = a.id）
 订单 按 c.year_completed  desc  

I have 3 tables :-
Table 1 : a (applicant)
Id | Name | DOB
__________________
1 | xyz | 12/01/1990
2 | pqr | 06/02/1995

Table 2: b (Course)
Id | Course
____________
1 | Btech
2 | Mtech
3 | PhD

Table 3 : c
a_Id | b_Id | Year Completed
_______________________________
1 | 1 | 2008
1 | 3 | 2015
1 | 2 | 2012
2 | 2 | 2008
2 | 1 | 2006

I need to retrieve just the highest qualification of both the applicants using joins

Expected Result :
Name | Course | Year Completed
________________________________
xyz | PhD | 2015
pqr | Mtech | 2008

I'm using the below script :

select *
from a as t1
left outer join 
( select * from C where  Year in  (SELECT MAX(Year) Year FROM C GROUP BY a_Id )) as t2 on t1.Id = t2.a_Id
left outer join b as t3 on t2.b_Id = t3.Id

but I'm getting the following result

Actual Result:

Name | Course | Year Completed
________________________________
xyz |Mtech | 2012
pqr |Btech | 2006

Please help

解决方案

Well apart from the fact I get completely different results using the sql you have so far, Wombaticus has raised a perfectly valid point that you shouldn't assume that the higher qualification is gained after the lower qualification.

I would use something like

with CTE as (
    select Max(b_id) as Highest, a_Id
    from c group by a_Id
)
select a.[Name], b.Course, c.Year_Completed
from CTE
inner join a on a.Id = CTE.a_Id
inner join b on b.Id = CTE.Highest
inner join c on a.Id = c.a_Id and c.b_Id = b.Id

The Common Table Expression determines the highest qualification gained by each applicant. I can then use that to determine the name of the course and link to table c to find the year the applicant gained it. Table a is included only to get the name of the applicant.

Try this:

select a.name, b.course, c.year_completed
from a join c on a.id=c.a_id join b on b.id=c.b_id
where c.b_id = (select max(b_id) from c where c.a_id=a.id)
order by c.year_completed desc

这篇关于sql使用连接在sql中选择一对多的最大值记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！