{"id":493,"date":"2014-12-03T18:34:50","date_gmt":"2014-12-03T09:34:50","guid":{"rendered":"http:\/\/www.dbsheetclient.jp\/blog\/?p=493"},"modified":"2018-06-27T16:22:52","modified_gmt":"2018-06-27T07:22:52","slug":"%e9%9b%86%e8%a8%88%e9%96%a2%e6%95%b0%ef%bc%88%e5%b0%8f%e8%a8%88%e3%80%81%e5%90%88%e8%a8%88%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.dbsheetclient.jp\/blog\/?p=493","title":{"rendered":"\u96c6\u8a08\u95a2\u6570\uff08\u5c0f\u8a08\u3001\u5408\u8a08\uff09"},"content":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002<\/p>\n

\"1389614692322\"<\/a><\/p>\n

\u30b7\u30e7\u30c3\u30d4\u30f3\u30b0\u30bb\u30f3\u30bf\u30fc\u306e\u5916\u306e\u5e83\u5834\u304c\u98fe\u308a\u3064\u3051\u3089\u308c\u3066\u3044\u308b\u306e\u3092\u3001\u5a18\u304c\u4ee5\u524d\u5915\u65b9\u306b\u64ae\u3063\u305f\u5199\u771f\u3067\u3059\u3002\u3053\u3046\u3044\u3046\u98fe\u308a\u304c\u3042\u308b\u3060\u3051\u3067\u96f0\u56f2\u6c17\u306f\u5909\u308f\u308a\u307e\u3059\u3088\u306d\u3002\u3053\u308c\u304b\u3089\u3001\u30af\u30ea\u30b9\u30de\u30b9\u306b\u5411\u3051\u3066\u3082\u3063\u3068\u3044\u308d\u3044\u308d\u306a\u5834\u6240\u3067\u304d\u308c\u3044\u306a\u98fe\u308a\u304c\u898b\u308c\u308b\u3088\u3046\u306b\u306a\u308b\u306e\u3067\u306f\u306a\u3044\u304b\u3068\u697d\u3057\u307f\u3067\u3059\u3002

\uff1c\u672c\u65e5\u306e\u984c\u6750\uff1e
\u96c6\u8a08\u95a2\u6570\uff08\u5c0f\u8a08\u3001\u5408\u8a08\uff09<\/strong><\/p>\n

\u30b7\u30b9\u30c6\u30e0\u306e\u5e33\u7968\u3092\u51fa\u529b\u3059\u308b\u5834\u5408\u306b\u3001\u3088\u304f\u5c0f\u8a08\u3084\u5408\u8a08\u3092\u51fa\u529b\u3059\u308b\u30b1\u30fc\u30b9\u304c\u3042\u308a\u307e\u3059\u3088\u306d\u3002\u3053\u308c\u3092SQL\u5074\u3067\u884c\u3044\u305f\u3044\u3068\u3044\u3046\u3053\u3068\u3082\u591a\u3005\u3042\u308b\u304b\u3068\u601d\u3044\u307e\u3059\u3002\u4eca\u56de\u306f\u3001GROUP BY\u53e5\u3067\u96c6\u8a08\u3092\u884c\u3046\u969b\u306b\u3001\u5c0f\u8a08\u3084\u5408\u8a08\u3092\u51fa\u3059\u65b9\u6cd5\u306b\u3064\u3044\u3066\u3001\u53d6\u308a\u4e0a\u3052\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

\u5546\u54c1\u30de\u30b9\u30bf\u306e\u30c7\u30fc\u30bf\u3068\u3001\u58f2\u4e0a\u30c6\u30fc\u30d6\u30eb\u306e\u30c7\u30fc\u30bf\u304c\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u5834\u5408\u306e\u3001\u53d7\u6ce8\u65e5\u3054\u3068\u3001\u53ca\u3073\u5546\u54c1\u306e\u5206\u985e\u3054\u3068\u306e\u96c6\u8a08\u3092\u51fa\u3059\u5834\u5408\u3092\u8003\u3048\u3066\u307f\u307e\u3059\u3002<\/p>\n

SELECT * FROM \u5546\u54c1\u30de\u30b9\u30bf
ORDER BY \u5546\u54c1CD;
\"\u5546\u54c1\u30de\u30b9\u30bf\u30c7\u30fc\u30bf\"<\/a>\u00a0<\/p>\n

SELECT * FROM \u58f2\u4e0a\u30c6\u30fc\u30d6\u30eb
ORDER BY \u53d7\u6ce8\u756a\u53f7;
\"\u58f2\u4e0a\u30c6\u30fc\u30d6\u30eb\u30c7\u30fc\u30bf\"<\/p>\n

<\/a>\u53d7\u6ce8\u65e5\u3054\u3068\u3001\u53ca\u3073\u5546\u54c1\u306e\u5206\u985e\u3054\u3068\u306e\u96c6\u8a08\u3092\u62bd\u51fa\u3057\u307e\u3059\u3002\uff08Oracle\u306e\u5834\u5408\uff09<\/p>\n

SELECT A.\u53d7\u6ce8\u65e5, B.\u5206\u985e
, SUM(A.\u5546\u54c1\u6570\u91cf) AS \u6570\u91cf, SUM(A.\u58f2\u4e0a\u91d1\u984d) \u58f2\u4e0a\u91d1\u984d
\u00a0 FROM \u58f2\u4e0a\u30c6\u30fc\u30d6\u30eb A
\u00a0 JOIN \u5546\u54c1\u30de\u30b9\u30bf B ON A.\u5546\u54c1CD = B.\u5546\u54c1CD
\u00a0GROUP BY A.\u53d7\u6ce8\u65e5, B.\u5206\u985e
\u00a0ORDER BY A.\u53d7\u6ce8\u65e5, B.\u5206\u985e;<\/p>\n

\"ora_groupby\"<\/a><\/p>\n

\u6b21\u306b\u3001\u53d7\u6ce8\u65e5\u3054\u3068\u3001\u53ca\u3073\u5546\u54c1\u306e\u5206\u985e\u3054\u3068\u306e\u96c6\u8a08\u306b\u3001\u5c0f\u8a08\u3001\u5408\u8a08\u3092\u51fa\u3059\u3088\u3046\u306b\u3001ROLLUP\u53e5\u3092\u4f7f\u7528\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

SELECT A.\u53d7\u6ce8\u65e5, B.\u5206\u985e
, SUM(A.\u5546\u54c1\u6570\u91cf) AS \u6570\u91cf, SUM(A.\u58f2\u4e0a\u91d1\u984d) \u58f2\u4e0a\u91d1\u984d
\u00a0 FROM \u58f2\u4e0a\u30c6\u30fc\u30d6\u30eb A
\u00a0 JOIN \u5546\u54c1\u30de\u30b9\u30bf B ON A.\u5546\u54c1CD = B.\u5546\u54c1CD
\u00a0GROUP BY ROLLUP(A.\u53d7\u6ce8\u65e5, B.\u5206\u985e)
\u00a0ORDER BY A.\u53d7\u6ce8\u65e5, B.\u5206\u985e;
\"ora_groupby_rollup\"
<\/a>
\u3053\u3053\u3067\u3001\u5c0f\u8a08\u3001\u5408\u8a08\u306e\u3068\u304d\u306b NULL \u306e\u8868\u793a\u306b\u306a\u308b\u306e\u304c\u5acc\u306a\u5834\u5408\u306b\u306f\u3001\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u304b\u305f\u3061\u3067\u8868\u73fe\u3092\u5909\u66f4\u3059\u308b\u3053\u3068\u3082\u3067\u304d\u307e\u3059\u3002<\/p>\n

SELECT
\u00a0 DECODE(GROUPING(A.\u53d7\u6ce8\u65e5), 1, ‘\u7dcf\u5408\u8a08’, A.\u53d7\u6ce8\u65e5) AS \u53d7\u6ce8\u65e5
, DECODE(GROUPING(A.\u53d7\u6ce8\u65e5), 1, ‘ ‘, DECODE(GROUPING(B.\u5206\u985e), 1, ‘1\u65e5\u5206\u5c0f\u8a08’, B.\u5206\u985e)) AS \u5206\u985e
, SUM(A.\u5546\u54c1\u6570\u91cf) AS \u6570\u91cf
, SUM(A.\u58f2\u4e0a\u91d1\u984d) AS \u58f2\u4e0a\u91d1\u984d
\u00a0 FROM \u58f2\u4e0a\u30c6\u30fc\u30d6\u30eb A
\u00a0 JOIN \u5546\u54c1\u30de\u30b9\u30bf B ON A.\u5546\u54c1CD = B.\u5546\u54c1CD
\u00a0GROUP BY ROLLUP(A.\u53d7\u6ce8\u65e5, B.\u5206\u985e)
\u00a0ORDER BY A.\u53d7\u6ce8\u65e5, B.\u5206\u985e;
\"ora_groupby_rollup_grouping\"
<\/a>\u203bGROUPING\u95a2\u6570\u306f\u3001ROLLUP\u3084CUBE\u306e\u64cd\u4f5c\u306e\u5834\u5408\u306b\u3001\u5c0f\u8a08\u3084\u5408\u8a08\u3092\u8868\u3059NULL\u306e\u5834\u5408\u306b\u300c1\u300d\u3092\u623b\u3057\u3001\u305d\u306e\u4ed6\u306f\u300c0\u300d\u3092\u623b\u3059\u306e\u3067\u3001\u305d\u308c\u3092\u4f7f\u3063\u3066\u8868\u793a\u3092\u5909\u66f4\u3057\u3066\u3044\u307e\u3059\u3002\u53d7\u6ce8\u65e5\u306e\u3068\u3053\u308d\u306e\u5408\u8a08\u306e\u3068\u3053\u308d\u306f\u3001NULL\u3067\u306f\u306a\u304f\u300c\u7dcf\u5408\u8a08\u300d\u3001\u5206\u985e\u306e\u3068\u3053\u308d\u306f\u3001NULL\u3067\u306f\u306a\u304f\u300c1\u65e5\u5206\u5c0f\u8a08\u300d\u3068\u3057\u307e\u3057\u305f\u3002\uff08\u7dcf\u5408\u8a08\u306e\u3068\u3053\u308d\u306e\u5206\u985e\u306f\u4eca\u56de\u306f\u7a7a\u767d\u306b\u3057\u307e\u3057\u305f\uff09

<\/a>\u307e\u305f\u3001CUBE\u53e5\u3092\u4f7f\u7528\u3059\u308b\u3068\u3001\u6307\u5b9a\u3055\u308c\u305f\u5217\u306e\u3059\u3079\u3066\u306e\u7d44\u5408\u305b\u306b\u3064\u3044\u3066\u306e\u5c0f\u8a08\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\n

\u5c0f\u8a08\u3084\u5408\u8a08\u306e\u3068\u304d\u306e\u8868\u73fe\u3092\u8003\u616e\u3057\u3066
SELECT
\u00a0 DECODE(GROUPING(A.\u53d7\u6ce8\u65e5), 1, ‘\u7dcf\u5408\u8a08’, A.\u53d7\u6ce8\u65e5) AS \u53d7\u6ce8\u65e5
, CASE WHEN GROUPING(B.\u5206\u985e) = 0 THEN B.\u5206\u985e
\u00a0 \u00a0 \u00a0 \u00a0WHEN GROUPING(A.\u53d7\u6ce8\u65e5) = 1 THEN ‘ ‘
\u00a0 \u00a0 \u00a0 \u00a0WHEN GROUPING(B.\u5206\u985e) = 1 THEN ‘1\u65e5\u5206\u5c0f\u8a08’
\u00a0 \u00a0 \u00a0 \u00a0END AS \u5206\u985e
, SUM(A.\u5546\u54c1\u6570\u91cf) AS \u6570\u91cf
, SUM(A.\u58f2\u4e0a\u91d1\u984d) AS \u58f2\u4e0a\u91d1\u984d
\u00a0 FROM \u58f2\u4e0a\u30c6\u30fc\u30d6\u30eb A
\u00a0 JOIN \u5546\u54c1\u30de\u30b9\u30bf B ON A.\u5546\u54c1CD = B.\u5546\u54c1CD
\u00a0GROUP BY CUBE(A.\u53d7\u6ce8\u65e5, B.\u5206\u985e)
\u00a0ORDER BY A.\u53d7\u6ce8\u65e5, B.\u5206\u985e;
\"ora_groupby_cube\"<\/a>
\u6700\u5f8c\u306e\u7dcf\u5408\u8a08\u306e\u3068\u3053\u308d\u3067\u3001\u5546\u54c1\u306e\u5206\u985e\u3054\u3068\u306e\u30c7\u30fc\u30bf\u304c\u62bd\u51fa\u3055\u308c\u3066\u3044\u308b\u306e\u304c ROLLUP\u53e5\u306e\u5834\u5408\u3068\u7570\u306a\u308a\u307e\u3059\u3002

\u25cfSQL Server<\/strong>\u306e\u5834\u5408
<\/strong>\u901a\u5e38\u306e\u96c6\u8a08\u306b\u3064\u3044\u3066\uff1a<\/p>\n

SELECT A.\u53d7\u6ce8\u65e5, B.\u5206\u985e, SUM(A.\u5546\u54c1\u6570\u91cf)\u6570\u91cf, SUM(A.\u58f2\u4e0a\u91d1\u984d)\u58f2\u4e0a\u91d1\u984d
\u00a0 FROM \u58f2\u4e0a\u30c6\u30fc\u30d6\u30eb A
\u00a0 JOIN \u5546\u54c1\u30de\u30b9\u30bf B ON A.\u5546\u54c1CD = B.\u5546\u54c1CD
\u00a0GROUP BY A.\u53d7\u6ce8\u65e5, B.\u5206\u985e
\u00a0ORDER BY A.\u53d7\u6ce8\u65e5, B.\u5206\u985e;
\"sqlsv_groupby\"<\/a><\/p>\n

<\/a>ROLLUP\u3092\u4f7f\u3063\u305f\u4f8b\u306f\u4ee5\u4e0b\uff08\u3053\u3061\u3089\u3082\u7dcf\u5408\u8a08\u3001\u5c0f\u8a08\u306e\u8868\u73fe\u3092\u8a2d\u5b9a\u3057\u305f\u3082\u306e\uff09\uff1a
SELECT
\u00a0 CASE WHEN GROUPING(A.\u53d7\u6ce8\u65e5) = 1 THEN ‘\u7dcf\u5408\u8a08’
\u00a0 \u00a0 \u00a0 \u00a0ELSE CAST(A.\u53d7\u6ce8\u65e5 AS VARCHAR)
\u00a0 \u00a0 \u00a0 \u00a0END AS \u53d7\u6ce8\u65e5
, CASE WHEN GROUPING(B.\u5206\u985e) = 0 THEN B.\u5206\u985e
\u00a0 \u00a0 \u00a0 \u00a0WHEN GROUPING(A.\u53d7\u6ce8\u65e5) = 1 THEN ‘ ‘
\u00a0 \u00a0 \u00a0 \u00a0WHEN GROUPING(B.\u5206\u985e) = 1 THEN ‘1\u65e5\u5206\u5c0f\u8a08’
\u00a0 \u00a0 \u00a0 \u00a0END AS \u5206\u985e
, SUM(A.\u5546\u54c1\u6570\u91cf)\u6570\u91cf
, SUM(A.\u58f2\u4e0a\u91d1\u984d)\u58f2\u4e0a\u91d1\u984d
\u00a0 FROM \u58f2\u4e0a\u30c6\u30fc\u30d6\u30eb A
\u00a0 JOIN \u5546\u54c1\u30de\u30b9\u30bf B ON A.\u5546\u54c1CD = B.\u5546\u54c1CD
\u00a0GROUP BY A.\u53d7\u6ce8\u65e5, B.\u5206\u985e WITH ROLLUP
\u00a0ORDER BY \u53d7\u6ce8\u65e5, GROUPING(B.\u5206\u985e), B.\u5206\u985e;
\"sqlsv_groupby_rollup2\"<\/a>
<\/a>\u203bROLLUP\u53e5\u306f\u3001GROP BY Col1, Col2,.. WITH ROLLUP \u3068\u306a\u308a\u307e\u3059\u3002<\/p>\n

CUBE\u3092\u4f7f\u3063\u305f\u4f8b\u306f\u4ee5\u4e0b\uff08\u3053\u3061\u3089\u3082\u7dcf\u5408\u8a08\u3001\u5c0f\u8a08\u306e\u8868\u73fe\u3092\u8a2d\u5b9a\u3057\u305f\u3082\u306e\uff09\uff1a
SELECT
\u00a0 CASE WHEN GROUPING(A.\u53d7\u6ce8\u65e5) = 1 THEN ‘\u7dcf\u5408\u8a08’
\u00a0 \u00a0 \u00a0 \u00a0ELSE CAST(A.\u53d7\u6ce8\u65e5 AS VARCHAR)
\u00a0 \u00a0 \u00a0 \u00a0END AS \u53d7\u6ce8\u65e5
, CASE WHEN GROUPING(B.\u5206\u985e) = 0 THEN B.\u5206\u985e
\u00a0 \u00a0 \u00a0 WHEN GROUPING(A.\u53d7\u6ce8\u65e5) = 1 THEN ‘ ‘
\u00a0 \u00a0 \u00a0 \u00a0WHEN GROUPING(B.\u5206\u985e) = 1 THEN ‘1\u65e5\u5206\u5c0f\u8a08’
\u00a0 \u00a0 \u00a0 \u00a0END AS \u5206\u985e
, SUM(A.\u5546\u54c1\u6570\u91cf)\u6570\u91cf
, SUM(A.\u58f2\u4e0a\u91d1\u984d)\u58f2\u4e0a\u91d1\u984d
\u00a0 FROM \u58f2\u4e0a\u30c6\u30fc\u30d6\u30eb A
\u00a0 JOIN \u5546\u54c1\u30de\u30b9\u30bf B ON A.\u5546\u54c1CD = B.\u5546\u54c1CD
\u00a0GROUP BY A.\u53d7\u6ce8\u65e5, B.\u5206\u985e WITH CUBE
\u00a0ORDER BY \u53d7\u6ce8\u65e5, GROUPING(B.\u5206\u985e), B.\u5206\u985e;
\"sqlsv_groupby_cube2\"<\/a>
<\/a>\u203bCUBE\u53e5\u306f\u3001GROP BY Col1, Col2,.. WITH CUBE \u3068\u306a\u308a\u307e\u3059\u3002
Oracle\u306e\u3068\u304d\u3068\u540c\u69d8\u306a\u7d50\u679c\u304c\u51fa\u307e\u3057\u305f\u3002<\/p>\n

\u3061\u306a\u307f\u306b\u3001MySQL\u306e\u5834\u5408\u3082 ROLLUP\u30fbCUBE\u53e5\u306f\u3001SQL Server\u3068\u540c\u3058\u304f
\u00a0 GROP BY Col1, Col2,.. WITH ROLLUP
\u3000GROP BY Col1, Col2,.. WITH CUBE
\u3068\u306a\u308a\u307e\u3059\u3002Oracle\u304c\u72ec\u81ea\u306e\u3088\u3046\u3067\u3059\u306d\u3002<\/p>\n

\u4eca\u65e5\u306f\u4ee5\u4e0a\u307e\u3067<\/p>\n","protected":false},"excerpt":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002 \u30b7\u30e7\u30c3\u30d4\u30f3\u30b0\u30bb\u30f3\u30bf\u30fc\u306e\u5916\u306e\u5e83\u5834\u304c\u98fe\u308a\u3064\u3051\u3089\u308c\u3066\u3044\u308b\u306e\u3092\u3001\u5a18\u304c\u4ee5\u524d\u5915\u65b9\u306b\u64ae\u3063\u305f\u5199\u771f\u3067\u3059\u3002\u3053\u3046\u3044\u3046\u98fe\u308a\u304c\u3042\u308b\u3060\u3051\u3067\u96f0\u56f2\u6c17\u306f\u5909\u308f\u308a\u307e\u3059\u3088\u306d\u3002\u3053\u308c\u304b\u3089\u3001\u30af\u30ea\u30b9\u30de\u30b9\u306b\u5411\u3051\u3066\u3082\u3063\u3068\u3044\u308d\u3044\u308d\u306a\u5834\u6240\u3067\u304d […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[9],"tags":[],"class_list":["post-493","post","type-post","status-publish","format-standard","hentry","category-9"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/493","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=493"}],"version-history":[{"count":16,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/493\/revisions"}],"predecessor-version":[{"id":1966,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/493\/revisions\/1966"}],"wp:attachment":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}