mysql - 在具有相同 id 的另一个表上查询数据(行)并仅使用一个数据删除结果

Question

这里有一个 SELECT 查询返回数据的示例，但我想删除或排除仅返回 1 个数据的结果。

SELECT m.roverbook,
       m.Sequence,
       m.AccountNumber,
       m.Name,
       c.duedate,
       c.amount as Bill
FROM unpaidbills c
LEFT JOIN master m ON (m.id = c.uid)
WHERE m.roverbook = '010101'
  AND c.duedate < '2017-01-10'
ORDER BY m.Sequence

结果样本

roverbook   Sequence    AccountNumber   Name    duedate Bill
10101   1001    1010010 CHUA, SOLIDAD   1/5/2003    514.41
10101   1001    1010010 CHUA, SOLIDAD   2/5/2003    614.97
10101   1001    1010010 CHUA, SOLIDAD   6/5/2003    264.58
10101   1002    1010040 CRUZ, BENITO    10/5/2001   1156.26
10101   1002    1010040 CRUZ, BENITO    11/5/2001   1775.42
10101   1003    1010051 SERRANO, NOMER  10/5/2001   633.41
10101   1004    1010221 C H A P E L 11/7/2014   128.41

我想去掉只有一个Bill的结果

Answer 1

SELECT m.roverbook,
   m.Sequence,
   m.AccountNumber,
   m.Name,
   c.duedate,
   c.amount as Bill
FROM unpaidbills c
LEFT JOIN master m ON (m.id = c.uid)
Left join
(
   SELECT 
   m.id,
   count(c.uid) as BillCount
   FROM unpaidbills c
   LEFT JOIN master m ON (m.id = c.uid)
   WHERE m.roverbook = '010101'
   AND c.duedate < '2017-01-10'
   group by m.id
   having count(c.uid)>1
 ) MorethanoneBill
 on(m.id = MorethanoneBill.id)
 WHERE m.roverbook = '010101'
 AND c.duedate < '2017-01-10'
 ORDER BY m.Sequence