Select the minimum value for each row join by anot

问题:

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  

i want the result to be like this:

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

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

回答1:

Use:

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

or

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

回答2:

Please try:

Select
    b.Name,
    b.Dept,
    MIN(Record) Record
from
    Table1 a join Table2 b on a.ID=b.ID
GROUP BY b.Name,
    b.Dept

回答3:

select 
        b.Name,
        b.Dept,
        min(a.Record)
from Table1 a,Table2 b 
where
        a.ID=b.ID 
group by 
        a.Record