SQL查询，通过打印组号码太多记录

Question

我有两个下表

Emp_ID FirstName 
------------------------------ 
1   John 
2   Mark 
3   Steve 

表2：Emp_Transaction

Trans ID Emp_ID start_Date End_date Paid_status Total_Hours  Total_Pay 
--------------------------------------------------------- 
1    1  01/07/13 01/14/2013  True  15.00  75.00 
2    1  01/15/13 01/21/2013  False  10.00  50.00 
3    1  01/21/13 01/27/2013  False  10.00  50.00 

4    2  01/07/13 01/14/2013  False  15.00  75.00 
5    2  01/15/13 01/21/2013  False  10.00  50.00 
6    2  01/21/13 01/27/2013  False  10.00  50.00 

7    3  01/07/13 01/14/2013  True  15.00  75.00 
8    3  01/15/13 01/21/2013  True  10.00  50.00 
9    3  01/21/13 01/27/2013  False  10.00  50.00 

结果应打印像这样的（最早的无偿日期）

ID FirstName start_Date End_date Paid_status Total_Hours  Total_Pay 
-------------------------------------------------------------- 
1    John  01/15/13 01/21/2013 False  10.00  50.00    
2    Mark  01/07/13 01/14/2013 False  15.00  75.00    
3    steve  01/21/13 01/27/2013 False  10.00  50.00 

Answer 1

既然你没有提到你正在使用的RDBMS，下面的查询几乎适用于所有RDBMS（否■所有）

SELECT a.*, b.* 
FROM users a 
     INNER JOIN Emp_transaction b 
      ON a.Emp_ID = b.Emp_ID 
     INNER JOIN 
     (
      SELECT Emp_ID, MIN(start_DATE) min_date 
      FROM emp_transaction 
      WHERE Paid_Status = 'False' 
      GROUP BY Emp_ID 
     ) c ON b.Emp_ID = c.Emp_ID AND 
       b.start_date = c.min_DATE 

• SQLFiddle Demo

但是如果你的RDBMS支持Window Functions，

SELECT a.*, 
     b.TransID, b.start_Date, b.End_date, 
     b.Paid_status, b.Total_Hours, b.Total_Pay 
FROM users a 
     INNER JOIN 
     (
      SELECT TransID, Emp_ID, start_Date, End_date, Paid_status, Total_Hours, Total_Pay, 
        ROW_NUMBER() OVER (PARTITION BY Emp_ID ORDER BY start_Date ASC) rn 
      FROM emp_transaction 
      WHERE Paid_status = 'False' 
     ) b ON a.Emp_ID = b.Emp_ID AND 
       b.rn = 1 

• SQLFiddle Demo