sql-server - SQL 服务器 : how to create sequence number column

Question

我有一个包含以下数据的销售表:

| SalesId | CustomerId | Amount |  
|---------|------------|--------|  
| 1       | 1          | 100    |  
| 2       | 2          | 75     |  
| 3       | 1          | 30     |  
| 4       | 3          | 49     |  
| 5       | 1          | 93     |  

我想在此表中插入一列，告诉我们客户购买的次数。所以它会像:

| SalesId | CustomerId | Amount | SalesNum |  
|---------|------------|--------|----------|  
| 1       | 1          | 100    | 1        |  
| 2       | 2          | 75     | 1        |  
| 3       | 1          | 30     | 2        |  
| 4       | 3          | 49     | 1        |  
| 5       | 1          | 93     | 3        |  

所以我可以看到在 salesId = 5 中，这是 customerId = 1 的第三笔交易。我如何编写这样的查询来插入/更新这样的列？我在 MS SQL 上，但我也对 MYSQL 解决方案感兴趣，如果我将来需要在那里做的话。

谢谢。

附言。为表格格式道歉。无法弄清楚如何很好地格式化它。

Answer 1

你需要ROW_NUMBER()分配一个序列号。不过，我强烈建议不要存储此值，因为您需要在每次更新时重新计算它，相反，如果您经常需要它，最好创建一个 View :

CREATE VIEW dbo.SalesWithRank
AS
    SELECT  SalesID,
            CustomerID,
            Amount,
            SalesNum = ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesID)
    FROM    Sales;
GO

SQL Server Example on SQL Fiddle

ROW_NUMBER() 不会在同一组中分配重复项，例如如果您根据 Amount 分配行，并且同一客户的两次销售额均为 100，则在您的 中没有任何其他订购条件的情况下，它们将不会具有相同的 SalesNum >ROW_NUMBER() 函数它们将被随机排序。如果您希望相同金额的销售额具有相同的 SalesNum，则需要使用 RANK 或 DENSE_RANK。 DENSE_RANK 将在序列中没有间隙，例如 1, 1, 2, 2, 3，而 RANK 将从相应的位置开始，例如1、1、3、3、5。

如果您必须将此作为更新执行，那么您可以使用:

WITH CTE AS
(   SELECT  SalesID,
            CustomerID,
            Amount,
            SalesNum,
            NewSalesNum = ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesID)
    FROM    Sales
)
UPDATE  CTE
SET     SalesNum = NewSalesNum;

SQL Server Update Example on SQL Fiddle

MySQL 没有排名函数，因此您需要使用局部变量通过跟踪上一行的值来实现排名。这在 View 中是不允许的，因此您只需要在需要行号的地方重复此逻辑:

SELECT  s.SalesID,
        s.Amount,
        @r:= CASE WHEN @c = s.CustomerID THEN @r + 1 ELSE 1 END AS SalesNum,
        @c:= CustomerID AS CustomerID
FROM    Sales AS s
        CROSS JOIN (SELECT @c:= 0, @r:= 0) AS var
ORDER BY s.CustomerID, s.SalesID;

order by 在这里很关键，这意味着为了在不影响排名的情况下对结果进行排序，您需要使用子查询:

SELECT  SalesID,
        Amount,
        CustomerID,
        SalesNum
FROM    (   SELECT  s.SalesID,
                    s.Amount,
                    @r:= CASE WHEN @c = s.CustomerID THEN @r + 1 ELSE 1 END AS SalesNum,
                    @c:= CustomerID AS CustomerID
            FROM    Sales AS s
                    CROSS JOIN (SELECT @c:= 0, @r:= 0) AS var
            ORDER BY s.CustomerID, s.SalesID
        ) AS s
ORDER BY s.SalesID;

MySQL Example on SQL Fiddle

同样，我建议不要存储该值，但如果您必须在 MySQL 中使用，您可以使用:

UPDATE  Sales
        INNER JOIN
        (   SELECT  s.SalesID,
                    @r:= CASE WHEN @c = s.CustomerID THEN @r + 1 ELSE 1 END AS NewSalesNum,
                    @c:= CustomerID AS CustomerID
            FROM    Sales AS s
                    CROSS JOIN (SELECT @c:= 0, @r:= 0) AS var
            ORDER BY s.CustomerID, s.SalesID
        ) AS s2
            ON Sales.SalesID = s2.SalesID
SET     SalesNum = s2.NewSalesNum;

MySQL Update Example on SQL Fiddle