{"id":6689,"date":"2024-11-22T10:16:00","date_gmt":"2024-11-22T01:16:00","guid":{"rendered":"https:\/\/taitan916.info\/blog\/?p=6689"},"modified":"2024-08-28T15:19:54","modified_gmt":"2024-08-28T06:19:54","slug":"post-6689","status":"publish","type":"post","link":"https:\/\/taitan916.info\/blog\/archives\/6689","title":{"rendered":"MySQL\u306b\u3066SELECT\u6642\u306bdate\u578b\u306e\u30ab\u30e9\u30e0\u304b\u3089\u5e72\u652f(\u5341\u4e8c\u652f)\u3092\u8a08\u7b97\u3057\u8868\u793a\u3059\u308b\u65b9\u6cd5"},"content":{"rendered":"<p>MySQL\u306bdate\u578b\u306e\u30ab\u30e9\u30e0\u306b\u8a95\u751f\u5e74\u6708\u65e5\u304c\u683c\u7d0d\u3055\u308c\u3066\u304a\u308a\u3001\u305d\u3053\u304b\u3089\u5e72\u652f(\u5341\u4e8c\u652f)\u3092\u8a08\u7b97\u3057\u3066\u53d6\u5f97\u3057\u305f\u3044\u3002\u4ee5\u4e0b\u306b\u5bfe\u5fdc\u65b9\u6cd5\u3092\u30e1\u30e2\u3002<\/p>\n<p>&nbsp;<\/p>\n<h2><span id=\"i\">\u30c6\u30fc\u30d6\u30eb\u69cb\u9020\u3068\u30c7\u30fc\u30bf\u5185\u5bb9<\/span><\/h2>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">mysql&gt; SHOW COLUMNS FROM `test_table`;\r\n+----------+------+------+-----+---------+----------------+\r\n| Field    | Type | Null | Key | Default | Extra          |\r\n+----------+------+------+-----+---------+----------------+\r\n| id       | int  | NO   | PRI | NULL    | auto_increment |\r\n| birthday | date | NO   |     | NULL    |                |\r\n+----------+------+------+-----+---------+----------------+\r\n\r\nmysql&gt; SELECT * FROM `test_table`;\r\n+----+------------+\r\n| id | birthday   |\r\n+----+------------+\r\n|  1 | 2020-01-01 |\r\n|  2 | 2019-01-01 |\r\n|  3 | 2018-01-01 |\r\n|  4 | 2017-01-01 |\r\n|  5 | 2016-01-01 |\r\n|  6 | 2015-01-01 |\r\n|  7 | 2014-01-01 |\r\n|  8 | 2013-01-01 |\r\n|  9 | 2012-01-01 |\r\n| 10 | 2011-01-01 |\r\n| 11 | 2010-01-01 |\r\n| 12 | 2009-01-01 |\r\n| 13 | 2008-01-01 |\r\n+----+------------+<\/pre>\n<p>&nbsp;<\/p>\n<h2><span id=\"i-2\">\u5bfe\u5fdc\u65b9\u6cd5<\/span><\/h2>\n<h3><span id=\"SQL\">SQL\u6587<\/span><\/h3>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">SELECT `birthday`, \r\n    CASE \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 0 THEN '\u5b50(\u306d\u305a\u307f)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 1 THEN '\u4e11(\u3046\u3057)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 2 THEN '\u5bc5(\u3068\u3089)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 3 THEN '\u536f(\u3046\u3055\u304e)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 4 THEN '\u8fb0(\u305f\u3064)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 5 THEN '\u5df3(\u307f)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 6 THEN '\u5348(\u3046\u307e)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 7 THEN '\u672a(\u3072\u3064\u3058)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 8 THEN '\u7533(\u3055\u308b)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 9 THEN '\u9149(\u3068\u308a)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 10 THEN '\u620c(\u3044\u306c)' \r\n        WHEN (DATE_FORMAT(`birthday`,\"%Y\") - 4) % 12 = 11 THEN '\u4ea5(\u3044)' \r\n    END AS `eto`\r\nFROM `test_table`;<\/pre>\n<h3>\u51fa\u529b\u7d50\u679c<\/h3>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">+------------+----------------+\r\n| birthday   | eto            |\r\n+------------+----------------+\r\n| 2020-01-01 | \u5b50(\u306d\u305a\u307f)     |\r\n| 2019-01-01 | \u4ea5(\u3044)         |\r\n| 2018-01-01 | \u620c(\u3044\u306c)       |\r\n| 2017-01-01 | \u9149(\u3068\u308a)       |\r\n| 2016-01-01 | \u7533(\u3055\u308b)       |\r\n| 2015-01-01 | \u672a(\u3072\u3064\u3058)     |\r\n| 2014-01-01 | \u5348(\u3046\u307e)       |\r\n| 2013-01-01 | \u5df3(\u307f)         |\r\n| 2012-01-01 | \u8fb0(\u305f\u3064)       |\r\n| 2011-01-01 | \u536f(\u3046\u3055\u304e)     |\r\n| 2010-01-01 | \u5bc5(\u3068\u3089)       |\r\n| 2009-01-01 | \u4e11(\u3046\u3057)       |\r\n| 2008-01-01 | \u5b50(\u306d\u305a\u307f)     |\r\n+------------+----------------+<\/pre>\n<p>&nbsp;<\/p>\n<h2>\u53c2\u8003\u30b5\u30a4\u30c8<\/h2>\n<p><a href=\"https:\/\/patareru.net\/archives\/129\" target=\"_blank\" rel=\"noopener\">https:\/\/patareru.net\/archives\/129<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL\u306bdate\u578b\u306e\u30ab\u30e9\u30e0\u306b\u8a95\u751f\u5e74\u6708\u65e5\u304c\u683c\u7d0d\u3055\u308c\u3066\u304a\u308a\u3001\u305d\u3053\u304b\u3089\u5e72\u652f(\u5341\u4e8c\u652f ... <\/p>\n","protected":false},"author":1,"featured_media":1072,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[5,66],"tags":[],"class_list":["post-6689","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","category-database"],"acf":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/posts\/6689","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/comments?post=6689"}],"version-history":[{"count":1,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/posts\/6689\/revisions"}],"predecessor-version":[{"id":6690,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/posts\/6689\/revisions\/6690"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/media\/1072"}],"wp:attachment":[{"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/media?parent=6689"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/categories?post=6689"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/tags?post=6689"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}