SQL入門：テーブル操作

テーブルのコピー

CREATE TABLE meats_tmp SELECT * FROM meats;

NULL値を平均値に置き換える

SELECT
  *,
  COALESCE(
    price,
    (SELECT SUM(price)/COUNT(*) FROM meats_tmp)
  ) AS price
FROM meats_tmp;

price=0ならNULLを返す

SELECT NULLIF(price, 0) FROM meats_tmp;

行の最小値

SELECT LEAST(col1, col2, col3);

行の最大値

SELECT GREATEST(col1, col2, col3);

整数へ型変換

SELECT
  *,
  COALESCE(
    price,
    CAST(
      (SELECT SUM(price)/COUNT(*) FROM meats_tmp) AS UNSIGNED
    )
  ) AS price
FROM meats_tmp;