\u6b64\u67e5\u8be2\u5c06\u4e3a\u60a8\u63d0\u4f9b\u6bcf\u4e2a\u6708\u95f4\u9694\u5185\u7684\u9500\u552e\u53d1\u751f\u6b21\u6570\uff0c\u5141\u8bb8\u60a8\u521b\u5efa\u76f4\u65b9\u56fe\u4ee5\u53ef\u89c6\u5316\u9500\u552e\u968f\u65f6\u95f4\u7684\u5206\u5e03\u60c5\u51b5\u3002<\/p>\n
\u5982\u4f55\u586b\u8865\u76f4\u65b9\u56fe\u67e5\u8be2\u4e2d\u7684\u7a7a\u767d
\u8fd0\u884c\u6b64\u67e5\u8be2\uff0c\u60a8\u53ef\u80fd\u4f1a\u5728\u7ed3\u679c\u96c6\u4e2d\u770b\u5230\u4e00\u4e9b\u7f3a\u5931\u7684\u6708\u4efd\uff0c\u53ef\u80fd\u662f\u56e0\u4e3a\u5728\u67d0\u4e9b\u6708\u4efd\u6ca1\u6709\u9500\u552e\uff0c\u56e0\u6b64 group by \u51fd\u6570\u65e0\u6cd5\u751f\u6210\u8fd9\u4e9b\u95f4\u9694\u7684\u6570\u636e\u3002 <\/p>\n
\u5982\u4f55\u8c03\u6574\u67e5\u8be2\u4ee5\u4f7f\u7528\u9ed8\u8ba4\u96f6\u503c\u6765\u586b\u8865\u8fd9\u4e9b\u7a7a\u767d\uff1f\u5426\u5219\u76f4\u65b9\u56fe\u4ecd\u7136\u4e0d\u5b8c\u6574\u3002<\/p>\n
\u4e3a\u4e86\u586b\u8865\u7ed3\u679c\u96c6\u4e2d\u7684\u7a7a\u767d\u5e76\u786e\u4fdd\u4ee3\u8868\u6240\u6709\u6708\u4efd\uff0c\u5373\u4f7f\u67d0\u4e9b\u6708\u4efd\u6ca1\u6709\u9500\u552e\uff0c\u60a8\u4e5f\u53ef\u4ee5\u4f7f\u7528\u9012\u5f52\u516c\u7528\u8868\u8868\u8fbe\u5f0f (cte) \u52a8\u6001\u751f\u6210\u6240\u9009\u65e5\u5386\u95f4\u9694\u7684\u6708\u4efd\u3002 <\/p>\n
\u5b83\u751f\u6210\u6db5\u76d6\u6240\u9700\u65f6\u95f4\u8303\u56f4\u7684\u65e5\u671f\u5e8f\u5217\u3002\u7136\u540e\uff0c\u60a8\u53ef\u4ee5\u5c06\u6b64\u65e5\u671f\u5e8f\u5217\u4e0e\u60a8\u7684\u9500\u552e\u6570\u636e\u5de6\u8fde\u63a5\uff0c\u4ee5\u5c06\u6240\u6709\u6708\u4efd\u5305\u542b\u5728\u7ed3\u679c\u96c6\u4e2d\u3002<\/p>\n
with recursive daterange as (\n select \n date_format(date_sub(date_format(now(), '%y-%m-01'), interval 11 month), '%y-%m-01') as min_date,\n date_format(now(), '%y-%m-01') as max_date\n union all\n select \n date_format(date_add(min_date, interval 1 month), '%y-%m-01'),\n max_date\n from \n daterange\n where \n date_add(min_date, interval 1 month) <= max_date\n)\nselect \n date_format(daterange.min_date, '%y-%m') as sales_month,\n count(sales.sale_date) as count\nfrom \n daterange\nleft join \n sales on date_format(daterange.min_date, '%y-%m') = date_format(sales.sale_date, '%y-%m')\ngroup by \n sales_month\norder by \n sales_month;\n<\/pre>\ndaterange cte\uff08\u901a\u7528\u8868\u8868\u8fbe\u5f0f\uff09\u9012\u5f52\u5730\u751f\u6210\u4e00\u7cfb\u5217\u6708\u4efd\uff0c\u6db5\u76d6\u9500\u552e\u8868\u4e2d\u6700\u5c0f\u548c\u6700\u5927\u9500\u552e\u65e5\u671f\u4e4b\u95f4\u7684\u65f6\u95f4\u8303\u56f4\u3002<\/p>\n
\u6b64\u67e5\u8be2\u6839\u636e\u9500\u552e\u8868\u4e2d\u7684\u6700\u5c0f\u548c\u6700\u5927\u9500\u552e\u65e5\u671f\u52a8\u6001\u751f\u6210\u6240\u9009\u65e5\u5386\u95f4\u9694\u7684\u6708\u4efd\uff0c\u786e\u4fdd\u6240\u6709\u6708\u4efd\u90fd\u51fa\u73b0\u5728\u7ed3\u679c\u96c6\u4e2d\u3002<\/p>\n
\u7528\u4ee3\u7801\u586b\u8865\u7a7a\u767d <\/h2>\n
sql \u89e3\u51b3\u65b9\u6848\u5bf9\u4e8e\u5f00\u53d1\u4eba\u5458\u6765\u8bf4\u53ef\u80fd\u4e0d\u592a\u8212\u670d\uff0c\u800c\u4e14\u9700\u8981\u66f4\u591a\u7684\u81ea\u5b9a\u4e49\u53ef\u80fd\u4f1a\u5bfc\u81f4\u66f4\u559c\u6b22\u57fa\u4e8e\u4ee3\u7801\u7684\u89e3\u51b3\u65b9\u6848\u3002<\/p>\n
\u5728\u8fd9\u79cd\u60c5\u51b5\u4e0b\uff0c\u60a8\u53ef\u4ee5\u901a\u8fc7\u4e09\u4e2a\u7b80\u5355\u7684\u6b65\u9aa4\u83b7\u5f97\u76f8\u540c\u7684\u7ed3\u679c\uff1a<\/p>\n
- \n
- \u7528\u4f60\u611f\u5174\u8da3\u7684\u65f6\u95f4\u95f4\u9694\u521b\u5efa\u6570\u7ec4\uff1b<\/li>\n
- \u83b7\u53d6group by\u67e5\u8be2\u7ed3\u679c\uff1b<\/li>\n
- \u5408\u5e76\u5b83\u4eec\u3002<\/li>\n<\/ul>\n
\u8fd9\u662f\u4f7f\u7528 laravel \u548c carbon \u5e93\u7684\u4ee3\u7801\u7247\u6bb5\uff1a<\/p>\n
$dates = [];\n\n\/\/ Create the array with the time interval of your interests\nfor(\n $day = now()->subDays(31);\n $day->startOfDay()->lte(now());\n $day->addDay()\n) {\n $dates[] = [\n 'day' => $day->format('Y-m-d'),\n 'total' => 0,\n ];\n}\n\n\/\/ Get the result from the GROUP BY query\n$sales = $product->sales()->select(DB::raw('DATE(sale_at) as day, CAST(SUM(qty) AS UNSIGNED) as total'))\n ->where('sale_at', '>=', now()->subDays(31))\n ->groupBy('day')\n ->get();\n\n\/\/ Merge them\nreturn array_map(function ($date) use ($sales) {\n foreach ($sales as $sale) {\n if ($date['day'] === $sale['day']) {\n return $sale;\n }\n }\n}, $dates);\n<\/pre>\n\u5982\u679c\u60a8\u6709\u5174\u8da3\u9605\u8bfb\u66f4\u591a\u6709\u5173\u6570\u636e\u5e93\u7684\u6587\u7ae0\uff0c\u60a8\u53ef\u4ee5\u67e5\u770b\u4ee5\u4e0b\u6587\u7ae0\uff1a<\/p>\n
- \n
- \u5982\u4f55\u6269\u5c55 sql \u6570\u636e\u5e93<\/li>\n
- \u5982\u4f55\u590d\u5236 mysql \u8868<\/li>\n
- \u5df2\u89e3\u51b3 – \u8fdd\u53cd\u5b8c\u6574\u6027\u7ea6\u675f<\/li>\n
- \u4f7f\u7528 laravel \u9884\u52a0\u8f7d\u6bcf\u5929\u8282\u7701 120 \u4e07\u6b21\u67e5\u8be2<\/li>\n
- \u5982\u4f55\u901a\u8fc7\u667a\u80fd sql \u67e5\u8be2\u52a0\u901f\u5e94\u7528\u7a0b\u5e8f\u6027\u80fd<\/li>\n
- \u4f7f\u60a8\u7684\u5e94\u7528\u7a0b\u5e8f\u53ef\u6269\u5c55\uff0c\u4f18\u5316 orm \u6027\u80fd<\/li>\n
- \u5df2\u89e3\u51b3 \u2013 \u4f7f\u7528 laravel \u961f\u5217\u548c\u4f5c\u4e1a\u8d85\u51fa mysql \u9501\u7b49\u5f85\u8d85\u65f6<\/li>\n<\/ul>\n
\u514d\u8d39\u76d1\u63a7\u60a8\u7684 php \u5e94\u7528\u7a0b\u5e8f <\/h2>\n
inspector \u662f\u4e00\u6b3e\u4e13\u4e3a\u8f6f\u4ef6\u5f00\u53d1\u4eba\u5458\u8bbe\u8ba1\u7684\u4ee3\u7801\u6267\u884c\u76d1\u63a7\u5de5\u5177\u3002\u60a8\u65e0\u9700\u5728\u4e91\u57fa\u7840\u8bbe\u65bd\u4e0a\u5b89\u88c5\u4efb\u4f55\u5185\u5bb9\uff0c\u53ea\u9700\u5b89\u88c5 laravel \u8f6f\u4ef6\u5305\u5373\u53ef\u5f00\u59cb\u4f7f\u7528\u3002<\/p>\n
inspector \u8d85\u7ea7\u6613\u4e8e\u4f7f\u7528\uff0c\u5e76\u4e14\u9700\u8981\u96f6\u914d\u7f6e\u3002<\/p>\n
\u5982\u679c\u60a8\u6b63\u5728\u5bfb\u627e http \u76d1\u63a7\u3001\u67e5\u8be2\u89c1\u89e3\u4ee5\u53ca\u5c06\u8b66\u62a5\u548c\u901a\u77e5\u8f6c\u53d1\u5230\u60a8\u9996\u9009\u6d88\u606f\u4f20\u9012\u73af\u5883\u7684\u529f\u80fd\uff0c\u8bf7\u514d\u8d39\u5c1d\u8bd5 inspector\u3002 [\u6ce8\u518c\u60a8\u7684\u5e10\u6237+(https:\/\/app.inspector.dev\/register).<\/p>\n
\u6216\u5728\u7f51\u7ad9\u4e0a\u4e86\u89e3\u66f4\u591a\uff1ahttps:\/\/inspector.dev<\/p>\n
![](\"https:\/\/www.17golang.com\/uploads\/20240827\/172472691866cd3e86b60e8.jpg\")
<\/p>\n\u4ee5\u4e0a\u5c31\u662f\u672c\u6587\u7684\u5168\u90e8\u5185\u5bb9\u4e86\uff0c\u662f\u5426\u6709\u987a\u5229\u5e2e\u52a9\u4f60\u89e3\u51b3\u95ee\u9898\uff1f\u82e5\u662f\u80fd\u7ed9\u4f60\u5e26\u6765\u5b66\u4e60\u4e0a\u7684\u5e2e\u52a9\uff0c\u8bf7\u5927\u5bb6\u591a\u591a\u652f\u6301\u4e3b\u673a\u5b9d\u8d1d\uff01\u66f4\u591a\u5173\u4e8e\u6570\u636e\u5e93\u7684\u76f8\u5173\u77e5\u8bc6\uff0c\u4e5f\u53ef\u5173\u6ce8\u4e3b\u673a\u5b9d\u8d1d\u516c\u4f17\u53f7\u3002<\/p>\n
\u7248\u672c\u58f0\u660e \u672c\u6587\u8f6c\u8f7d\u4e8e\uff1adev.to \u5982\u6709\u4fb5\u72af\uff0c\u8bf7\u8054\u7cfb \u5220\u9664 <\/p>\n
- \n
- \n <\/dt>\n<\/dl>\n","protected":false},"excerpt":{"rendered":"
\u4f7f\u7528 MySQL \u521b\u5efa\u76f4\u65b9\u56fe \u2013...<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[101],"tags":[],"class_list":["post-200130","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts\/200130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/comments?post=200130"}],"version-history":[{"count":0,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts\/200130\/revisions"}],"wp:attachment":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/media?parent=200130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/categories?post=200130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/tags?post=200130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- \n <\/dt>\n<\/dl>\n","protected":false},"excerpt":{"rendered":"