mysql - 在mysql中创建一个链接2个表的查询并仅显示某些信息

Question

我有 2 个表，tbl_students 和 tbl_inv。学生表保存学生的所有信息。我正在创建一个发票系统来为学生创建和保存发票记录(每月)。 tbl_inv 可以为单个学生提供多张发票，但日期不同。我正在尝试创建一个数组，其中将列出所有具有最新发票、总计和状态的学生，并按顶部的最新发票日期排序。我对 mysql 和 php 编程相当陌生，所以如果我的问题听起来很愚蠢，我很抱歉。这就是我要查询的内容......

$query = "SELECT * FROM tbl_student 
  LEFT JOIN tbl_inv ON tbl_student.sid = tbl_inv.inv_sid 
  GROUP BY tbl_student.sid 
  ORDER BY tbl_inv.inv_date DESC";

这将创建一个数组，每个学生一行，但不显示最新的发票日期和详细信息。

如果有人可以提供帮助，我将不胜感激:-)

(从直接评论到现有答案的附加拉动)

这是最终有效的查询..

SELECT 
      S.*, 
      TI2.* 
   FROM 
      tbl_student S 
         LEFT JOIN ( SELECT 
                           TI.inv_sid, 
                           MAX(TI.inv_id) LatestInvoice 
                        FROM 
                           tbl_inv TI 
                        GROUP BY 
                           TI.inv_sid ) PreQuery 
            ON S.sid = PreQuery.inv_sid 
            LEFT JOIN tbl_inv TI2 
               ON PreQuery.inv_sid = TI2.inv_sid 
              AND PreQuery.LatestInvoice = TI2.inv_id 
   ORDER BY 
      TI2.inv_id ASC

我使用学生表上名为 f_link 的字段让学生相互链接。如果学生有与其关联的家庭成员，则 f_link 字段会显示主人 ID 号。如果没有家庭链接，该字段仅显示 0。通过我的发票系统，我正在为学生和与其链接的任何家庭成员创建发票。当前查询不会显示家庭成员的发票数据，但他们已开具发票。我想缩小此查询范围，只显示 f_link 字段中值为 0 的学生。

Answer 1

继续寻找和学习查询...格式和可读性对于 future 的维护来说是一个很大的优势。对于此类问题，询问您首先需要什么...在您的情况下，以每个学生为基础，他们最后一张发票是什么(无论数据如何)。

select
      TI.inv_sid,
      MAX( TI.inv_date ) LatestInvoiceDate
   from
      tbl_inv TI
   group by
      TI.inv_sid

上面将为您提供一个难题，那就是构建了多少复杂查询。然后，您需要学生的姓名以及有关最新发票的发票详细信息。为此，上述查询将在下一阶段使用...在这种情况下，它将为其结果集获取一个“别名”，以获取其余的详细信息。

select
      S.*,
      TI2.*
   from
      tbl_Student S
         LEFT JOIN  ( select
                            TI.inv_sid,
                            MAX( TI.invoicenumber ) LatestInvoiceNumber,
                            count(*) totalInvoicesForStudent,
                            sum( TI.Total ) as TotalAllInvoices,
                            sum( TI.Total - TI.AmountPaid) as OutstandingBalances
                         from
                            tbl_inv TI
                         group by
                            TI.inv_sid ) PreQuery
            on S.sid = PreQuery.inv_sid
            LEFT JOIN tbl_inv TI2
               on PreQuery.inv_sid = TI2.inv_sid
              AND PreQuery.LatestInvoiceNumber = TI2.invoiceNumber
   where
      s.F_Link = 0
   order by
      TI2.inv_date desc

因此，澄清上述查询

对于每个学生(首先列出的表格)，

对包含所有学生最新发票日期的“PreQuery”结果集进行 LEFT JOIN(所有学生，无论是否有发票)。

如果找到这样的发票记录，请再次对发票表执行另一个 LEFT JOIN，但这次是在预查询“student id”和为该用户确定的原始发票表的“LatestInvoiceDate”上。

将这些全部连接在一起后，从学生表和第二实例 (TI2) 发票表中获取发票详细信息的所有字段。

现在，如果您只需要至少拥有 1 张发票的学生，只需保留“JOIN”而不是“LEFT JOIN”即可。