beak我想計算每個類別的羽毛出現在資料集中的次數，然后只過濾那些在列有 category時被計算超過 5 次的羽毛long。
但是，我收到以下錯誤：
“（”附近：語法錯誤
SELECT
land_birds.feather, land_birds.weight, COUNT(DISTINCT land_birds.feather) AS numFeathers,
land_birds.size, sea_birds.beak
FROM
land_birds
INNER JOIN
sea_birds
ON
land_birds.colour = sea_birds.colour
WHERE sea_birds.colour IN (SELECT colour from land_birds) AND beak LIKE 'Long'
GROUP BY feather
ORDER BY feather ASC
FILTER(WHERE numFeathers > 5)
uj5u.com熱心網友回復：
要過濾通過分組生成的資訊，請使用HAVING緊跟在子句之后的GROUP BY子句，如下所示：
SELECT
land_birds.feather
, land_birds.weight
, COUNT(DISTINCT land_birds.feather) AS numFeathers
, land_birds.size
, sea_birds.beak
FROM land_birds
INNER JOIN sea_birds ON land_birds.colour = sea_birds.colour
WHERE beak LIKE 'Long'
GROUP BY land_birds.feather
HAVING COUNT(DISTINCT land_birds.feather) > 5
ORDER BY land_birds.feather ASC
雖然在 having 子句中使用您給出該計算的“numFeathers”別名似乎是合乎邏輯的，但不要. 而是參考計算本身。記住這一點可能會有所幫助，您可以在 have 子句中參考未出現在 select 子句中的分組計算，例如這仍然有效
SELECT
land_birds.feather
, land_birds.weight
, land_birds.size
, sea_birds.beak
FROM land_birds
INNER JOIN sea_birds ON land_birds.colour = sea_birds.colour
WHERE beak LIKE 'Long'
GROUP BY land_birds.feather
HAVING COUNT(DISTINCT land_birds.feather) > 5
ORDER BY land_birds.feather ASC
在這里，該計算根本沒有列別名。
關于您的查詢的其他意見。
- 在整個查詢中參考列時始終使用表名（或表別名）
- 關于顏色的內部連接條件意味著結果中只能有與該條件完全匹配的行。因此，不需要在 where 子句中也包含相同的條件。
最后一點，請不要將 having 子句視為 where 子句的替代品。where 子句出現在分組之前，因此它減少了要分組的資料量。having 子句過濾生成的資訊，這些資訊只能在分組后存在。簡而言之，它們是具有特定功能和用途的非常不同的子句。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/434077.html