DB2 GROUP BY 拡張の変更点

• 追加された行はこのように表示されます。
• 削除された行はこのように表示されます。

!!!DB2 GROUP BY 拡張
[DB2]{{category SQL}}

!!ROLLUP
*グループごとのサマリーを作成
 SELECT t.name AS tour_name,
        c.name AS county_name,
        COUNT(*) AS falls_count
 FROM upfall u INNER JOIN trip t
       ON u.id = t.stop
    INNER JOIN county c
       ON u.county_id = c.id
 GROUP BY ROLLUP(t.name, c.name);

 TOUR_NAME  COUNTY_NAME FALLS_COUNT
 ---------- ----------- ----------------------
 M-28       Alger       3
 M-28       Baraga      1
 M-28       Ontonogan   2
 M-28                   6
 US-2       Delta       1
 US-2       Gogebic     1
 US-2       Dickenson   1
 US-2       Ontonogan   1
 US-2                   4
 Munising   Alger       6
 Munising               6

!!CUBE
*指定列のすべての可能な組み合わせサマリ
 SELECT t.name AS tour_name,
        c.name county_name,
        COUNT(*) AS falls_count
 FROM upfall u INNER JOIN trip t
       ON u.id = t.stop
    INNER JOIN county c
       ON u.county_id = c.id
 WHERE t.name = 'Munising'
 GROUP BY CUBE(t.name, c.name);

 TOUR_NAME  COUNTY_NAME FALLS_COUNT
 ---------- ----------- ----------------------
                        6
            Alger       6
 Munising               6
 Munising   Alger       6

!!GROUP BY で有用な関数
!GROUPING( column )
*CUBE, ROLLUPの結果が NULL の場合、1を返す、そうでない場合、0を返す
!GROUPING_ID( column , column , . . . )
!GROUP_ID( )