(使用SQL Server 2008)我可以很容易地得到这个工作,如果我建立一个视图,然后查询视图,但我想在一个SQL查询中执行此。我有一个表,其中包含两列(DeliveredDate(DateTime),Delivered(Varchar))。我首先将DeliveredDate转换为仅日期,然后按日期分组。同时,我执行计数交货(列是YES或NULL)。这是我用来完成这部分:
(Using SQL Server 2008) I could easily get this to work if I built a view, and then a query on the view, but I want to perform this in one SQL query. I have a table that contains two columns (DeliveredDate (DateTime), Delivered (Varchar)). I am first converting DeliveredDate to Date only, and then grouping by Date. At the same time I am performing a Count on Delivered (column is either YES or NULL). Here is what I use to accomplish this portion:
SELECT CAST([DeliveredDate] As Date), COUNT([Delivered]) FROM [TableName] GROUP BY CAST([DeliveredDate] As Date) ORDER BY CAST([DeliveredDate] As Date)作为输出,我会得到类似的结果:
As an output, I get something like:
DeliveredDate | Delivered 2012-04-24 10 2012-04-25 500 2012-04-26 422 2012-04-27 33我要找的是这样的:
DeliveredDate | Delivered | RunningTotal 2012-04-24 10 10 2012-04-25 500 510 2012-04-26 422 932 2012-04-27 33 965我已经尝试了我在那里看到的各种例子,但没有一个似乎匹配执行Count和
I've tried various examples I've seen out there, but none seem to match this scenario of performing a Count and a RunningTotal on said Count.
推荐答案我不确定CTE是否计为一个视图,但这将在SQL 2005+它不支持SUM的有序OVER子句,
I'm not sure if a CTE counts as a view but this will work in SQL 2005+ which Does Not Support ordered OVER clauses for SUM,
WITH cte (DeliveredDate, Delivered) AS (SELECT Cast([DeliveredDate] AS DATE) DeliveredDate, Count([Delivered]) Delivered FROM [TableName] GROUP BY Cast([DeliveredDate] AS DATE)) SELECT d1.delivereddate, d1.delivered, Sum(d2.Delivered) RunningTotal FROM cte d1 LEFT JOIN cte d2 ON d1.delivereddate >= d2.DeliveredDate GROUP BY d1.delivereddate, d1.delivered ORDER BY d1.delivereddate更多推荐
SQL运行总计按日期分组
发布评论