我想查看最近的未來日期的報告日期。我希望它是基于實作 ID 的單個記錄。今天 02-18-2022
SELECT *
FROM document_reports
WHERE reportDate >= now() AND reportDate in
(SELECT MIN(reportDate)
FROM document_reports
WHERE reportDate >= NOW()
group by implementation_id )
group by implementation_id
order by reportDate
您可以查看下面的表格
請幫我？我在哪里犯錯了？
uj5u.com熱心網友回復：
如果它是 MySQL 5.x，那么我會使用相關子查詢，或者在聚合子查詢上加入...
SELECT
*
FROM
document_reports
WHERE
reportDate = (
SELECT MIN(reportDate)
FROM document_reports AS lookup
WHERE lookup.implementation_id = document_reports.implementation_id
AND lookup.reportDate >= NOW()
)
ORDER BY
reportDate
Or...
SELECT
document_reports.*
FROM
(
SELECT implementation_id, MIN(reportDate) AS minReportDate
FROM document_reports
WHERE reportDate >= NOW()
GROUP BY implementation_id
)
AS lookup
INNER JOIN
document_reports
ON document_reports.implementation_id = lookup.implmentation_id
AND document_reports.reportDate = lookup.minReportDate
ORDER BY
document_reports.reportDate
如果是 MySQL 8，那么我會使用ROW_NUMBER()
WITH
sorted AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY implementation_id
ORDER BY reportDate
)
AS rn
FROM
document_reports
WHERE
reportDate >= NOW()
)
SELECT
*
FROM
sorted
WHERE
rn = 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/427347.html