我需要以下查詢，它從表中回傳 MIN(date)，但是我想在查詢中添加 where 條件以僅選擇 2021-01-01 和 2021-04-01 之間的日期。
SELECT MIN(file.date_received) as date
FROM `file`
JOIN `ref` ON `ref`.`r_id` = `file`.`r_id`
JOIN `screening` ON `screening`.`screen_id` = `ref`.`r_id`
WHERE `ref`.`out` = 4
GROUP BY `file`.`r_id
當我將以下 where 條件添加到查詢時，我收到以下錯誤
錯誤代碼：1111。無效使用組功能
SELECT MIN(file.date_received) as date
FROM `file`
JOIN `ref` ON `ref`.`r_id` = `file`.`r_id`
JOIN `screening` ON `screening`.`screen_id` = `ref`.`r_id`
WHERE `ref`.`out` = 4
AND MIN(file.date_received) > '2021-01-01' AND MIN(file.date_received) < '2021-04-01'
GROUP BY `file`.`r_id
uj5u.com熱心網友回復：
對于匯總結果，您需要 HAVING
SELECT MIN(file.date_received) as date
FROM `file`
JOIN `ref` ON `ref`.`r_id` = `file`.`r_id`
JOIN `screening` ON `screening`.`screen_id` = `ref`.`r_id`
WHERE `ref`.`out` = 4
GROUP BY `file`.`r_id
HAVING MIN(file.date_received) > '2021-01-01' AND MIN(file.date_received) < '2021-04-01
uj5u.com熱心網友回復：
沒有必要使用MINin WHERE-clause，因為您在SELECT-statement 中過濾結果。
要確定您可以使用的日期范圍BETWEEN。
SELECT MIN(file.date_received) as date
FROM `file`
JOIN `ref` ON `ref`.`r_id` = `file`.`r_id`
JOIN `screening` ON `screening`.`screen_id` = `ref`.`r_id`
WHERE `ref`.`out` = 4
AND file.date_received BETWEEN CAST('2021-01-01' AS DATE) AND CAST('2021-04-01' AS DATE)
GROUP BY `file`.`r_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/411780.html
標籤：