{"id":689,"date":"2023-11-21T23:59:39","date_gmt":"2023-11-21T14:59:39","guid":{"rendered":"https:\/\/web.cloudfree.jp\/journal\/?p=689"},"modified":"2023-12-01T10:28:23","modified_gmt":"2023-12-01T01:28:23","slug":"mysql%e3%81%ae%e5%8b%89%e5%bc%b7%ef%bc%9awindow%e9%96%a2%e6%95%b0","status":"publish","type":"post","link":"https:\/\/web.cloudfree.jp\/journal\/mysql%e3%81%ae%e5%8b%89%e5%bc%b7%ef%bc%9awindow%e9%96%a2%e6%95%b0\/","title":{"rendered":"MySQL\u306e\u52c9\u5f37\uff1aWINDOW\u95a2\u6570"},"content":{"rendered":"

MySQL\u306e\u52c9\u5f37\uff1aWINDOW\u95a2\u6570<\/h3>\n\n\n

2023-11-21 \u8a18\u8f09
\u6982\u8981 : MySQL8.0\u3067\u65b0\u898f\u5c0e\u5165\u306eWINDOW\u95a2\u6570\u3092\u5b66\u3076
\u95a2\u9023\u8a18\u4e8b : MySQL\u306e\u52c9\u5f37\uff1a\u5171\u901a\u8868\u5f62\u5f0fCTE CommonTableExpression<\/a>
Keyword : WINDOW\u95a2\u6570, MySQL<\/p>\n\n\n\n

\u66f8\u7c4d\u300eMySQL\u5fb9\u5e95\u5165\u9580 :\u7b2c4\u7248 -MySQL 8.0\u5bfe\u5fdc\u300f\u306b\u3066\u5b66\u3093\u3060\u3053\u3068\u2015\u2015WINDOW\u95a2\u6570\u3002

\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u30c6\u30fc\u30d6\u30eb\u304c\u3042\u3063\u305f\u3068\u3057\u307e\u3059\u3002
\u793e\u54e1\u98df\u5802\u3067\u3001\u597d\u304d\u306b\u7d44\u307f\u5408\u308f\u305b\u3067\u304d\u308b\u5b9a\u98df\u3092\u60f3\u5b9a\u3057\u307e\u3059\u3002
\u30d7\u30ec\u30fc\u30c8\u306b\uff14\u3064\u306e\u304f\u307c\u307f\u304c\u3042\u308a\u3001\u4e3b\u98df \u30fb\u30e1\u30a4\u30f3\u76bf\u30fb\u526f\u83dc\u30fb\u6c41\u7269\u306e\u5668\u304c\u306f\u307e\u308a\u307e\u3059\u3002\u3064\u307e\u308a\u533a\u5206\u3067\uff11\u3064\u3092\u9078\u3073\u307e\u3059\u3002\u30ec\u30b8\u3067\u4f1a\u8a08\u3057\u307e\u3059\u3002<\/p>\n\n\n\n

auto_id<\/th>\u533a\u5206<\/th>\u54c1\u540d<\/th>\u4fa1\u683c<\/th><\/tr><\/thead>
1<\/td>\u4e3b\u98df <\/td>\u767d\u3054\u306f\u3093<\/td>200<\/td><\/tr>
2<\/td>\u6c41\u7269<\/td>\u5b63\u7bc0\u306e\u5473\u564c\u6c41<\/td>80<\/td><\/tr>
3<\/td>\u4e3b\u98df <\/td>\u304b\u3084\u304f\u3054\u98ef<\/td>250<\/td><\/tr>
4<\/td>\u30e1\u30a4\u30f3\u76bf<\/td>\u30cf\u30f3\u30d0\u30fc\u30b0<\/td>400<\/td><\/tr>
5<\/td>\u30e1\u30a4\u30f3\u76bf<\/td>\u713c\u304d\u9b5a<\/td>300<\/td><\/tr>
6<\/td>\u30e1\u30a4\u30f3\u76bf<\/td>\u304b\u3089\u3042\u3052 <\/td>350<\/td><\/tr>
7<\/td>\u526f\u83dc <\/td>\u51b7\u5974<\/td>200<\/td><\/tr>
8<\/td>\u526f\u83dc <\/td>\u716e\u7269 <\/td>240<\/td><\/tr>
9<\/td>\u526f\u83dc <\/td>\u7d0d\u8c46 <\/td>100<\/td><\/tr>
10<\/td>\u526f\u83dc <\/td>\u3060\u3057\u5dfb\u304d\u5375<\/td>130<\/td><\/tr>
13<\/td>\u30e1\u30a4\u30f3\u76bf<\/td>\u304a\u3064\u304f\u308a<\/td>400<\/td><\/tr>
14<\/td>\u30e1\u30a4\u30f3\u76bf<\/td>\u30b9\u30c6\u30fc\u30ad<\/td>500<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

\u30c7\u30fc\u30bf\u4f5c\u6210SQL\u306f\u3053\u3061\u3089\u3002<\/p>\n\n\n\n

CREATE TABLE `t_test` (\n  `auto_id` int(11) NOT NULL,\n  `\u533a\u5206` varchar(64) NOT NULL,\n  `\u54c1\u540d` varchar(64) NOT NULL,\n  `\u4fa1\u683c` int(11) NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;\nALTER TABLE `t_test`\n  ADD PRIMARY KEY (`auto_id`);\n\nINSERT INTO `t_test` (`auto_id`, `\u533a\u5206`, `\u54c1\u540d`, `\u4fa1\u683c`) VALUES\n(1, '\u4e3b\u98df', '\u767d\u3054\u306f\u3093', 200),\n(2, '\u6c41\u7269', '\u5b63\u7bc0\u306e\u5473\u564c\u6c41', 80),\n(3, '\u4e3b\u98df', '\u304b\u3084\u304f\u3054\u98ef', 250),\n(4, '\u30e1\u30a4\u30f3\u76bf', '\u30cf\u30f3\u30d0\u30fc\u30b0', 400),\n(5, '\u30e1\u30a4\u30f3\u76bf', '\u713c\u304d\u9b5a', 300),\n(6, '\u30e1\u30a4\u30f3\u76bf', '\u304b\u3089\u3042\u3052', 350),\n(7, '\u526f\u83dc', '\u51b7\u5974', 200),\n(8, '\u526f\u83dc', '\u716e\u7269', 240),\n(9, '\u526f\u83dc', '\u7d0d\u8c46', 100),\n(10, '\u526f\u83dc', '\u3060\u3057\u5dfb\u304d\u5375', 130),\n(13, '\u30e1\u30a4\u30f3\u76bf', '\u304a\u3064\u304f\u308a', 400),\n(14, '\u30e1\u30a4\u30f3\u76bf', '\u30b9\u30c6\u30fc\u30ad', 500);<\/code><\/pre><\/div>\n\n\n\n
\u300c\u5b89\u3042\u304c\u308a\u306a\u7d44\u307f\u5408\u308f\u305b\u306f\uff1f\u300d\u3068\u3044\u3046\u89b3\u70b9\u3067\u307f\u308b\u3068\u304d
\u3053\u3046\u3044\u3046SQL\u3092\u767a\u884c\u3067\u304d\u307e\u3059\u3002<\/p>\n\n\n\n
select * , RANK() \nover (PARTITION by \u533a\u5206 ORDER by \u4fa1\u683c) as \u5b89\u3044\u9806 \nfrom t_test;<\/code><\/pre><\/div>\n\n\n\n
auto_id\t\u533a\u5206\t\t\u54c1\u540d\t\t\u4fa1\u683c\t\u5b89\u3044\u9806\n5\t\u30e1\u30a4\u30f3\u76bf\t\u713c\u304d\u9b5a\t\t300\t1\n6\t\u30e1\u30a4\u30f3\u76bf\t\u304b\u3089\u3042\u3052\t350\t2\n4\t\u30e1\u30a4\u30f3\u76bf\t\u30cf\u30f3\u30d0\u30fc\u30b0\t400\t3\n13\t\u30e1\u30a4\u30f3\u76bf\t\u304a\u3064\u304f\u308a\t400\t3\n14\t\u30e1\u30a4\u30f3\u76bf\t\u30b9\u30c6\u30fc\u30ad\t500\t5\n1\t\u4e3b\u98df\t\t\u767d\u3054\u306f\u3093\t200\t1\n3\t\u4e3b\u98df\t\t\u304b\u3084\u304f\u3054\u98ef\t250\t2\n9\t\u526f\u83dc\t\t\u7d0d\u8c46\t\t100\t1\n10\t\u526f\u83dc\t\t\u3060\u3057\u5dfb\u304d\u5375\t130\t2\n7\t\u526f\u83dc\t\t\u51b7\u5974\t\t200\t3\n8\t\u526f\u83dc\t\t\u716e\u7269\t\t240\t4\n2\t\u6c41\u7269\t\t\u5b63\u7bc0\u306e\u5473\u564c\u6c41\t80\t1<\/code><\/pre>\n\n\n\n
WINDOW\u95a2\u6570=\u300c\u884c\u3092\u3072\u3068\u3064\u306b\u307e\u3068\u3081\u306a\u3044Group By\u95a2\u6570\u300d\u3060\u305d\u3046\u3067\u3059\u3002
\u540c\u4fa1\u683c\u306f\u540c\u9806\u4f4d\u3067\u3001\u91cd\u306a\u3063\u305f\u3076\u3093\u3060\u3051\u6b21\u306f\u30c8\u30f3\u3067\u9023\u756a\u306f\u6b20\u3051\u308b\u3093\u3067\u3059\u306d\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
2023-11-21 \u8a18\u8f09\u6982\u8981 : MySQL8.0\u3067\u65b0\u898f\u5c0e\u5165\u306eWINDOW\u95a2\u6570\u3092\u5b66\u3076\u95a2\u9023\u8a18\u4e8b : MySQL\u306e\u52c9\u5f37\uff1a\u5171\u901a\u8868\u5f62\u5f0fCTE CommonTableExpressionKeyword : WINDOW\u95a2\u6570, M […]<\/p>\n","protected":false},"author":1,"featured_media":691,"comment_status":"open","ping_status":"closed","sticky":false,"template":"wp-custom-template-x24-index","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[59,58,73,56],"_links":{"self":[{"href":"https:\/\/web.cloudfree.jp\/journal\/wp-json\/wp\/v2\/posts\/689"}],"collection":[{"href":"https:\/\/web.cloudfree.jp\/journal\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/web.cloudfree.jp\/journal\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/web.cloudfree.jp\/journal\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/web.cloudfree.jp\/journal\/wp-json\/wp\/v2\/comments?post=689"}],"version-history":[{"count":0,"href":"https:\/\/web.cloudfree.jp\/journal\/wp-json\/wp\/v2\/posts\/689\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/web.cloudfree.jp\/journal\/wp-json\/wp\/v2\/media\/691"}],"wp:attachment":[{"href":"https:\/\/web.cloudfree.jp\/journal\/wp-json\/wp\/v2\/media?parent=689"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/web.cloudfree.jp\/journal\/wp-json\/wp\/v2\/categories?post=689"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/web.cloudfree.jp\/journal\/wp-json\/wp\/v2\/tags?post=689"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}