MySQL 使用 Sum 和 Case
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19529864/
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
MySQL using Sum and Case
提问by Latex Person
I'm trying to create a GridView with ASP.NET connecting to a MySQL database. The data appears like below.
我正在尝试创建一个带有 ASP.NET 连接到 MySQL 数据库的 GridView。数据如下所示。
BusinessUnit OrderDate Canceled
UnitA 1/15/2013 N
UnitA 10/1/2013 N
UnitB 10/15/2013 N
UnitB 10/22/2013 N
UnitB 10/22/2013 N
Based on the records above, I'd like the result to appear like below
根据上面的记录，我希望结果如下所示
BusinessUnit TodaysOrders ThisMonthsOrders ThisYearsOrders
UnitA 0 1 2
UnitB 2 3 3
My current code is below. It's giving me error (something about DatabaseName.sum does not exist. Check the Function Name Parsing and Resolution' section... )
我当前的代码如下。它给了我错误（关于 DatabaseName.sum 的东西不存在。检查函数名称解析和解析部分......）
Select
SUM (CASE WHEN (OrderDate)=DATE(NOW()) THEN 1 ELSE 0 END) AS TodaysOrders,
SUM (CASE WHEN YEAR(OrderDate) = YEAR(CURDATE()) AND MONTH(OrderDate) = MONTH(CURDATE()) THEN 1 ELSE 0 END) AS ThisMonthsOrders,
SUM (CASE WHEN YEAR(main_order_managers.creation_date) = YEAR(CURDATE()) THEN 1 ELSE 0 END) AS ThisYearsOrders
code continues
代码继续
FROM OrderTable WHERE OrderTable.Canceled. <> 'Y';
Is Sum Case the best use here?
Sum Case 是这里的最佳用途吗？
回答by peterm
The error is caused by the space between function name and parenthesis
错误是由函数名和括号之间的空格引起的
SUM (CASE WHEN ...
^^
Read more Function Name Parsing and Resolution
阅读更多函数名称解析和解析
Try
尝试
SELECT BusinessUnit,
SUM(CASE WHEN OrderDate = CURDATE() THEN 1 ELSE 0 END) TodaysOrders,
SUM(CASE WHEN DATE_FORMAT(OrderDate, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') THEN 1 ELSE 0 END) ThisMonthsOrders,
SUM(CASE WHEN YEAR(OrderDate) = YEAR(CURDATE()) THEN 1 ELSE 0 END) ThisYearsOrders
FROM OrderTable
WHERE Canceled <> 'Y'
GROUP BY BusinessUnit
Here is SQLFiddledemo
这是SQLFiddle演示
