System iを愛するすべての人のためのコミュニティサイト

今までこの "素直なSQLの書き方" シリーズでは SQL を RPG を引き合いに出しながら説明してきましたが、RPG経験者の方には、「SQL というものは

- FROM 句は F 仕様書（なので、ここから書き始めるわけですね）
- WHERE 句が C 仕様書、GROUP BY 句 が RPG サイクルの制御レベル

といったようにとらえてみれば、そんなに難しいものではない」ということをご理解いただけたのではないでしょうか?!

RPG とか関係ない方でも、SQL の書き方の順番を

- FROM → WHERE → GROUP BY → HAVING → SELECT

と考えておくのは、実際に書きやすい順番なので、ぜひ頭の片隅にとめておいていただけるといいと思います。

表の集まりから、条件の指定や、集計をしていって、最後に出てくるはずの行から、必要なカラムのみを選択して表示する、というイメージですね。

実際、HAVING に指定できるのは GROUP BY で使用したカラムですし、さらに GROUP BY または HAVING で指定したカラムしか SELECT には指定できない、なんてことがあるわけです。
↑ の順番であれば自然にそう書けるんですね。
（ちなみに … ORDER BY は取得する行の内容には関わりません。みばえだけの問題といっていいので、この順番のさらに最後、になります）

今回はその最後の SELECT 句のおはなしをしようと思ったのですが、「最後に出てくるはずの行から、必要なカラムのみを選択して表示する」ということ以外に特に気をつけるようなことはないんですね …

そこで、SELECT 句で一番威力を発揮しやすい、CASE 式のおはなしをしたいと思います。

まず、最初に RPG 経験者の方には、SELECT 句というのは O 仕様書のようなものだとお考えいただければいいのかなと思います。

O 仕様書で印刷したいフィールドの選択や順番（位置）を決めますよね?

さらに O 仕様書では、固定情報を書いたり、そのフィールドを EDTCODE などで変更したりすることもできますね。

SELECT 句でもやはり、取得したいカラムを指定したり、取得順を決めたり、固定情報を指定したりできます。

EDTCODE などはどちらかというと、"見た目"を整えるためのようなところがありますが、これをもっと自由にしたのが SELECT 句で使用する CASE 式になります。

CASE 式はカラムが指定できるところにはみんな使えるので、GROUP BY などで使用することもあるのですが、まずは SELECT での使い方をマスターしてから、の方がわかりやすいと思います。

ちなみに、今回もサンプルのデータベースは CREATE_SQL_SAMPLE ストアド・プロシージャで作成したものを使用していますし、例に出てくるすべての SQL は V5R4/V6R1 の DB2 for IBM i で実行できることを確認していますので、ぜひ実際に実行して結果を確かめながら読んでみてくださいね。

CASE 式自体はいたって単純なものです。↓ のかたちでまず理解してください。

CASE WHEN [カラム] = [条件]　THEN

THEN の後は、固定情報とか、カラムと何かの計算結果とか、別のカラムとかいったものがよく指定されます。

たとえば、↓ のように単純な置き換えに使用できます。

SELECT empno, 
       firstnme, 
       lastname, 
       CASE WHEN sex = 'F' THEN ' 女性 ' 
            WHEN sex = 'M' THEN ' 男性 ' 
            ELSE NULL END 
  FROM Sample/Employee  

'F' や 'M' でもわかるでしょうが、もっとわかりやすくするためにそれぞれを '女性' '男性' と表示を入れ替えています。

また、条件に応じて計算を行うこともできます。
これはワークフィールド的に新規のカラムを作るようなイメージですね。同じ行の他のカラムの値に応じて、あるカラムの値を加工した結果を表示させています。

SELECT empno,                                               
       lastname,                                           
       edlevel,                                             
       salary,                                             
       CASE WHEN edlevel < 15 THEN (salary * 0.8)           
            WHEN edlevel >= 15 THEN (salary * 1.2)     
            ELSE salary END  AS NEW_salary 
  FROM Sample/Employee

集計と組み合わせて、男女それぞれの集計結果を同時に得ることもできます。

SELECT workdept, 
       SUM(CASE WHEN sex = 'F' THEN salary ELSE NULL END) AS F, 
       SUM(CASE WHEN sex = 'M' THEN salary ELSE NULL END) AS M 
  FROM Sample.Employee 
 GROUP BY workdept        

こんなふうにすると部門での総合計と性別ごとの合計を内訳的に出すこともできます。

SELECT workdept,                                                 
       SUM(CASE WHEN sex = 'F' THEN salary ELSE NULL END) AS F, 
       SUM(CASE WHEN sex = 'M' THEN salary ELSE NULL END) AS M, 
       SUM(salary)                                               
  FROM Sample.Employee                                     
 GROUP BY workdept          

こんなふうにすると最後に総合計をつけたりすることもできます。

SELECT workdept,                                                 
       SUM(CASE WHEN sex = 'F' THEN salary ELSE NULL END) AS F, 
       SUM(CASE WHEN sex = 'M' THEN salary ELSE NULL END) AS M, 
       SUM(salary)                                               
  FROM Sample.Employee                                     
 GROUP BY workdept           
UNION 
SELECT 'TOTAL',                                                 
       SUM(CASE WHEN sex = 'F' THEN salary ELSE NULL END) AS F, 
       SUM(CASE WHEN sex = 'M' THEN salary ELSE NULL END) AS M, 
       SUM(salary)                                               
  FROM Sample.Employee                                    

さらに、こんなふうに、明細と小計と合計というように出すことも可能です。

SELECT workdept,                                               
       empno,                                                 
       (CASE WHEN sex = 'F' THEN salary ELSE NULL END) AS F,   
       (CASE WHEN sex = 'M' THEN salary ELSE NULL END) AS M,   
       CAST(NULL AS DEC)                                       
  FROM Sample/Employee                                   
UNION                                                   
SELECT workdept,                                               
       'SUBTOTAL',                                             
       SUM(CASE WHEN sex = 'F' THEN salary ELSE NULL END) AS F ,
       SUM(CASE WHEN sex = 'M' THEN salary ELSE NULL END) AS M , 
       SUM(salary)                                             
  FROM Sample/Employee                                   
 GROUP BY workdept                                       
UNION                                                   
SELECT 'TOTAL',                                                 
       '',                                                       
       SUM(CASE WHEN sex = 'F' THEN salary ELSE NULL END) AS F, 
       SUM(CASE WHEN sex = 'M' THEN salary ELSE NULL END) AS M, 
       SUM(salary)                                               
  FROM Sample/Employee                                     
 ORDER BY 1                                              

もちろん、集計と組み合わせるだけでなく、結合と組み合わせることもできますね。
あんまりフクザツなものに結合すると読みにくくなりそうなので、
ごく簡単なものに以前ご紹介したような名前を取ってくるための結合をつけてみましょう。

SELECT E.workdept, D.deptname, 
       SUM(CASE WHEN E.sex = 'F' THEN E.salary ELSE NULL END) AS F, 
       SUM(CASE WHEN E.sex = 'M' THEN E.salary ELSE NULL END) AS M 
  FROM (Sample.Employee E JOIN Sample.Department D ON E.workdept = D.deptno) 
 GROUP BY E.workdept, D.deptname        

合計ばっかり例に取りましたが、もちろん他の集計関数も使えます。

SELECT E.workdept, D.deptname, 
       AVG(CASE WHEN E.sex = 'F' THEN E.salary ELSE NULL END) AS F, 
       AVG(CASE WHEN E.sex = 'M' THEN E.salary ELSE NULL END) AS M 
  FROM (Sample.Employee E JOIN Sample.Department D ON E.workdept = D.deptno) 
 GROUP BY E.workdept, D.deptname

こんなふうに SQL というのはかなりいろんなことができます。

RPG や Query の経験者であれば、全然抵抗なく理解できる言語です。
ぜひ使ってみてその力を確かめてみてください!!