本文介绍了通过另一个表选择每个行连接的最小值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下表格:
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更多推荐
通过另一个表选择每个行连接的最小值
发布评论