sql - 选择每一行被另一个表联接的最小值

我有下表：

           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。

最佳答案

用：

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

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

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