我正在尝试使用连接到MySQL数据库的ASP.NET创建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
根据上面的记录,我希望结果显示如下
BusinessUnit TodaysOrders ThisMonthsOrders ThisYearsOrders UnitA 0 1 2 UnitB 2 3 3
我当前的代码如下。这给了我错误(关于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
代码继续
FROM OrderTable WHERE OrderTable.Canceled. <> 'Y';
Sum Case最好在这里使用吗?
该错误是由函数名称和括号之间的空格引起的
SUM (CASE WHEN ... ^^
阅读更多函数名称解析和解析
尝试
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
这是 SQLFiddle 演示