SQL 練習 ドリル 2 ～高度で複雑な SQL に挑戦｜研修コースに参加してみた

SELECT publish, AVG(price)
FROM books 
GROUP BY publish;

解説
• GRUOP BY の基本どおり
• グループ化する列（ publish ）と集計列（ price ）をはっきりさせる

SELECT p.p_name, SUM(o.quantity), SUM(p.price * o.quantity)
FROM order_desc AS o RIGHT JOIN product AS p
ON p.p_id = o.p_id
GROUP BY p.p_id, p.p_name ORDER BY SUM(p.price * o.quantity) DESC;

解説
• 「まったく売れていない商品」についても取り出すには、外部結合を利用する
  • ここでは RIGHT JOIN （右外部結合）を使っている
  • 全件表示したいテーブルがどちらなのか というと product テーブル
  • ちなみに、左外部結合でも書ける
• 累計購入額も求める
  • GROUP BY して集計関数
  • SUM(p.price * o.quantity)
• GROUP BY では別名を使っているが、 ORDER BY には別名を使っていないので、 SELECT の列名に同じものを書いている

SELECT CONCAT('ISBN', isbn) AS ISBNコード, title AS 書名,
  DATE_FORMAT(publish_date, '%Y年%m月%d日') AS 刊行日
FROM books;

解説
• 日時の整形
• 取り出した文字列で足りなくて足すことはよくある
• 文字列を足すのは CONCAT
• 日付の書式整形は DATE_FORMAT 関数
  • %は%Y%m%dで年月日の埋め込みを表す
• データベース製品ごとに、関数や使用方法が異なる場合があるので注意

SELECT referer, count(*) AS カウント数,
CASE
  WHEN count(*) >= 50 THEN 'A'
  WHEN count(*) >= 10 THEN 'B'
  ELSE 'C'
END AS ランク
FROM access_log
GROUP BY referer;

解説
• CASE 演算子で条件分岐する
  • WHEN で各条件と対応する値を記述し、その他の条件の値は ELSE に記述する
• CASE 全体に AS で名前をつけることもできる

SELECT answer1, answer2
FROM quest
WHERE age >
  (SELECT AVG(age) FROM quest)
ORDER BY answer1 ASC;

解説
• 「回答者の平均年齢を超えている人の回答」を副問合せにする
• age と回答者の平均年齢（副問合せ）を比較
• >で比較するので、副問合せは単一の値だけが返る SQL 文を記述する
• 副問合せを思いつくのは難しいかもしれない
  • どれだけ引き出しを持っているかが、すばやく SQL を書けることにつながる

SELECT cc.c_title, cn.c_title
FROM contents AS cc
  INNER JOIN contents AS cn
  ON cc.next_id = cn.c_id
ORDER BY cc.c_id ASC;

解説
• 自己結合
  • ふだんあまり使わないテクニックだが、たまにある
  • 自分の分身と結合するようなもの
  • ただし書き方は通常の結合と同じ
  • どちらも同じテーブルなので、別名をつけないといけない

SELECT b.title, a.name, b.publish_date
FROM
  ( books AS b
  INNER JOIN author_books AS ab
  ON b.isbn = ab.isbn )
  INNER JOIN author AS a
  ON ab.author_id = a.author_id
WHERE b.publish = '日経BP'
ORDER BY b.publish_date DESC;

解説
• 3 つ以上のテーブルの結合
  • 3 つ以上もできるということは知っていても、それをスムーズに書けるかどうかはまた別
• JOIN の入れ子
  • 丸カッコで 1 つめの結合
  • その結合した結果に対してさらに INNER JOIN
• なお、この例では両方 INNER JOIN だが、 INNER JOIN と LEFT JOIN でもよい

SELECT u.l_name_kana, u.f_name_kana
FROM usr AS u
UNION
  SELECT e.l_name_kana, e.f_name_kana
  FROM employee AS e
  ORDER BY 1, 2;

解説
• UNION や UNION ALL による和集合は、ふだん多用はしないが、たまに出てくる