我有两个查询,我试图连接在一起,所以我不必一个接一个地执行,因为在某些情况下,我想改变输出顺序。
第一个查询获取发票状态。它更改为保留日志,但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,因此不需要invoice_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。
这是两个查询的组合版本。我创建了一个子查询来选择状态与输入状态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提琴的演示。
引用:
MySQL: The Rows Holding Group-wise Maximum of a Certain Column