wordpressなどで金額をvarcharのカラムに20,000円という形で入れていた。
普通にmin()やmax()を使っていたら全く比較できていなかったのでまとめました。
データ
| id | fee |
|---|---|
| 1 | 10,000円 |
| 2 | 20,000円 |
| 3 | 30,000円 |
| 4 | 5,000円 |
ダメな例
sql
SELECT
min(fee), // => 10,000円
max(fee) // => 5,000円
FROM posts
単純に文字列として比較されている為、 正常に集計できていなかった。
良い例
sql
SELECT
min(abs(REPLACE(fee, ",", ""))) AS min_fee, // => 5000
max(abs(REPLACE(fee, ",", ""))) AS max_fee // => 30000
FROM posts
置換でカンマを消してから abs()で絶対値を取得して、 min() max()をかけたらうまくいきました。
※カンマを消さないでabs()をかけるとカンマの前までしか絶対値を取得できないです。
また、データをもとの形(10,000円)で取り出したい時はこんな感じで書きました。
sql
SELECT
concat(format(min(abs(REPLACE(fee, ",", ""))), 0), "円") AS min_fee, // => 5,000円
concat(format(max(abs(REPLACE(fee, ",", ""))), 0), "円") AS max_fee // => 30,000円
FROM posts
format()で3桁ごとに小数点を加えて、 concat()で円を結合させて表示しました。
まとめ
基本的には数値は数値型のカラムに入れるべき。
文字列型に入れてしまった時は、数値に直して比較する。