12,673
回編集
差分
ナビゲーションに移動
検索に移動
ページの作成:「==DB2 GROUP BY 拡張== [DB2]{{category SQL}} ===ROLLUP=== *グループごとのサマリーを作成 SELECT t.name AS tour_name, c.name AS county_name,…」
==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を返す
[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を返す
© 2006 矢木浩人
