代码之家 › 专栏 › 技术社区 › Scott Chamberlain

如何添加每个分组唯一的标识列

sql-server-2005

2

Scott Chamberlain · 技术社区 · 15 年前

假设我有两列

Product     Product_Cat
-------     -----------
Cat         0
Dog         0
Potatoes    2
Carrots     2
Laundry     1
Bird        0

所以输出看起来像

Product     Product_Cat     Cat_Ident
-------     -----------     ---------
Cat         0               1
Dog         0               2
Potatoes    2               1
Carrots     2               2
Laundry     1               1
Bird        0               3

2 回复 | 直到 15 年前

1

3

Codesleuth 15 年前

你需要使用 RANK() 具体如下:

CREATE TABLE #Products
(
    ID int IDENTITY(1,1),
    Product nvarchar(8),
    Product_Cat int
)
GO

INSERT INTO #Products (Product, Product_Cat)
VALUES ('Cat', 0)
,('Dog', 0)
,('Potatoes', 2)
,('Carrots', 2)
,('Laundry', 1)
,('Bird', 0)
GO

ALTER TABLE #Products
    ADD Cat_Ident int
GO

UPDATE #Products
    SET Cat_Ident = rankVal
FROM #Products 
    INNER JOIN (
        SELECT ID, RANK () OVER (PARTITION BY Product_Cat ORDER BY ID ) AS rankVal
        FROM #Products ) rankings ON #Products.ID = rankings.ID

SELECT * FROM #Products

DROP TABLE #Products

ID          Product  Product_Cat Cat_Ident
----------- -------- ----------- -----------
1           Cat      0           1
2           Dog      0           2
3           Potatoes 2           1
4           Carrots  2           2
5           Laundry  1           1
6           Bird     0           3

(6 row(s) affected)

2

0

JonH 15 年前

我们需要更多的信息,但看起来您需要使用触发器来形成cat_ident的值。

SELECT COUNT()+1 GROUP BY ProductCat 应该有帮助

3

-1

Ardent Coder Michael Richardson 5 年前

INSERT INTO Products
           (Product
           ,Product_Cat
           ,Cat_Ident)
     VALUES
           ('Flower'
           ,1
           , (select ISNULL( (SELECT top (1) Cat_Ident +1  as x
               FROM Products
              where Product_Cat =1
                    order by  Cat_Ident desc),'1') ))