我有一個使用 INNER JOIN 和一個子查詢的作業查詢，但想知道是否有更有效的撰寫方式。
with prl
as
(
SELECT `number`, creator, notes
FROM ratings
INNER JOIN
projects on ratings.project_id = projects.project_id
WHERE ratings.rating = 5 AND projects.active = 1
)
SELECT prl.`number`, creator, notes
FROM prl
INNER JOIN(
SELECT `number`
HAVING COUNT(creator) > 1
)temp ON prl.`number` = temp.`number`
ORDER BY temp.`number`
專案表
project_id| number | creator | active |
| 1 | 3 | bob | 1 |
| 2 | 4 | mary | 1 |
| 3 | 5 | asi | 1 |
評級表
project_id| notes | rating |
| 1 | note1 | 5 |
| 1 | note2 | 5 |
| 3 | note3 | 5 |
| 1 | note4 | 1 |
| 2 | note5 | 5 |
| 3 | note6 | 2 |
結果
| number | creator | notes |
| 3 | bob | note1 |
| 3 | bob | note2 |
uj5u.com熱心網友回復：
看來您正在使用支持視窗功能的 MySQL 版本。如果是這樣，那么試試這個：
SELECT number, creator, notes
FROM
(SELECT p.number, p.creator, r.notes,
COUNT(creator) OVER (PARTITION BY creator) AS cnt
FROM project p
JOIN rating r ON p.project_id=r.project_id
WHERE r.rating=5
AND p.active = 1) v
WHERE cnt=2;
至于這是否更有效，我不確定，因為它取決于您的表索引，但對于一個小資料集，我認為這會做得很好。
演示小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/439344.html