【達人に学ぶSQL徹底指南書メモ】6. HAVING句の力

SELECT '歯抜けあり' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq)
UNION ALL
SELECT '歯抜けなし' AS gap
FROM SeqTbl
HAVING COUNT(*) = MAX(seq);

SELECT
  CASE
    WHEN COUNT(*) <> MAX(seq) THEN '歯抜けあり'
    ELSE '歯抜けなし'
  END AS gap
FROM
  SeqTbl;

SELECT
  CASE
    WHEN COUNT(*) = 0 OR MIN(seq) > 1 THEN 1
    ELSE (
      SELECT
        MIN(seq + 1)
      FROM
        SeqTbl S1
      WHERE
        NOT EXISTS (
          SELECT *
          FROM SeqTbl S2
          WHERE S2.seq = S1.seq + 1
        )
    )
  END
FROM
  SeqTbl;

SELECT
  income,
  COUNT(*) AS cnt
FROM
  Graduates
GROUP BY
  income
HAVING
  COUNT(*) >= ALL (
    SELECT COUNT(*)
    FROM Graduates
    GROUP BY income
  );

SELECT
  income,
  COUNT(*) AS cnt
FROM
  Graduates
GROUP BY
  income
HAVING
  COUNT(*) >= (
    SELECT MAX(cnt)
    FROM (
      SELECT COUNT(*) AS cnt
      FROM Graduates
      GROUP BY income
    ) TMP
  );

SELECT COUNT(*), COUNT(col_1)
FROM NullTbl;

SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);

SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END);

SELECT dpt
FROM Students
GROUP BY dpt
HAVING
  COUNT(*) = SUM(
    CASE
      WHEN sbmt_date BETWEEN '2018-09-01' AND '2018-09-30' THEN 1
      ELSE 0
    END
  );

SELECT class
FROM TestResults
GROUP BY class
HAVING
  COUNT(*) * 0.75 <= SUM(
    CASE
      WHEN score >= 80 THEN 1
      ELSE 0
    END
  );

SELECT class
FROM TestResults
GROUP BY class
HAVING
  SUM(
    CASE
      WHEN score >= 50 AND sex = '男' THEN 1
      ELSE 0
    END
  ) > SUM(
    CASE
      WHEN score >= 50 AND sex = '女' THEN 1
      ELSE 0
    END
  );

SELECT class
FROM TestResults
GROUP BY class
HAVING
  AVG(
    CASE
      WHEN sex = '男' THEN score
      ELSE NULL
    END
  ) < AVG(
    CASE
      WHEN sex = '女' THEN score
      ELSE NULL
    END
  );

SELECT
  team_id,
  member
FROM
  Teams T1
WHERE
  NOT EXISTS (
    SELECT *
    FROM Teams T2
    WHERE T1.team_id = T2.team_id
      AND status <> '待機'
  );

SELECT
  team_id
FROM
  Teams
GROUP BY
  team_id
HAVING
  COUNT(*) = SUM(
    CASE
      WHEN status = '待機' THEN 1
      ELSE 0
    END
  );

SELECT
  team_id
FROM
  Teams
GROUP BY
  team_id
HAVING
  MAX(status) = '待機'
    AND MIN(status) = '待機';

SELECT
  team_id,
  CASE
    WHEN MAX(status) = '待機'
      AND MIN(status) = '待機' THEN '⚪︎'
    ELSE '×'
  END AS status
FROM
  Teams
GROUP BY
  team_id;

SELECT
  center
FROM
  Materials
GROUP BY
  center
HAVING COUNT(*) <> COUNT(DISTINCT material);

SELECT
  center,
  CASE
    WHEN COUNT(*) <> COUNT(DISTINCT material) THEN 'ダブりあり'
    ELSE 'ダブりなし'
  END AS status
FROM
  Materials
GROUP BY
  center;

SELECT
  center,
  material
FROM
  Materials M1
WHERE
  EXISTS(
    SELECT *
    FROM Materials M2
    WHERE
      M1.center = M2.center
        AND M1.receive_date <> M2.receive_date
        AND M1.material = M2.material
  );

SELECT
  SI.shop
FROM
  ShopItems SI
  INNER JOIN Items I
    ON SI.item = I.item
GROUP BY
  SI.shop
HAVING
  COUNT(SI.item) = (
    SELECT COUNT(item)
    FROM Items
  );

SELECT
  SI.shop
FROM
  ShopItems SI
  LEFT OUTER JOIN Items I
    ON SI.item = I.item
GROUP BY
  SI.shop
HAVING
  COUNT(SI.item) = (SELECT COUNT(item) FROM Items)  -- 条件1
    AND COUNT(I.item) = (SELECT COUNT(item) FROM Items);  -- 条件2

SELECT
  SI.shop,
  COUNT(SI.item) AS my_item_cnt,
  (SELECT COUNT(item) FROM Items) - COUNT(SI.item) AS diff_cnt
FROM
  ShopItems SI
  INNER JOIN Items I
    ON SI.item = I.item
GROUP BY
  SI.shop;