[DB2][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
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を返す
YAGI Hiroto (piroto@a-net.email.ne.jp)
twitter http://twitter.com/pppiroto
Copyright© 矢木 浩人 All Rights Reserved.