{"id":1172,"date":"2024-08-24T11:19:03","date_gmt":"2024-08-24T03:19:03","guid":{"rendered":"http:\/\/www.max-shu.com\/blog\/?p=1172"},"modified":"2024-08-24T11:19:04","modified_gmt":"2024-08-24T03:19:04","slug":"mysql%e6%a0%b9%e6%8d%ae%e6%9f%90%e4%b8%aa%e8%a1%a8%e7%9a%84%e6%97%a5%e6%9c%9f%e8%8c%83%e5%9b%b4%e5%ad%97%e6%ae%b5%e8%bd%ac%e6%8d%a2%e7%94%9f%e6%88%90%e6%af%8f%e5%a4%a9%e4%b8%80%e6%9d%a1%e8%ae%b0","status":"publish","type":"post","link":"http:\/\/www.max-shu.com\/blog\/?p=1172","title":{"rendered":"MySQL\u6839\u636e\u67d0\u4e2a\u8868\u7684\u65e5\u671f\u8303\u56f4\u5b57\u6bb5\u8f6c\u6362\u751f\u6210\u6bcf\u5929\u4e00\u6761\u8bb0\u5f55"},"content":{"rendered":"\n<p><strong>\u751f\u6210\u5355\u72ec\u7684\u65e5\u671f\u8868(\u4ece2015-01-01\u52302042-05-18\u6bcf\u5929\u4e00\u6761\u8bb0\u5f55)\uff1a<\/strong><\/p>\n\n\n\n<p>SELECT ADDDATE(&#8220;2015-01-01&#8221;, INTERVAL numT.num DAY) AS _date FROM<\/p>\n\n\n\n<p>(SELECT<\/p>\n\n\n\n<p>n1.v + n10.v * 10 + n100.v * 100 + n1000.v * 1000 AS num<\/p>\n\n\n\n<p>FROM<\/p>\n\n\n\n<p>(SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n1<\/p>\n\n\n\n<p>CROSS JOIN (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS n10<\/p>\n\n\n\n<p>CROSS JOIN (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS n100<\/p>\n\n\n\n<p>CROSS JOIN (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS n1000<\/p>\n\n\n\n<p>) AS numT;<\/p>\n\n\n\n<p>\u8bf4\u660e\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>numT \u8868\u4e3a\u81ea\u52a8\u751f\u6210\u7684\u4e34\u65f6\u8868\uff0c\u53ea\u6709\u4e00\u4e2anum\u5b57\u6bb5\uff0c\u517110000\u6761\u8bb0\u5f55\uff0c\u8bb0\u5f55\u503c\u4e3a0~9999\u3002<\/li>\n\n\n\n<li>n1, n10, n100, n1000 \u8fd94\u4e2a\u8868\u662f\u4e00\u6837\u7684\uff0c\u53ea\u6709\u4e00\u4e2a\u5b57\u6bb5v\uff0c\u517110\u6761\u8bb0\u5f55\uff0c\u8bb0\u5f55\u503c\u4e3a0~9\u3002<\/li>\n\n\n\n<li>\u56e0\u4e3a\u8fd9\u91cc\u53ea\u751f\u6210\u4e86\u6700\u592710000\u6761num\u8bb0\u5f55\uff0c\u6240\u4ee5\u53ea\u652f\u6301\u6700\u592710000\u5929\uff0c\u8981\u66f4\u5927\u7684\u8bdd\uff0c\u53ef\u4ee5\u518djoin\u589e\u52a0n10000\u7c7b\u4f3c\u8868\uff0c\u5c31\u53ef\u4ee5\u592710\u500d\u4e86\u3002<\/li>\n<\/ul>\n\n\n\n<p><strong>\u6839\u636e\u5176\u4ed6\u8868\u7684\u5f00\u59cb\u7ed3\u675f\u65e5\u671f\uff0c\u751f\u6210\u6bcf\u65e5\u4e00\u6761\u8bb0\u5f55\uff1a<\/strong><\/p>\n\n\n\n<p>SELECT ADDDATE(t.bid_date, INTERVAL numT.num DAY) AS _date, t.* FROM<\/p>\n\n\n\n<p>t_cpe_event_config AS t,<\/p>\n\n\n\n<p>(SELECT<\/p>\n\n\n\n<p>n1.v + n10.v * 10 + n100.v * 100 + n1000.v * 1000 AS num<\/p>\n\n\n\n<p>FROM<\/p>\n\n\n\n<p>(SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n1<\/p>\n\n\n\n<p>CROSS JOIN (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS n10<\/p>\n\n\n\n<p>CROSS JOIN (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS n100<\/p>\n\n\n\n<p>CROSS JOIN (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS n1000<\/p>\n\n\n\n<p>) AS numT<\/p>\n\n\n\n<p>WHERE numT.num BETWEEN 0 AND DATEDIFF(t.bid_date_end, t.bid_date)<\/p>\n\n\n\n<p>AND t._id=908;<\/p>\n\n\n\n<p>\u8bf4\u660e\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>t_cpe_event_config \u8868\u6709\u4e09\u4e2a\u4ee5\u4e0a\u5b57\u6bb5\uff1a id\uff0c\u00a0 bid_date(\u5f00\u59cb\u65e5\u671f)\uff0c\u00a0 bide_date_end(\u7ed3\u675f\u65e5\u671f)\uff0c&#8230;\u3002<\/li>\n\n\n\n<li>numT \u8868\u4e3a\u81ea\u52a8\u751f\u6210\u7684\u4e34\u65f6\u8868\uff0c\u53ea\u6709\u4e00\u4e2anum\u5b57\u6bb5\uff0c\u517110000\u6761\u8bb0\u5f55\uff0c\u8bb0\u5f55\u503c\u4e3a0~9999\u3002<\/li>\n\n\n\n<li>n1, n10, n100, n1000 \u8fd94\u4e2a\u8868\u662f\u4e00\u6837\u7684\uff0c\u53ea\u6709\u4e00\u4e2a\u5b57\u6bb5v\uff0c\u517110\u6761\u8bb0\u5f55\uff0c\u8bb0\u5f55\u503c\u4e3a0~9\u3002<\/li>\n\n\n\n<li>\u56e0\u4e3a\u8fd9\u91cc\u53ea\u751f\u6210\u4e86\u6700\u592710000\u6761num\u8bb0\u5f55\uff0c\u6240\u4ee5\u53ea\u652f\u6301\u6700\u592710000\u5929\uff0c\u8981\u66f4\u5927\u7684\u8bdd\uff0c\u53ef\u4ee5\u518djoin\u589e\u52a0n10000\u7c7b\u4f3c\u8868\uff0c\u5c31\u53ef\u4ee5\u592710\u500d\u4e86\u3002<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u751f\u6210\u5355\u72ec\u7684\u65e5\u671f\u8868(\u4ece2015-01-01\u52302042-05-18\u6bcf\u5929\u4e00\u6761\u8bb0\u5f55)\uff1a SELECT ADDDATE( &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[63],"tags":[365,912,913],"class_list":["post-1172","post","type-post","status-publish","format-standard","hentry","category-63","tag-mysql","tag-912","tag-913"],"views":702,"_links":{"self":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1172"}],"version-history":[{"count":1,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1172\/revisions"}],"predecessor-version":[{"id":1173,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1172\/revisions\/1173"}],"wp:attachment":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1172"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}