{"id":620,"date":"2015-02-05T15:12:37","date_gmt":"2015-02-05T06:12:37","guid":{"rendered":"http:\/\/www.dbsheetclient.jp\/blog\/?p=620"},"modified":"2018-06-27T16:19:16","modified_gmt":"2018-06-27T07:19:16","slug":"%e8%a4%87%e6%95%b0%e8%a1%8c%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf%e3%82%92%e9%9b%86%e8%a8%88%e3%81%97%e3%81%a6%e6%a8%aa%e5%b1%95%e9%96%8b","status":"publish","type":"post","link":"https:\/\/www.dbsheetclient.jp\/blog\/?p=620","title":{"rendered":"\u8907\u6570\u884c\u306e\u30c7\u30fc\u30bf\u3092\u96c6\u8a08\u3057\u3066\u6a2a\u5c55\u958b"},"content":{"rendered":"

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

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

\u00a0\u57fc\u7389\u770c\u52a0\u9808\u5e02\u306b\u3042\u308b\u3001\u52a0\u9808\u82b1\u5d0e\u6c34\u4e0a\u516c\u5712\u306e\u6563\u6b69\u9053\u3067\u3059\u3002\u30d7\u30fc\u30eb\u3068\u81ea\u7136\u89b3\u5bdf\u5712\u306e\u9593\u306e\u307b\u3046\u306e\u9053\u3067\u64ae\u3063\u305f\u5199\u771f\u306b\u306a\u308a\u307e\u3059\u3002\u6c34\u4e0a\u516c\u5712\u3068\u3044\u3046\u3053\u3068\u3067\u3001\u590f\u306e\u30d7\u30fc\u30eb\u304c\u6ce8\u76ee\u3055\u308c\u307e\u3059\u304c\u3001\u304a\u82b1\u898b\u3084\u30d0\u30fc\u30d9\u30ad\u30e5\u30fc\u3001\u307e\u305f\u81ea\u7136\u89b3\u5bdf\u5712\u3067\u306f\u3044\u308d\u3044\u308d\u306a\u6728\u3005\u3084\u30a6\u30c3\u30c9\u30c7\u30c3\u30ad\u3001\u91ce\u9ce5\u306e\u89b3\u5bdf\u5c0f\u5c4b\u306a\u3069\u3082\u3042\u308a\u3001\u5b63\u7bc0\u306b\u3088\u3063\u3066\u3044\u308d\u3044\u308d\u306a\u8868\u60c5\u304c\u898b\u308c\u308b\u306e\u3067\u3001\u79c1\u306f\u3068\u3066\u3082\u6c17\u306b\u5165\u3063\u3066\u3044\u307e\u3059\u3002<\/p>\n

\uff1c\u672c\u65e5\u306e\u984c\u6750\uff1e
\u8907\u6570\u884c\u306e\u30c7\u30fc\u30bf\u3092\u96c6\u8a08\u3057\u3066\u6a2a\u5c55\u958b
<\/strong>
\u4eca\u56de\u306f\u3001\u30c6\u30fc\u30d6\u30eb\u30ec\u30a4\u30a2\u30a6\u30c8\u4e0a\u3001\u30ad\u30fc\u6bce\u306b\u8907\u6570\u884c\u3042\u308b\u30c7\u30fc\u30bf\u3092\u3001\u6a2a\u306b\u4e26\u3079\u3066\u96c6\u8a08\u3057\u306a\u304c\u30891\u884c\u3067\u8868\u793a\u3055\u305b\u305f\u3044\u5834\u5408\u306eSQL\u306b\u3064\u3044\u3066\u53d6\u308a\u4e0a\u3052\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002\uff08\u4ee5\u524d\u3001\u30ab\u30f3\u30de\u533a\u5207\u308a\u3067\u6a2a\u4e26\u3073\u306b\u3059\u308b\u30b1\u30fc\u30b9\u306b\u3064\u3044\u3066\u3001SQL Server\u306e\u5834\u5408\u306bFOR XML\u53e5\u3092\u53d6\u308a\u4e0a\u3052\u305f\u3053\u3068\u304c\u3042\u308a\u307e\u3059\u304c\u3001\u4eca\u56de\u306f\u3088\u304f\u4f7f\u3046\u4f8b\u3068\u3057\u3066CASE\u6587\u3092\u4f7f\u7528\u3057\u307e\u3059\uff09<\/p>\n

\u4f8b\u3048\u3070\u3001\u90e8\u54c1\u5225\u306e\u65e5\u5225\u306e\u4ed5\u5165\u6570\u91cf\u306e\u30c7\u30fc\u30bf\u304c\u3001\u90e8\u54c1\u30b3\u30fc\u30c9\u3001\u5e74\u6708\u65e5\u3001\u6570\u91cf\u3068\u3044\u3046\u3088\u3046\u306a\u30ec\u30a4\u30a2\u30a6\u30c8\u3067DB\u306b\u767b\u9332\u3055\u308c\u3066\u3044\u308b\u5834\u5408\u306b\u3001\u90e8\u54c1\u5225\u306b\u3001\u6708\u3092\u6a2a\u306b\u4e26\u3079\u3066\u4ed5\u5165\u6570\u91cf\u306e\u6708\u5225\u5408\u8a08\u5024\u3092\u8868\u793a\u3055\u305b\u308b\u3088\u3046\u306a\u30b1\u30fc\u30b9\u3002(SQL Server\u3067\u691c\u8a3c)<\/p>\n

DB\u4e0a\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u30c7\u30fc\u30bf\u304c\u767b\u9332\u3055\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n

SELECT
\u00a0 \u00a0 \u90e8\u54c1\u30b3\u30fc\u30c9
\u00a0 , SUBSTRING(\u5e74\u6708\u65e5,1,6) AS \u5e74\u6708
\u00a0 , SUM(\u6570\u91cf) \u00a0AS \u4ed5\u5165\u6570\u91cf
\u00a0 FROM \u90e8\u54c1\u767a\u6ce8\u8868
\u00a0GROUP BY \u90e8\u54c1\u30b3\u30fc\u30c9, SUBSTRING(\u5e74\u6708\u65e5,1,6)
\u00a0ORDER BY \u90e8\u54c1\u30b3\u30fc\u30c9, SUBSTRING(\u5e74\u6708\u65e5,1,6);<\/p>\n

\"\u90e8\u54c1\u767a\u6ce8\u30c7\u30fc\u30bf\"<\/p>\n

<\/a>\u3053\u308c\u3092\u3001\u90e8\u54c1\u30b3\u30fc\u30c9\u6bce\u3067\u3001\u6708\u3092\u6a2a\u306b\u4e26\u3079\u3066\u4ed5\u5165\u6570\u91cf\u3092\u8868\u793a\u3055\u305b\u305f\u3044\u5834\u5408\u3001\u4ee5\u4e0b\u306e\u3088\u3046\u306aSQL\u3092\u5b9f\u884c\u3057\u307e\u3059\u3002<\/p>\n

SELECT
\u00a0 A.\u90e8\u54c1\u30b3\u30fc\u30c9
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’04’ THEN A.\u6570\u91cf ELSE 0 END) AS “4\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’05’ THEN A.\u6570\u91cf ELSE 0 END) AS “5\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’06’ THEN A.\u6570\u91cf ELSE 0 END) AS “6\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’07’ THEN A.\u6570\u91cf ELSE 0 END) AS “7\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’08’ THEN A.\u6570\u91cf ELSE 0 END) AS “8\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’09’ THEN A.\u6570\u91cf ELSE 0 END) AS “9\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’10’ THEN A.\u6570\u91cf ELSE 0 END) AS “10\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’11’ THEN A.\u6570\u91cf ELSE 0 END) AS “11\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’12’ THEN A.\u6570\u91cf ELSE 0 END) AS “12\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’01’ THEN A.\u6570\u91cf ELSE 0 END) AS “1\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’02’ THEN A.\u6570\u91cf ELSE 0 END) AS “2\u6708”
, SUM(CASE SUBSTRING(A.\u5e74\u6708\u65e5,5,2) WHEN ’03’ THEN A.\u6570\u91cf ELSE 0 END) AS “3\u6708”
\u00a0 FROM [dbo].[\u90e8\u54c1\u767a\u6ce8\u8868] A
\u00a0 JOIN [dbo].[\u5e74\u5ea6\u30de\u30b9\u30bf] B ON SUBSTRING(A.\u5e74\u6708\u65e5,1,6) = B.\u5e74\u6708
\u00a0WHERE B.\u5e74\u5ea6 = ‘2014’
\u00a0GROUP BY A.\u90e8\u54c1\u30b3\u30fc\u30c9
\u00a0ORDER BY A.\u90e8\u54c1\u30b3\u30fc\u30c9;<\/p>\n

\"\u90e8\u54c1\u767a\u6ce8\u6a2a\u4e26\u3073\"<\/a><\/p>\n

\u3053\u306e\u3088\u3046\u306b\u3001\u90e8\u54c1\u30b3\u30fc\u30c9\u6bce\u306b\u30014\u6708\uff5e3\u6708\u307e\u3067\u6708\u6bce\u306e\u4ed5\u5165\u6570\u91cf\u3092\u6a2a\u4e26\u3073\u3067\u8868\u793a\u3055\u305b\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3057\u305f\u3002<\/p>\n

\u4e0a\u8a18\u3067\u306f\u5e74\u5ea6\u30de\u30b9\u30bf\u3092\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u8a2d\u5b9a\u3057\u3066\u3044\u307e\u3059\u3002
SELECT * FROM [dbo].[\u5e74\u5ea6\u30de\u30b9\u30bf]
\u00a0ORDER BY \u5e74\u5ea6,\u8868\u793a\u9806;<\/p>\n

\"\u5e74\u5ea6\u30de\u30b9\u30bf\"<\/a>
<\/a><\/p>\n

\u00a0ORACLE\u3067\u3082\u540c\u69d8\u306b\u884c\u3046\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u304c\u3001\u4e0a\u8a18\u306eSUBSTRING\u95a2\u6570\u306f\u3001ORACLE\u3067\u306fSUBSTR\u95a2\u6570\u3068\u306a\u308a\u307e\u3059\u3002\u305d\u3053\u3060\u3051\u6ce8\u610f\u3059\u308c\u3070\u540c\u69d8\u306b\u5b9f\u884c\u3067\u304d\u307e\u3059\u3002<\/p>\n

\u307e\u305f\u3001\u4e0a\u8a18\u306e\u4f8b\u306f\u3001\u6570\u91cf\u306e\u5834\u5408\u3067\u3057\u305f\u304c\u3001\u6587\u5b57\u9805\u76ee\u3092\u6a2a\u4e26\u3073\u306b\u3057\u305f\u3044\u5834\u5408\u3082\u3042\u308b\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

\u4f8b\u3048\u3070\u3001\u4e0b\u8a18\u306e\u3088\u3046\u306b\u3001\u65e5\u672c\u306e\u69d8\u3005\u306a\u30e9\u30f3\u30ad\u30f3\u30b0\u3092\u30c7\u30fc\u30bf\u3068\u3057\u3066\u6301\u3064\u30c6\u30fc\u30d6\u30eb\u304c\u3042\u308b\u3068\u3057\u307e\u3059\u3002<\/p>\n

SELECT
\u00a0 A.\u533a\u5206NO
, B.\u533a\u5206\u540d
, A.\u540d\u79f0
, A.\u6570\u5024
, B.\u5358\u4f4d
\u00a0 FROM dbo.\u30e9\u30f3\u30ad\u30f3\u30b0 A
\u00a0 JOIN dbo.\u30e9\u30f3\u30ad\u30f3\u30b0\u533a\u5206 B ON A.\u533a\u5206NO = B.\u533a\u5206NO
\u00a0 ORDER BY A.\u533a\u5206NO, A.NO;<\/p>\n

\"\u65e5\u672c\u30e9\u30f3\u30ad\u30f3\u30b0\"
<\/a>\u203b\u30c7\u30fc\u30bf\u306f\u3082\u3063\u3068\u305f\u304f\u3055\u3093\u3042\u308a\u307e\u3059\u304c\u3001\u8868\u793a\u306f\u4e0a\u8a18\u307e\u3067<\/p>\n

\u3053\u308c\u3089\u306e\u90fd\u5e02\u4eba\u53e3\u3084\u5ddd\u306e\u9577\u3055\u3001\u6e56\u306e\u9762\u7a4d\u3001\u5c71\u306e\u9ad8\u3055\u306a\u3069\u306e\u533a\u5206\u3054\u3068\u306b\u3001\u30e9\u30f3\u30ad\u30f3\u30b0\u306e1\u4f4d\u304b\u308910\u4f4d\u307e\u3067\u3092\u6a2a\u4e26\u3073\u3067\u8868\u793a\u3055\u305b\u305f\u3044\u3068\u3057\u307e\u3059\u3002<\/p>\n

\u307e\u305a\u3001\u5404\u533a\u5206\u6bce\u306b\u30e9\u30f3\u30ad\u30f3\u30b0\u60c5\u5831\u3092\u62bd\u51fa\u3059\u308b\u306e\u306f\u3001\u4ee5\u4e0b\uff1a<\/p>\n

SELECT
\u00a0 A.\u533a\u5206NO
, B.\u533a\u5206\u540d
, A.\u540d\u79f0
, RANK() OVER (PARTITION BY A.\u533a\u5206NO ORDER BY A.\u6570\u5024 DESC) RANK\u9806\u4f4d
, A.\u6570\u5024
, B.\u5358\u4f4d
\u00a0 FROM dbo.\u30e9\u30f3\u30ad\u30f3\u30b0 A
\u00a0 JOIN dbo.\u30e9\u30f3\u30ad\u30f3\u30b0\u533a\u5206 B ON A.\u533a\u5206NO = B.\u533a\u5206NO
\u00a0 ORDER BY A.\u533a\u5206NO, RANK\u9806\u4f4d;<\/p>\n

\"\u65e5\u672c\u30e9\u30f3\u30ad\u30f3\u30b0_\u30e9\u30f3\u30af\u9806\"
<\/a>\u203b\u30c7\u30fc\u30bf\u306f\u3082\u3063\u3068\u305f\u304f\u3055\u3093\u3042\u308a\u307e\u3059\u304c\u3001\u8868\u793a\u306f\u4e0a\u8a18\u307e\u3067<\/p>\n

\u3053\u308c\u3092\u5404\u533a\u5206\u6bce\u306b\u6a2a\u4e26\u3073\u3067\u8868\u793a\u3059\u308b\u306e\u306f\u3001\u4f8b\u3048\u3070\u4ee5\u4e0b\u306eSQL\u306e\u3088\u3046\u306b\u3001MAX\u95a2\u6570\u306a\u3069\u3092\u4f7f\u7528\u3057\u3066\u96c6\u7d04\u3057\u305f\u304b\u305f\u3061\u306b\u3057\u307e\u3059\u3002<\/p>\n

\u00a0SELECT
\u00a0 C.\u533a\u5206NO
, C.\u533a\u5206\u540d
, MAX(CASE C.RANK\u9806\u4f4d WHEN 1 THEN C.\u540d\u79f0 ELSE ” END)
\u00a0 AS “1\u4f4d”
, MAX(CASE C.RANK\u9806\u4f4d WHEN 2 THEN C.\u540d\u79f0 ELSE ” END)
\u00a0 AS “2\u4f4d”
, MAX(CASE C.RANK\u9806\u4f4d WHEN 3 THEN C.\u540d\u79f0 ELSE ” END)
\u00a0 AS “3\u4f4d”
, MAX(CASE C.RANK\u9806\u4f4d WHEN 4 THEN C.\u540d\u79f0 ELSE ” END)
\u00a0 AS “4\u4f4d”
, MAX(CASE C.RANK\u9806\u4f4d WHEN 5 THEN C.\u540d\u79f0 ELSE ” END)
\u00a0AS “5\u4f4d”
, MAX(CASE C.RANK\u9806\u4f4d WHEN 6 THEN C.\u540d\u79f0 ELSE ” END)
\u00a0 AS “6\u4f4d”
, MAX(CASE C.RANK\u9806\u4f4d WHEN 7 THEN C.\u540d\u79f0 ELSE ” END)
\u00a0 AS “7\u4f4d”
, MAX(CASE C.RANK\u9806\u4f4d WHEN 8 THEN C.\u540d\u79f0 ELSE ” END)
\u00a0 AS “8\u4f4d”
, MAX(CASE C.RANK\u9806\u4f4d WHEN 9 THEN C.\u540d\u79f0 ELSE ” END)
\u00a0 AS “9\u4f4d”
, MAX(CASE C.RANK\u9806\u4f4d WHEN 10 THEN C.\u540d\u79f0 ELSE ” END)
\u00a0 AS “10\u4f4d”
\u00a0FROM
\u00a0(SELECT
\u00a0 \u00a0A.\u533a\u5206NO
\u00a0, B.\u533a\u5206\u540d
\u00a0, A.\u540d\u79f0
\u00a0, RANK() OVER (PARTITION BY A.\u533a\u5206NO ORDER BY A.\u6570\u5024 DESC) RANK\u9806\u4f4d
\u00a0, A.\u6570\u5024
\u00a0 \u00a0 FROM dbo.\u30e9\u30f3\u30ad\u30f3\u30b0 A
\u00a0 \u00a0 JOIN dbo.\u30e9\u30f3\u30ad\u30f3\u30b0\u533a\u5206 B ON A.\u533a\u5206NO = B.\u533a\u5206NO
\u00a0) C
\u00a0GROUP BY C.\u533a\u5206NO, C.\u533a\u5206\u540d
\u00a0ORDER BY C.\u533a\u5206NO;<\/p>\n

\"\u65e5\u672c\u30e9\u30f3\u30ad\u30f3\u30b0_\u30e9\u30f3\u30af\u9806\u6a2a\u4e26\u3073\"<\/a><\/p>\n

\u00a0\u203b\u6700\u8fd1\u306e\u7d71\u8a08\u3067\u306f\u3001\u6700\u4e0a\u5ddd\u3068\u6728\u66fd\u5ddd\u306f\u3069\u3061\u3089\u3082229km\u3067\u540c\u30587\u4f4d\u3068\u306a\u3063\u3066\u3044\u308b\u3088\u3046\u3067\u3059\u304c\u3001\u4ee5\u524d\u306f\u6700\u4e0a\u5ddd\u304c7\u4f4d\u3001\u6728\u66fd\u5ddd\u304c8\u4f4d\u3068\u3055\u308c\u3066\u3044\u305f\u3088\u3046\u3067\u3059\u3002\u4eca\u56de\u306f\u3001\u53d6\u308a\u3042\u3048\u305a\u305d\u3061\u3089\u3092\u4f7f\u3063\u3066\u3044\u307e\u3059\u3002<\/p>\n

\u4e0a\u8a18\u306f\u3001ORACLE\u3067\u3082\u307e\u3063\u305f\u304f\u540c\u69d8\u306b\u5b9f\u884c\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\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 \u00a0\u57fc\u7389\u770c\u52a0\u9808\u5e02\u306b\u3042\u308b\u3001\u52a0\u9808\u82b1\u5d0e\u6c34\u4e0a\u516c\u5712\u306e\u6563\u6b69\u9053\u3067\u3059\u3002\u30d7\u30fc\u30eb\u3068\u81ea\u7136\u89b3\u5bdf\u5712\u306e\u9593\u306e\u307b\u3046\u306e\u9053\u3067\u64ae\u3063\u305f\u5199\u771f\u306b\u306a\u308a\u307e\u3059\u3002\u6c34\u4e0a\u516c\u5712\u3068\u3044\u3046\u3053\u3068\u3067\u3001\u590f\u306e\u30d7\u30fc\u30eb\u304c\u6ce8\u76ee\u3055\u308c\u307e\u3059\u304c\u3001\u304a\u82b1\u898b\u3084\u30d0\u30fc\u30d9\u30ad\u30e5\u30fc\u3001\u307e\u305f\u81ea […]<\/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":[7],"tags":[],"class_list":["post-620","post","type-post","status-publish","format-standard","hentry","category-7"],"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\/620","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=620"}],"version-history":[{"count":6,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/620\/revisions"}],"predecessor-version":[{"id":1960,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/620\/revisions\/1960"}],"wp:attachment":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=620"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=620"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=620"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}