茨の道も一歩から

40代後半の田舎住まい無職。再就職先が決まるまでの茨の道を記録します。

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;