通过另一个表选择每个行连接的最小值 [英] Select the minimum value for each row join by another table

问题描述

我有以下表格：

I have the following table:

           Table1                         Table2        
CardNo  ID   Record   Date          ID    Name      Dept    
1      101   8.00    11/7/2013      101   Danny     Green   
2      101   13.00   11/7/2013      102   Tanya     Red 
3      101   15.00   11/7/2013      103   Susan     Blue    
4      102   11.00   11/7/2013      104   Gordon    Blue    
5      103   12.00   11/7/2013                  
6      104   12.00   11/7/2013                  
7      104   18.00   11/7/2013                  
8      101    1.00   12/7/2013                  
9      101   10.00   12/7/2013                  
10     102    0.00   12/7/2013                  
11     102    1.00   12/7/2013                  
12     104    3.00   12/7/2013                  
13     104    4.00   12/7/2013  

我希望结果如同这：

Name    Dept    Record
Danny   Green   8.00
Tanya   Red     11.00
Susan   Blue    12.00
Gordon  Blue    18.00

其中结果仅显示每个名称的记录的最小值，并按所选日期进行过滤。我使用SQL。

where the result is only showing the minimum value of "Record" for each "Name", and filtered by the date selected. I'm using SQL.

推荐答案

使用：

select t2.Name, t2.Dept, min(t1.Record)
from table1 t1
join table2 t2 on t2.ID = t1.ID
group by t2.ID, t2.Name, t2.Dept

或

select t2.Name, t2.Dept, a.record
from table2 t2
join
(
    select t1.ID, min(t1.Record) [record]
    from table1 t1
    group by t1.ID
)a
on a.ID = t2.ID

要过滤查询，请添加其中子句，例如：

To filter query, add where clause, e.g.:

select t2.Name, t2.Dept, min(t1.Record)
from table1 t1
join table2 t2 on t2.ID = t1.ID
where t1.Date = '11/7/2013'
group by t2.ID, t2.Name, t2.Dept

这篇关于通过另一个表选择每个行连接的最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！