{"id":702,"date":"2018-06-24T22:15:44","date_gmt":"2018-06-24T14:15:44","guid":{"rendered":"https:\/\/blog.freesilo.com\/?p=702"},"modified":"2019-07-07T10:19:19","modified_gmt":"2019-07-07T02:19:19","slug":"%e4%b8%80%e4%ba%9bsql%e8%af%ad%e5%8f%a5%e7%a4%ba%e4%be%8b","status":"publish","type":"post","link":"https:\/\/freesilo.com\/?p=702","title":{"rendered":"\u4e00\u4e9bSQL\u8bed\u53e5\u793a\u4f8b"},"content":{"rendered":"<p>\u5982:<br \/>\n\u8868:consume_record<br \/>\n\u5b57\u6bb5:consume (money\u7c7b\u578b)<br \/>\ndate (datetime\u7c7b\u578b)<\/p>\n<p>\u8bf7\u95ee\u600e\u4e48\u5199\u56db\u6761sql\u8bed\u53e5\u5206\u522b\u6309\u65e5,\u6309\u5468,\u6309\u6708,\u6309\u5b63\u7edf\u8ba1\u6d88\u8d39\u603b\u91cf.<br \/>\n\u5982:1\u6708 1200\u5143<br \/>\n2\u6708 3400\u5143<br \/>\n3\u6708 2800\u5143<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n--\u6309\u65e5 \r\nselect sum(consume),day(&#x5B;date]) \r\n from consume_record where year(&#x5B;date]) = '2006' \r\ngroup by day(&#x5B;date]) \r\n\r\n--\u6309\u5468quarter \r\nselect sum(consume),datename(week,&#x5B;date]) \r\nfrom consume_record where year(&#x5B;date]) = '2006' \r\ngroup by datename(week,&#x5B;date]) \r\n\r\n--\u6309\u6708 \r\nselect sum(consume),month(&#x5B;date]) \r\nfrom consume_record where year(&#x5B;date]) = '2006' \r\ngroup by month(&#x5B;date]) \r\n\r\n--\u6309\u5b63 \r\nselect sum(consume),datename(quarter,&#x5B;date]) \r\nfrom consume_record where year(&#x5B;date]) = '2006' \r\ngroup by datename(quarter,&#x5B;date]) \r\n\r\n \r\n\r\n--\u6307\u5b9a\u65e5\u671f\u4f60\u5c31\u770b\u4e0a\u9762\u7684\u4f8b\u5b50\u53d8\u901a\u4e0b\u5440\uff0c\u65e0\u975e\u5c31\u662f\u4e00\u4e2a\u805a\u5408\u51fd\u6570\u548cGroup by \r\n\r\nselect &#x5B;date],sum(consume) from consume_record \r\nwhere &#x5B;date] between '2006-06-01' and '2006-07-10' \r\ngroup by &#x5B;date] \r\n<\/pre>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n--2011\u5e74\u6bcf\u6708\u6ce8\u518c\u7684\u4eba\u6570\r\nSELECT CONVERT(CHAR(7), joindate,120), COUNT(*) \r\nFROM member \r\nwhere year(joindate)=2011 \r\nGROUP BY CONVERT(CHAR(7), joindate,120)\r\n\r\n--2011\u5e743\u6708\u6bcf\u5929\u6ce8\u518c\u7684\u4eba\u6570\r\nSELECT CONVERT(CHAR(10), joindate,120), COUNT(*) \r\nFROM member \r\nwhere year(joindate)=2011 and month(joindate)=3 \r\nGROUP BY CONVERT(CHAR(10), joindate,120)\r\n\r\n<\/pre>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n--\u83b7\u53d6\u6bcf\u5bb6\u516c\u53f8\u524d\u4e00\u767e\u6761\u65b0\u95fb\u7684ID\r\nselect a.WebNewsID from WebNews as a\r\nwhere a.WebNewsID in (select top 100 WebNewsID \r\nfrom WebNews where Code=a.Code \r\norder by PageGetTime desc) \r\ngroup by a.WebNewsID,a.Code  order by a.Code\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5982: \u8868:consume_record \u5b57\u6bb5:consume (money\u7c7b\u578b)&#8230;<br \/><a class=\"read-more-button\" href=\"https:\/\/freesilo.com\/?p=702\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-702","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/702","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=702"}],"version-history":[{"count":7,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/702\/revisions"}],"predecessor-version":[{"id":1206,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/702\/revisions\/1206"}],"wp:attachment":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=702"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=702"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=702"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}