php - MySQL 获取依赖最新插入的最新日期

Question

我有两个查询想要连接在一起，因此我不必一个接一个地执行，因为在某些情况下我想更改输出顺序。

第一个查询获取发票状态。它会更改以保留日志，但 99% 的情况下，我只需要检查最新条目。布局如下:

providers_invoice_status_ID, providers_invoice_summary_ID, status_ID, userinfo_ID, providers_invoice_status_date

SELECT providers_invoice_summary_ID
  FROM db_providers_invoice_status
 WHERE status_ID = '$status'
 ORDER BY providers_invoice_status_date DESC

然后根据上述查询的结果查询发票表。其布局如下。

providers_invoice_summary_ID, providers_invoice_summary_file, providers_invoice_summary_total, providers_invoice_summary_due, providers_invoice_summary_generated

    SELECT si.providers_invoice_summary_ID
     , providers_invoice_summary_file
     , providers_invoice_summary_total
     , providers_invoice_summary_due
     , providers_invoice_summary_generated
  FROM db_providers_invoice_summary si 
 WHERE providers_invoice_summary_ID = '$invoice_ID';

可能会尝试合并查询，结果如下:

SELECT si.providers_invoice_summary_ID
     , providers_invoice_summary_file
     , providers_invoice_summary_total
     , providers_invoice_summary_due
     , providers_invoice_summary_generated
     , s.status_ID 
  FROM db_providers_invoice_summary si, db_providers_invoice_status s
 WHERE status_ID = ( SELECT status_ID
                   FROM db_providers_invoice_status
                  WHERE providers_invoice_summary_ID = si.providers_invoice_summary_ID 
                    AND status_ID = 7  
                  ORDER 
                     BY providers_invoice_status_date DESC 
                  LIMIT 1)

但是，它从状态记录表中提取所有结果，其中 status_ID = 7，因此无法完全获取最新插入(状态可能已更改为 8，因此不需要该发票_ID)。我尝试过使用 ORDER BY，但它只是对所有结果进行排序，而不是我想要的子集。

对此的任何帮助将不胜感激。希望阅读它的人能够清楚地了解这一点，我知道它相当复杂。

编辑数据:

发票表:

providers_invoice_summary_ID providers_invoice_summary_file providers_invoice_summary_total providers_invoice_summary_due providers_invoice_summary_generated
----------------------------------------------------------------------------------------------------------------------------------------------------------
           4                |        ../blah/blah.jpg      |            245.63       |           2014-04-20               |           2014-03-14
           5                |        ../blah/blah.jpg      |            456.89       |           2014-04-20               |           2014-03-12
           6                |        ../blah/blah.jpg      |            125.36       |           2014-04-24               |           2014-03-12    

状态表:

providers_invoice_status_ID providers_invoice_summary_ID status_ID userinfo_ID providers_invoice_Status_date
------------------------------------------------------------------------------------------------------------
               4           |              4             |   7     |      7    |   2014-03-14 10:19:41
               5           |              5             |   7     |      7    |   2014-03-12 10:22:41
               6           |              6             |   7     |      7    |   2014-03-24 10:15:38
               7           |              5             |   8     |      7    |   2014-03-26 11:15:14

我希望从此数据中获得 2 张发票，因为两张发票当前的最新状态设置为 7。希望这能进一步澄清问题。

Answer 1

这是两个查询的组合版本。我创建了一个子查询来选择状态与输入状态 ID(例如 7)匹配的发票。状态 ID 又是使用最新状态日期从子查询中获取的。

更新的查询:

SELECT 
 si.providers_invoice_summary_ID
 , si.providers_invoice_summary_file
 , si.providers_invoice_summary_total
 , si.providers_invoice_summary_due
 , si.providers_invoice_summary_generated
 , invoice_status.status_ID 
FROM 
  db_providers_invoice_summary si, 
  (SELECT t1.providers_invoice_summary_ID as providers_invoice_summary_ID
      , t1.status_id as status_id
   FROM db_providers_invoice_status t1
  LEFT JOIN db_providers_invoice_status t2 ON t1.providers_invoice_summary_ID = t2.providers_invoice_summary_ID AND t1.providers_invoice_Status_date < t2.providers_invoice_Status_date
WHERE t2.providers_invoice_summary_ID IS NULL
  AND t1.status_id = 7
   ) invoice_status
WHERE SI.PROVIDERS_INVOICE_SUMMARY_ID = invoice_status.PROVIDERS_INVOICE_SUMMARY_ID

请查看sql fiddle也可以进行演示。

引用文献:

MySQL: The Rows Holding the Group-wise Maximum of a Certain Column