如何只在内连接查询的第二个表中选择第一行 - How to select first row only in 2nd table of inner join query

我使用下面的查询从3个不同的表中选择数据。Tbl_invoices和tbl_clients有唯一的记录。每个tbl_invoices记录有多个tbl_invoice_entries记录:

$query = 'SELECT T1.*, T2.*, T3.* 
                FROM tbl_invoices T1 
                LEFT JOIN tbl_invoice_entries T2
                ON T1.number = T2.invoice_number
                LEFT JOIN tbl_clients T3
                ON T1.client = T3.client_id
                WHERE date_format(date, '%Y') = ".$_POST['year']." AND date_format(date, '%c') = ".$_POST['month']." ORDER BY date, number ASC'
$stmt = $conn->prepare($query)
$stmt->execute();    
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

当前返回tbl_invoice_entries中的所有记录。如何更改查询以仅返回每个tbl_invoices记录的第一个tbl_invoice_entries记录?

表格如下:

tbl_clients

+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | John      | Doe      |
|  2 | Jane      | Doe      |
+----+-----------+----------+

tbl_invoices

+----+--------+--------+------------+
| id | number | client |    date    |
+----+--------+--------+------------+
|  1 |     14 |      1 | 2015-07-14 |
|  1 |     15 |      2 | 2015-07-14 |
+----+--------+--------+------------+

tbl_invoice_entries

+----+----------------+------------+
| id | invoice_number |  produkt   |
+----+----------------+------------+
|  1 |             14 | Fish       |
|  2 |             14 | Bread      |
|  3 |             15 | Vegetables |
|  4 |             15 | Fruit      |
+----+----------------+------------+

所以我要找的结果是:

约翰·多伊14鱼2015-07-14

简·多伊15蔬菜2015-07-14

谢谢你的帮助!

通过链接invoice_entries表，不是直接通过发票号，而是通过它的第一个条目的id，您可以实现您想要的:

SELECT firstname,lastname,number,product,date
 FROM tbl_invoices T1 
 LEFT JOIN tbl_invoice_entries T2
 ON T2.id =(select min(id) from tbl_invoice_entries 
            where invoice_number=number)
 LEFT JOIN tbl_clients T3
 ON T1.client = T3.id
 WHERE ...

您需要通过第一行告诉RDBMS您的意图。元组中没有自然顺序。如果您希望具有最低ID的元组具有相同的invoice_number，则需要另一个查询

SELECT tbl1.* FROM tbl_invoice_entries AS tbl1
    JOIN ( SELECT MIN(id) AS id, invoice_number FROM tbl_invoice_entries
         GROUP BY invoice_number ) AS tbl2
    USING (id);

上面的查询相当于tbl_invoice_entries，但只有每个发票号的最低ID。您可以将其作为一个VIEW来执行(实际上是两个，因为您不能在VIEW中使用子查询):

CREATE VIEW tbl_invoice_entries_firstnumber AS
  SELECT MIN(id) AS id, invoice_number
  FROM tbl_invoice_entries
  GROUP BY invoice_number;
CREATE VIEW tbl_invoice_entries_first AS
  SELECT tbl1.* FROM tbl_invoice_entries AS tbl1
  JOIN tbl_invoice_entries_firstnumber
  USING (id);

之后，您可以在当前查询中使用tbl_invoice_entries_first而不是tbl_invoice_entries。

请记住，视图是动态的，所以它只是一个更复杂查询的简写。这意味着当前的查询将变得更复杂，需要更长的时间:

SELECT T1.*, T2.*, T3.* 
    FROM tbl_invoices AS T1 
    LEFT JOIN tbl_invoice_entries_first AS T2
        ON T1.number = T2.invoice_number
    LEFT JOIN tbl_clients AS T3
        ON T1.client = T3.id; -- you have no client_id in T3

我在这里放了一把小提琴。

或者您可以更多地修改您的查询，并在T2上添加JOIN条件，以便它只获取最小ID -或者您喜欢的任何排序条件:

SELECT T1.*, T2.*, T3.* 
    FROM tbl_invoices AS T1 
    LEFT JOIN tbl_invoice_entries AS T2
        ON (
          -- (( T1.number = T2.invoice_number AND  )) --
          T2.id = (
          SELECT MIN(id) FROM tbl_invoice_entries 
          WHERE invoice_number = number
        ))
    LEFT JOIN tbl_clients AS T3
        ON T1.client = T3.id;

UPDATE: number检查被注释掉了(参见@cars10的解决方案)，因为它被内部子查询结转。

最后你可以在代码中做到这一点，即你保存前一个元组的值并根据需要排序查询;然后丢弃所有不需要的元组。如果每张发票的条目很少，那么这可能是值得的:

 // pseudo code
 if (prev.client == tuple.client)
    and
    (prev.invoice == tuple.invoice)
        continue;
 prev = tuple;
 -- use tuple.