SQL 将 datepart() 与 group by 命令一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13957170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using datepart() with the group by command
提问by Zac Cramer
I am beating my head against something that is, I'm sure, very obvious -
我正在用头撞到一些东西，我敢肯定，非常明显 -
I have a bit of SQL code designed to sum the total selling price of each invoice in my store and then organize it by Month.
我有一些 SQL 代码，旨在对我商店中每张发票的总售价求和，然后按月进行组织。
select SUM(totalsellingprice) from dbo.tblServiceOrders
where datepart(MONTH,dbo.tblServiceOrders.datereceived) =12
As far as I understand it, that should return the sum of all the totatlsellingprice from month 12 (December). Currently, this query returns
据我了解，这应该返回从第 12 个月（12 月）开始的所有总销售价格的总和。目前，此查询返回
135998.92
However, if I then try to put that into a group by to get it to spit it out for all months, the number changes.
但是，如果我然后尝试将其放入一个组中以使其在所有月份中都将其吐出，则数字会发生变化。
select SUM(totalsellingprice) from dbo.tblServiceOrders
group by datepart(MONTH,dbo.tblServiceOrders.datereceived)
And I get this table -
我得到了这张桌子——
1 - 110567.70
2 - 60059.59
3 - 135998.92
4 - 63089.22
5 - 102287.01
6 - 71088.68
7 - 149102.10
8 - 67722.65
9 - 67122.45
10 - 64234.82
11 - 7542.05
12 - 130461.10
There are 12 rows, which sounds good to me (12 months in a year) but the last row is 130461.
有 12 行，这对我来说听起来不错（一年中有 12 个月），但最后一行是 130461。
How is it possible that row 12 from the second search does not equal what I did in the first search? I feel like I'm missing something obvious but I can't for the life of me figure out what.
第二次搜索的第 12 行怎么可能不等于我在第一次搜索中所做的？我觉得我错过了一些明显的东西，但我一生都无法弄清楚是什么。
Any and all help will be much appreciated!
任何和所有的帮助将不胜感激！
回答by Francis P
I got it:
我知道了：
Your query is very confusing since it does not include the MONTH column:
您的查询非常混乱，因为它不包括 MONTH 列：
If you would have done that, you would have realized your query is not ordered by MONTH and so, the MONTH 12 is returned as the 3rd row of your query.
如果您这样做，您就会意识到您的查询不是按 MONTH 排序的，因此，MONTH 12 将作为查询的第 3 行返回。
;)
;)
select SUM(totalsellingprice) from dbo.tblServiceOrders
group by datepart(MONTH,dbo.tblServiceOrders.datereceived)
order by datepart(MONTH,dbo.tblServiceOrders.datereceived)
And please, don't refer to the row index to choose which month is related to which sum. And should be a good idea to also discriminate the year (if you need to).
并且请不要参考行索引来选择哪个月与哪个总和相关。并且还应该区分年份（如果需要）是个好主意。
回答by Charles Bretana
Run this and see what it does...
运行它，看看它做了什么......
select dateadd(month, datediff(month, 0, datereceived), 0),
Sum(totalsellingprice)
from dbo.tblServiceOrders
group by dateadd(month, datediff(month, 0, datereceived), 0)
