{"id":3255,"date":"2020-09-18T06:56:00","date_gmt":"2020-09-17T21:56:00","guid":{"rendered":"https:\/\/taitan916.info\/blog\/?p=3255"},"modified":"2024-01-16T00:11:02","modified_gmt":"2024-01-15T15:11:02","slug":"post-3255","status":"publish","type":"post","link":"https:\/\/taitan916.info\/blog\/archives\/3255","title":{"rendered":"MySQL\u30671\u5bfe\u591a\u3068\u3044\u3046\u30c6\u30fc\u30d6\u30eb\u69cb\u9020\u3067\u30c7\u30fc\u30bf\u7d50\u5408\u3068\u6761\u4ef6\u691c\u7d22\u3059\u308b\u65b9\u6cd5"},"content":{"rendered":"<p>MySQL\u3067\u98f2\u98df\u5e97\u8217\u30c6\u30fc\u30d6\u30eb\u3068\u4f11\u696d\u65e5\u30c6\u30fc\u30d6\u30eb\u304c\u3042\u308a\u30011\u5e97\u8217\u306b\u5bfe\u3057\u3066\u8907\u6570\u884c\u306e\u4f11\u696d\u65e5\u30c7\u30fc\u30bf\u304c\u5165\u3063\u3066\u3044\u308b\u3068\u3044\u3046\u30b1\u30fc\u30b9\u304c\u3042\u3063\u305f\u30021\u5bfe\u591a\u306e\u30c7\u30fc\u30bf\u3092\u7d50\u5408\u3057\u3064\u3064\u6761\u4ef6\u691c\u7d22\u3084\u30bd\u30fc\u30c8\u3092\u884c\u3044\u305f\u3044\u3002\u4ee5\u4e0b\u306b\u5177\u4f53\u4f8b\u3068\u5bfe\u5fdc\u65b9\u6cd5\u3092\u30e1\u30e2\u3002<\/p>\n<p>&nbsp;<\/p>\n<h2>\u5177\u4f53\u4f8b<\/h2>\n<p>\u98f2\u98df\u5e97\u8217\u30c6\u30fc\u30d6\u30eb\u306f<a href=\"https:\/\/taitan916.info\/blog\/archives\/3253\">\u524d\u56de\u306e\u8a18\u4e8b<\/a>\u3068\u540c\u69d8\u3067\u65b0\u305f\u306b\u4f11\u696d\u65e5\u30c6\u30fc\u30d6\u30eb\u3068\u3044\u3046\u306e\u3092\u8ffd\u52a0\u3057\u305f\u3002\u5177\u4f53\u7684\u306b\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u5f62\u3068\u306a\u308b\u3002<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">mysql&gt; show columns from shop;\r\n+------------+------------------+------+-----+---------+----------------+\r\n| Field      | Type             | Null | Key | Default | Extra          |\r\n+------------+------------------+------+-----+---------+----------------+\r\n| id         | int(11)          | NO   | PRI | NULL    | auto_increment |\r\n| name       | varchar(255)     | NO   |     | NULL    |                |\r\n| start_time | time             | YES  |     | NULL    |                |\r\n| end_time   | time             | YES  |     | NULL    |                |\r\n| last_order | int(11) unsigned | NO   |     | NULL    |                |\r\n+------------+------------------+------+-----+---------+----------------+\r\n\r\nmysql&gt; select * from shop;\r\n+----+-----------+------------+----------+------------+\r\n| id | name      | start_time | end_time | last_order |\r\n+----+-----------+------------+----------+------------+\r\n|  1 | \u725b\u89d2      | 10:00:00   | 22:00:00 |         30 |\r\n|  2 | \u53d9\u3005\u82d1    | 09:00:00   | 19:00:00 |         15 |\r\n+----+-----------+------------+----------+------------+\r\n\r\nmysql&gt; show columns from holiday;\r\n+---------+------------------+------+-----+---------+----------------+\r\n| Field   | Type             | Null | Key | Default | Extra          |\r\n+---------+------------------+------+-----+---------+----------------+\r\n| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |\r\n| shop_id | int(10) unsigned | NO   |     | NULL    |                |\r\n| holiday | date             | YES  |     | NULL    |                |\r\n+---------+------------------+------+-----+---------+----------------+\r\n\r\nmysql&gt; select * from holiday;\r\n+----+---------+------------+\r\n| id | shop_id | holiday    |\r\n+----+---------+------------+\r\n|  1 |       1 | 2020-09-12 |\r\n|  2 |       1 | 2020-09-20 |\r\n+----+---------+------------+<\/pre>\n<p>shop\u30c6\u30fc\u30d6\u30eb\u306eid\u3068holiday\u30c6\u30fc\u30d6\u30eb\u306eshop_id\u304c\u5bfe\u306b\u306a\u3063\u3066\u3044\u308b\u3002<\/p>\n<p>&nbsp;<\/p>\n<h2>\u5bfe\u5fdc\u65b9\u6cd5<\/h2>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">\/\/MySQL5.1\u306e\u5834\u5408\r\nSELECT \r\n    *, \r\n    SEC_TO_TIME (\r\n        TIME_TO_SEC(s.end_time) - ( s.last_order * 60)\r\n    ) AS last_time,\r\n    IF(\r\n        start_time &lt;= now() &amp;&amp;\r\n        SEC_TO_TIME (\r\n            TIME_TO_SEC(s.end_time) - ( s.last_order * 60 )\r\n        ) &gt;= now() &amp;&amp; \r\n        (\r\n            GROUP_CONCAT(h.holiday) NOT LIKE \"%2020-09-12%\" || GROUP_CONCAT(h.holiday) IS NULL\r\n        ), 1, 0\r\n    ) AS open_flg,\r\n    GROUP_CONCAT(h.holiday) AS holiday_list, \r\n    IF( GROUP_CONCAT(h.holiday) LIKE \"%2020-09-12%\", 1, 0) AS holiday_flg \r\nFROM shop AS s \r\nLEFT JOIN holiday AS h \r\nON s.id = h.shop_id\r\nGROUP BY s.id;\r\n\r\n\/\/MySQL5.7\u306e\u5834\u5408\r\nSELECT \r\n    *, \r\n    ( end_time - INTERVAL last_order MINUTE ) AS last_time, \r\n    IF(\r\n        start_time &lt;= now() &amp;&amp; \r\n        ( end_time - INTERVAL last_order MINUTE ) &gt;= now() &amp;&amp; \r\n        (\r\n            GROUP_CONCAT(h.holiday) NOT LIKE \"%2020-09-12%\" || GROUP_CONCAT(h.holiday) IS NULL\r\n        ), 1, 0\r\n    ) AS open_flg,\r\n    GROUP_CONCAT(h.holiday) AS holiday_list, \r\n    IF( GROUP_CONCAT(h.holiday) LIKE \"%2020-09-12%\", 1, 0) AS holiday_flg \r\nFROM shop AS s \r\nLEFT JOIN holiday AS h \r\nON s.id = h.shop_id\r\nGROUP BY s.id;<\/pre>\n<p>\u300c2020-09-12\u300d\u306e\u90e8\u5206\u306f\u9069\u5b9c\u7f6e\u304d\u63db\u3048\u308b\u3002PHP\u306e\u5834\u5408\u3060\u3068date('Y-m-d')\u3068\u304b\u306b\u306a\u308b\u3002<\/p>\n<h3>1\u5bfe\u591a\u306e\u7d50\u5408<\/h3>\n<p>1\u5bfe\u591a\u306e\u7d50\u5408\u3060\u304cLEFT JOIN\u3057\u3064\u3064GROUP_CONCAT(h.holiday)\u3068GROUP BY s.id\u304c\u5fc5\u8981\u306b\u306a\u308b\u3002LEFT JOIN\u3060\u3051\u3060\u3068\u540c\u4e00\u5e97\u8217\u306b\u8907\u6570\u4f11\u696d\u65e5\u3092\u767b\u9332\u3057\u3066\u3044\u305f\u5834\u5408\u3001\u767b\u9332\u6570\u5206\u8868\u793a\u3055\u308c\u3066\u3057\u307e\u3046\u3002<\/p>\n<p>LEFT JOIN\u3068GROUP_CONCAT\u306e2\u3064\u3060\u3068\u4f11\u696d\u65e5\u304c\u767b\u9332\u3055\u308c\u3066\u3044\u306a\u3044\u5e97\u8217\u306f\u8868\u793a\u3055\u308c\u306a\u3044\u3002<\/p>\n<p>LEFT JOIN\u3057\u3064\u3064GROUP_CONCAT(h.holiday)\u3068GROUP BY s.id\u3092\u6307\u5b9a\u3059\u308b\u3053\u3068\u3067\u8907\u6570\u4f11\u696d\u65e5\u3092\u767b\u9332\u3057\u3066\u3044\u3066\u30821\u5217\u3067\u8868\u793a\u3055\u308c\u308b\u4e0a\u3001\u4f11\u696d\u65e5\u304c\u672a\u767b\u9332\u306e\u5e97\u8217\u3082\u8868\u793a\u3055\u308c\u308b\u3002<\/p>\n<h3>open_flg\u306e\u90e8\u5206<\/h3>\n<p>\u9577\u3081\u306eif\u6587\u306e\u3068\u3053\u308d\u3060\u304c\u300c\u55b6\u696d\u6642\u9593\u5185(\u30e9\u30b9\u30c8\u30aa\u30fc\u30c0\u30fc\u6642\u9593\u306e\u6e1b\u7b97\u542b\u3080)\u300d\u3068\u3044\u3046\u5224\u5225\u3068\u300c\u4f11\u696d\u65e5\u3067\u306f\u306a\u3044\u300d\u3082\u3057\u304f\u306f\u300c\u4f11\u696d\u65e5\u81ea\u4f53\u304c\u767b\u9332\u3055\u308c\u3066\u3044\u306a\u3044\u300d\u306e\u3044\u305a\u308c\u304b\u306e\u5834\u5408\u306b\u30d5\u30e9\u30b0\u3092\u7acb\u3066\u3066\u3044\u308b\u3002\u30d5\u30e9\u30b0\u304c\u7acb\u3063\u3066\u3044\u308b\u5834\u5408\u306f\u55b6\u696d\u4e2d\u3001\u7acb\u3063\u3066\u3044\u306a\u3044\u5834\u5408\u306f\u55b6\u696d\u6642\u9593\u5916\u3082\u3057\u304f\u306f\u4f11\u696d\u65e5\u3068\u3044\u3046\u5224\u5b9a\u3002<\/p>\n<p>\u300c\u55b6\u696d\u6642\u9593\u5185(\u30e9\u30b9\u30c8\u30aa\u30fc\u30c0\u30fc\u6642\u9593\u306e\u6e1b\u7b97\u542b\u3080)\u300d\u306b\u3064\u3044\u3066\u306f<a href=\"https:\/\/taitan916.info\/blog\/archives\/3253#time\">\u524d\u56de\u306e\u8a18\u4e8b<\/a>\u3092\u53c2\u8003\u3002<\/p>\n<p>\u300c\u4f11\u696d\u65e5\u3067\u306f\u306a\u3044\u300d\u306f\u300cGROUP_CONCAT(h.holiday) NOT LIKE \"%2020-09-12%\"\u300d\u306e\u90e8\u5206\u306b\u306a\u308b\u3002\u4f11\u696d\u65e5\u81ea\u4f53\u306f\u767b\u9332\u3057\u3066\u3044\u308b\u3082\u306e\u306e\u3001\u691c\u7d22\u65e5(2020-09-12)\u3092\u542b\u307e\u306a\u3044\u5834\u5408\u306ftrue\u5224\u5b9a\u3002<\/p>\n<p>\u4e0a\u8a18\u3060\u3051\u3060\u3068\u4f11\u696d\u65e5\u672a\u767b\u9332\u5e97\u8217\u306eNULL\u306e\u95a2\u4fc2\u3067\u671b\u3093\u3060\u6319\u52d5\u306b\u306a\u3089\u306a\u3044\u306e\u3067\u300c\u4f11\u696d\u65e5\u81ea\u4f53\u304c\u767b\u9332\u3055\u308c\u3066\u3044\u306a\u3044 = \u4f11\u696d\u65e5\u3067\u306f\u306a\u3044\u300d\u3068\u3044\u3046\u5224\u5b9a\u3092\u300cGROUP_CONCAT(h.holiday) IS NULL\u300d\u90e8\u5206\u3067\u884c\u3063\u3066\u3044\u308b\u3002<\/p>\n<h3>holiday_flg\u306e\u90e8\u5206<\/h3>\n<p>\u55b6\u696d\u6642\u9593\u4e91\u3005\u306b\u95a2\u308f\u3089\u305a\u4f11\u696d\u65e5\u306b\u691c\u7d22\u65e5(2020-09-12)\u304c\u542b\u307e\u308c\u3066\u3044\u308b\u5834\u5408\u306f\u30d5\u30e9\u30b0\u3092\u7acb\u3066\u3066\u3044\u308b\u3002open_flg\u304c\u3042\u308c\u3070\u3044\u3089\u306a\u3044\u3093\u3058\u3083\u306a\u3044\u304b\u3068\u601d\u3063\u305f\u304c\u3001open_flg\u3060\u3051\u3060\u3068\u4f11\u696d\u65e5\u306a\u306e\u304b\u55b6\u696d\u6642\u9593\u5916\u306a\u306e\u304b\u5206\u304b\u3089\u306a\u3044\u3002\u30c7\u30fc\u30bf\u53d6\u5f97\u5f8c\u306bPHP\u5074\u3067\u300choliday_flg\u304c1\u306e\u5834\u5408\u306f\u4f11\u696d\u65e5 &gt; open_flg\u304c1\u306e\u5834\u5408\u306f\u55b6\u696d\u4e2d &gt; \u305d\u306e\u4ed6\u306f\u53d7\u4ed8\u6642\u9593\u5916\u300d\u306b\u3059\u308c\u3070\u5224\u5225\u3067\u304d\u308b\u3002<\/p>\n<h3>\u6761\u4ef6\u691c\u7d22\u3068\u30bd\u30fc\u30c8<\/h3>\n<p>\u6761\u4ef6\u691c\u7d22\u306fHAVING\u3092\u30bd\u30fc\u30c8\u306fORDER BY\u3067\u6307\u5b9a\u3067\u304d\u308b\u3002\u8a18\u8ff0\u306e\u9806\u756a\u3067\u5c11\u3005\u8a70\u307e\u3063\u305f\u304cGROUP BY\u306e\u5f8c\u306bHAVING\u3001HAVING\u306e\u5f8c\u306bORDER BY\u3067\u6307\u5b9a\u3067\u304d\u305f\u3002<\/p>\n<p>&nbsp;<\/p>\n<h2>\u55b6\u696d\u4e2d\u306e\u30c7\u30fc\u30bf\u306e\u307f\u62bd\u51fa\u3057\u305f\u3044\u5834\u5408<\/h2>\n<p>\u300c\u55b6\u696d\u7d42\u4e86\u6642\u9593\u304b\u3089\u30e9\u30b9\u30c8\u30aa\u30fc\u30c0\u30fc\u306e\u6642\u9593\u3092\u6e1b\u7b97\u300d\u7b49\u306e\u3084\u3084\u3053\u3057\u3044\u5224\u5b9a\u306f\u3044\u3089\u305a\u3001\u55b6\u696d\u6642\u9593\u5185\u3067\u5c1a\u4e14\u3064\u4f11\u696d\u65e5\u3058\u3083\u306a\u3044\u30c7\u30fc\u30bf\u3092\u62bd\u51fa\u3057\u305f\u3044\u5834\u5408\u306f\u30b5\u30d6\u30af\u30a8\u30ea\u306e\u65b9\u304c\u30b7\u30f3\u30d7\u30eb\u306b\u8a18\u8ff0\u3067\u304d\u308b\u3002<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">SELECT *\r\nFROM shop\r\nWHERE start_time &lt;= now() \r\nAND end_time &gt;= now()\r\nAND NOT EXISTS (\r\n    SELECT * \r\n    FROM holiday\r\n    WHERE holiday = \"2020-09-12\" \r\n    AND shop.id = holiday.shop_id\r\n);<\/pre>\n<p>NOT EXISTS\uff5e\u306e\u90e8\u5206\u3067\u4f11\u696d\u65e5\u306b\u691c\u7d22\u65e5(2020-09-12)\u304c\u542b\u307e\u308c\u3066\u3044\u308b\u5e97\u8217\u3092\u9664\u5916\u3057\u3066\u3044\u308b\u3002<\/p>\n<p>&nbsp;<\/p>\n<h2>\u6240\u611f<\/h2>\n<p>\u4eca\u56de\u306e\u6848\u4ef6\u306f\u30bd\u30fc\u30c8\u3084\u6761\u4ef6\u691c\u7d22\u304c\u8907\u6570\u3042\u3063\u305f\u70ba\u3001\u7d50\u5408\u3057\u3066if\u6587\u3067\u8272\u3005\u30c7\u30fc\u30bf\u6574\u5f62\u3057\u3066\u3068\u3044\u3046\u3084\u3084\u3053\u3057\u3044\u5f62\u3092\u53d6\u3063\u305f\u304c\u3001\u30b5\u30d6\u30af\u30a8\u30ea\u306e\u65b9\u304c\u4eca\u5f8c\u4f7f\u3046\u6a5f\u4f1a\u304c\u591a\u305d\u3046\u306b\u601d\u308f\u308c\u308b\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL\u3067\u98f2\u98df\u5e97\u8217\u30c6\u30fc\u30d6\u30eb\u3068\u4f11\u696d\u65e5\u30c6\u30fc\u30d6\u30eb\u304c\u3042\u308a\u30011\u5e97\u8217\u306b\u5bfe\u3057\u3066\u8907\u6570\u884c\u306e\u4f11\u696d\u65e5 ... <\/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-3255","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\/3255","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=3255"}],"version-history":[{"count":0,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/posts\/3255\/revisions"}],"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=3255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/categories?post=3255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/taitan916.info\/blog\/wp-json\/wp\/v2\/tags?post=3255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}