我有这样的数据:
+----+-------------------------+----------+----------+
| ID | DateReceived | Quantity | VendorID |
+----+-------------------------+----------+----------+
| 1 | 2010-08-09 06:53:44.783 | 2 | 1 |
| 2 | 2010-08-01 13:31:26.893 | 1 | 1 |
| 3 | 2010-07-26 07:52:29.403 | 2 | 1 |
| 4 | 2011-03-22 13:31:11.000 | 1 | 2 |
| 5 | 2011-03-22 13:31:11.000 | 1 | 2 |
| 6 | 2011-03-22 11:27:01.000 | 1 | 2 |
| 7 | 2011-03-18 09:04:58.000 | 1 | 1 |
| 8 | 2011-12-17 08:21:29.000 | 1 | 3 |
| 9 | 2012-08-10 10:55:20.000 | 9 | 3 |
| 10 | 2012-08-02 20:18:10.000 | 5 | 1 |
| 11 | 2012-07-12 20:44:36.000 | 3 | 1 |
| 12 | 2012-07-05 20:45:29.000 | 1 | 1 |
| 13 | 2013-03-22 13:31:11.000 | 1 | 2 |
| 14 | 2013-03-22 13:31:11.000 | 1 | 2 |
+----+-------------------------+----------+----------+
我想按排序数据DateReceived
并求和Quantity
。但是,只要它们是相邻的,我想对Quantity
分组的结果求和VendorID
,如下面的示例输出所示。
+----------+----------+
| VendorID | Quantity |
+----------+----------+
| 1 | 5 |
| 2 | 3 |
| 1 | 1 |
| 3 | 10 |
| 1 | 9 |
| 2 | 2 |
+----------+----------+
我目前正在通过加载所有行并在我的应用程序代码中对它们进行遍历来实现此目的。目前,这是我要消除的软件瓶颈。
什么是MS Sql Server查询以生成所需的输出?
PS。有更好的标题建议吗?
试试这个解决方案:
SELECT z.VendorID, z.GroupID,
MIN(z.DateReceived) AS DateReceivedStart,
MAX(z.DateReceived) AS DateReceivedStop,
SUM(z.Quantity) AS SumOfQuantity
FROM
(
SELECT y.VendorID,
y.RowNum1 - y.RowNum2 AS GroupID,
y.DateReceived,
y.Quantity
FROM
(
SELECT *, ROW_NUMBER() OVER(ORDER BY x.DateReceived) AS RowNum1,
ROW_NUMBER() OVER(ORDER BY x.VendorID, x.DateReceived) AS RowNum2
FROM @MyTable x
) y
) z
GROUP BY z.VendorID, z.GroupID
ORDER BY DateReceivedStart
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句