时间戳

首页 > 解决方案 > 如何连续计算一个数字序列

sql - 如何连续计算一个数字序列

问题描述

我需要选择并计算已获得 10% 折扣的 OrderID 数量。

我试过使用 COUNT 函数，但它只计算一个唯一实体的出现，而不是每个 OrderID。

USE Northwind
GO

SELECT a.OrderID, COUNT(a.OrderID) as 'SeqNo', b.ProductName, a.UnitPrice, a.Quantity, a.UnitPrice*a.Quantity as Amount, a.Discount
FROM [Order Details] as a
INNER JOIN [Products] as b
ON a.ProductID = b.ProductID
GROUP BY a.OrderID, b.ProductName, a.UnitPrice, a.Quantity, a.Discount
HAVING a.Discount = 0.1

我实际上希望“SeqNo”来计算 OrderID，但它们都是 1。

  OrderID SeqNo       ProductName      UnitPrice Quantity Amount Discount
1  10288  | 1  | Tourtiere             | 5.9    |  10 |   59.00  | 0.1
2  10288  | 2  | Scottish Longbreads   | 10     |  3  |   30.00  | 0.1
3  10291  | 1  | Konbu                 | 4.8    |  20 |   96.00  | 0.1
3  10291  | 2  | Gula Malacca          | 15.5   |  24 |   372.00 | 0.1
3  10291  | 3  | Mankimup Dried Apples | 42.4   |  2  |   84.8   | 0.1

标签： sqlsql-servernorthwind

解决方案

您可以使用ROW_NUMBER代替COUNT：

USE Northwind
GO

SELECT a.OrderID
, ROW_NUMBER() OVER(ORDER BY a.OrderID ASC) AS 'SeqNo'
, b.ProductName
, a.UnitPrice
, a.Quantity
, a.UnitPrice*a.Quantity as Amount
, a.Discount
FROM [Order Details] as a
INNER JOIN [Products] as b
ON a.ProductID = b.ProductID
GROUP BY a.OrderID, b.ProductName, a.UnitPrice, a.Quantity, a.Discount
HAVING a.Discount = 0.1

阅读更多关于ROW_NUMBER：https ://docs.microsoft.com/fr-fr/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017

推荐阅读