ＳＱＬの特色としてデータを塊で扱うことは得意だけど、データを一件一件精査して扱うような操作は不得意ということがあります。
一方で普通の言語であれば、ループさせないとできないような処理をループを使わずに、１行の命令で書くことができます。
たとえば、先月のすべての販売価格を修正するような処理は、下のような命令で済みます。
UPDATE `trn_sales` SET `Sales` = `Price` * `Count`
WHERE `SaleDate` BETWEEN '2010/10/01 00:00:00' AND '2010/10/31 23:59:59'
以下のような販売データがあったとします。
| BillNo | SaleDate | GoodsCode | Customer | Count | Price | Sales |
|---|---|---|---|---|---|---|
| 303 | 2010-10-01 10:01:15 | A10101 | U001 | 3 | 3000 | 9000 |
| 305 | 2010-10-0412:10:14 | A10121 | U001 | 3 | 1168 | 3504 |
| 306 | 2010-10-0513:24:34 | A10102 | U003 | 2 | 6250 | 12500 |
| 307 | 2010-10-0516:34:52 | A10104 | U002 | 10 | 298 | 2980 |
| //// | ||||||
さてこのデータから、一ヶ月分の各日付ごとの売上金額のデータが必要だとします。このようなＳＱＬ文で簡単に日付単位のデータを取得することができます。
SELECT DATE(`SaleDate`) as `Date`, Sum(`Sales`) as `Sales`
FROM `trn_sales`
WHERE `SaleDate` BETWEEN '2010/10/01 00:00:00' AND '2010/10/31 23:59:59'
GROUP BY DATE(`SaleDate`)
| Date | Sales |
|---|---|
| 2010-10-01 | 9000 |
| 2010-10-02 | 1480 |
| 2010-10-04 | 3504 |
| 2010-10-05 | 15480 |
| // | |
しかし、ちょっと見ると2010-10-03のデータがありません。やはり、こういうときこういうイメージのデータになってほしいのではないでしょうか？
| Date | Sales |
|---|---|
| 2010-10-01 | 9000 |
| 2010-10-02 | 1480 |
| 2010-10-03 | 0 |
| 2010-10-04 | 3504 |
| 2010-10-05 | 15480 |
| // | |
こうするには、ちょっと工夫が必要です。
まず、０～９までのデータのテーブルを用意します。一ヶ月ですので、1から31のテーブルを作成しても良いのですが、それでは汎用性がありません。
テーブル/mst_digit
| Dighit |
|---|
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
このままでは、０～９をあらわすだけなので、ちょっとビューで作成します。
CREATE VIEW `vw_sequence99` AS
SELECT (`d1`.`digit` + (`d2`.`digit` * 10)) AS `Number`
FROM (`mst_digit` `d1` join `mst_digit` `d2`);
これで、0～99までのビューができます。
| Number |
|---|
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| // |
| 99 |
これを元に、日付データを作成します。
SELECT ADDDATE(‘2010-10-01’, V.Number) as Date
FROM vw_sequence99 as V
WHERE ADDDATE(‘2010-10-01’, V.Number) BETWEEN ‘2010-10-01’ AND ‘2010-10-31’
| Date |
|---|
| 2010-10-01 |
| 2010-10-02 |
| 2010-10-03 |
| 2010-10-04 |
| 2010-10-05 |
| 2010-10-06 |
| // |
| 2010-10-31 |
この日付と、先ほどの売上データのデータを組み合わせることによって、売上のない日も含めてデータを作成することができました。
SELECT ADDDATE('2010-10-01', V.Number) as Date,IFNULL(Sum(S.Sales),0) as Sales
FROM vw_sequence99 as V LEFT JOIN trn_sales as S
ON ADDDATE('2010-10-01', V.Number) = DATE(S.`SaleDate`)
WHERE ADDDATE('2010-10-01', V.Number) BETWEEN '2010-10-01' AND '2010-10-31'
GROUP BY ADDDATE('2010-10-01', V.Number);
| Date | Sales |
|---|---|
| 2010-10-01 | 9000 |
| 2010-10-02 | 1480 |
| 2010-10-03 | 0 |
| 2010-10-04 | 3504 |
| 2010-10-05 | 15480 |
| // | |
| 2010-10-31 | 0 |
同じように0から99を作成するだけでなく、0～999や0～9999も作成できます。
CREATE VIEW vw_sequence999 AS
SELECT (`d1`.`digit` + (`d2`.`digit` * 10)) + (`d3`.`digit` * 100) AS `Number`
FROM `mst_digit` AS `d1` JOIN `mst_digit` AS `d2` JOIN `mst_digit` AS `d3`;
この技法は連続したシーケンス番号を作り出すのが苦手なSQLでは結構使われます。
