SQL4 択難易度 標準

2 人以上の部署に絞る HAVING COUNT(*) >= 2 を足したい。正しく直したものは？

SELECT dept, COUNT(*) AS nFROM empWHERE active = 1GROUP BY deptORDER BY n DESC;

正答

1. SELECT dept, COUNT(*) AS n FROM emp WHERE active = 1 HAVING COUNT(*) >= 2 GROUP BY dept ORDER BY n DESC;
2. SELECT dept, COUNT(*) AS n FROM emp WHERE active = 1 GROUP BY dept ORDER BY n DESC HAVING COUNT(*) >= 2;
3. SELECT dept, COUNT(*) AS n HAVING COUNT(*) >= 2 FROM emp WHERE active = 1 GROUP BY dept ORDER BY n DESC;
4. SELECT dept, COUNT(*) AS n FROM emp WHERE active = 1 GROUP BY dept HAVING COUNT(*) >= 2 ORDER BY n DESC;

解説

HAVING はグループ化した結果を絞る句なので GROUP BY の後ろに書き、並べ替えの ORDER BY より前に置く。WHERE の直後に置くと GROUP BY より先になり構文エラー。ORDER BY の後ろも順序違反で通らず、SELECT 句の直後は FROM より前なので論外である。WHERE active = 1 は行単位の条件、HAVING COUNT(*) >= 2 はグループ単位の条件と役割が分かれている。

参考: www.sqlite.org

havingclause-order