{"id":106,"date":"2025-02-05T19:26:43","date_gmt":"2025-02-05T11:26:43","guid":{"rendered":"https:\/\/www.handytoo.com\/?p=106"},"modified":"2025-02-05T19:26:43","modified_gmt":"2025-02-05T11:26:43","slug":"mysql%e6%97%b6%e9%97%b4%e6%9d%a1%e4%bb%b6%e6%9f%a5%e8%af%a2sql%e6%80%bb%e7%bb%93","status":"publish","type":"post","link":"https:\/\/www.handytoo.com\/?p=106","title":{"rendered":"Mysql\u65f6\u95f4\u6761\u4ef6\u67e5\u8be2SQL\u603b\u7ed3"},"content":{"rendered":"\n<p>\u4eca\u5929<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM \u8868\u540d WHERE TO_DAYS(\u65f6\u95f4\u5b57\u6bb5\u540d) = TO_DAYS(now());\n<\/pre>\n\n\n\n<p>\u6628\u5929<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM \u8868\u540d WHERE TO_DAYS( NOW( ) ) - TO_DAYS( \u65f6\u95f4\u5b57\u6bb5\u540d) &lt;= 1\n<\/pre>\n\n\n\n<p>\u8fd17\u5929<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM \u8868\u540d WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) &lt;= DATE(\u65f6\u95f4\u5b57\u6bb5\u540d)\n<\/pre>\n\n\n\n<p>\u8fd130\u5929<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM \u8868\u540d WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) &lt;= DATE(\u65f6\u95f4\u5b57\u6bb5\u540d)\n<\/pre>\n\n\n\n<p>\u672c\u6708<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM \u8868\u540d WHERE DATE_FORMAT( \u65f6\u95f4\u5b57\u6bb5\u540d, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )\n<\/pre>\n\n\n\n<p>\u4e0a\u4e00\u6708<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM \u8868\u540d WHERE PERIOD_DIFF( DATE_FORMAT( now( ) , '%Y%m' ) , DATE_FORMAT( \u65f6\u95f4\u5b57\u6bb5\u540d, '%Y%m' ) ) =1\n<\/pre>\n\n\n\n<p>\u67e5\u8be2\u672c\u5b63\u5ea6\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM `ht_invoice_information` WHERE QUARTER(create_date)=QUARTER(now());\n<\/pre>\n\n\n\n<p>\u67e5\u8be2\u4e0a\u5b63\u5ea6\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM `ht_invoice_information` WHERE QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));\n<\/pre>\n\n\n\n<p>\u67e5\u8be2\u672c\u5e74\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM `ht_invoice_information` WHERE YEAR(create_date)=YEAR(NOW());\n<\/pre>\n\n\n\n<p>\u67e5\u8be2\u4e0a\u5e74\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT  * FROM `ht_invoice_information` WHERE year(create_date)=year(date_sub(now(),interval 1 year));\n<\/pre>\n\n\n\n<p>\u67e5\u8be2\u5f53\u524d\u8fd9\u5468\u7684\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT name,submittime FROM enterprise WHERE YEARWEEK(DATE_FORMAT(submittime,'%Y-%m-%d')) = YEARWEEK(now());\n<\/pre>\n\n\n\n<p>\u67e5\u8be2\u4e0a\u5468\u7684\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT name,submittime FROM enterprise WHERE YEARWEEK(DATE_FORMAT(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;\n<\/pre>\n\n\n\n<p>\u67e5\u8be2\u4e0a\u4e2a\u6708\u7684\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT name,submittime FROM enterprise WHERE DATE_FORMAT(submittime,'%Y-%m')=DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')\nSELECT * FROM user WHERE DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')&nbsp;; \nSELECT * FROM user WHERE WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) \nSELECT * FROM user WHERE MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) \nSELECT * FROM user WHERE YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) \nSELECT * FROM user WHERE pudate BETWEEN \u4e0a\u6708\u6700\u540e\u4e00\u5929  and \u4e0b\u6708\u7b2c\u4e00\u5929 \n<\/pre>\n\n\n\n<p>\u67e5\u8be2\u5f53\u524d\u6708\u4efd\u7684\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT name,submittime FROM enterprise WHERE DATE_FORMAT(submittime,'%Y-%m')=DATE_FORMAT(now(),'%Y-%m')\n<\/pre>\n\n\n\n<p>\u67e5\u8be2\u8ddd\u79bb\u5f53\u524d\u73b0\u57286\u4e2a\u6708\u7684\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT name,submittime FROM enterprise WHERE submittime between date_sub(now(),interval 6 month) and now();<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u4eca\u5929 SELECT * FROM \u8868\u540d WHERE TO_DAYS(\u65f6\u95f4\u5b57\u6bb5\u540d) = TO_DAYS(now( [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[29],"class_list":["post-106","post","type-post","status-publish","format-standard","hentry","category-database","tag-mysql"],"_links":{"self":[{"href":"https:\/\/www.handytoo.com\/index.php?rest_route=\/wp\/v2\/posts\/106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.handytoo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.handytoo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.handytoo.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.handytoo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=106"}],"version-history":[{"count":1,"href":"https:\/\/www.handytoo.com\/index.php?rest_route=\/wp\/v2\/posts\/106\/revisions"}],"predecessor-version":[{"id":107,"href":"https:\/\/www.handytoo.com\/index.php?rest_route=\/wp\/v2\/posts\/106\/revisions\/107"}],"wp:attachment":[{"href":"https:\/\/www.handytoo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.handytoo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.handytoo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}