存储过程

Question

我有一个包含Device Type、DeviceName和label列的dataTable。

每个deviceType可以有多个设备。因此，我想编写一个存储过程，它将把标签列填充为01,02,03,04...对于每个DeviceName和DeviceType组合。

例如：

DeviceName    DeviceType     **Label**

Probe1            1           01
Probe2            1           02
Probe3            1           03
Tank1             2           01
Tank2             2           02
Pump1             3           01
Pump2             3           02  

Answer 1

假设DeviceName是唯一的，您可以这样使用ROW_NUMBER：

DECLARE @Data TABLE (DeviceName VARCHAR(50), DeviceType INTEGER, Label CHAR(2))
INSERT @Data VALUES ('Probe1', 1, '')
INSERT @Data VALUES ('Probe2', 1, '')
INSERT @Data VALUES ('Probe3', 1, '')
INSERT @Data VALUES ('Tank1', 2, '')
INSERT @Data VALUES ('Tank2', 2, '')
INSERT @Data VALUES ('Pump1', 3, '')
INSERT @Data VALUES ('Pump2', 3, '')

UPDATE d
SET d.Label = RIGHT('0' + CAST(x.RowNo AS VARCHAR(2)), 2)
FROM @Data d
    JOIN (
        SELECT DeviceName, ROW_NUMBER() OVER (PARTITION BY DeviceType ORDER BY DeviceName) AS RowNo
        FROM @Data
    ) x ON d.DeviceName = x.DeviceName

SELECT * FROM @Data

Answer 2

看看如何使用ROW_NUMBER

SELECT  *,
        ROW_NUMBER() OVER(PARTITION BY DeviceType ORDER BY DeviceName) Label
FROM    Table

编辑

下面是一个小的更新示例：

DECLARE @Table TABLE(
        DeviceName VARCHAR(20),
        DeviceType INT,
        Label VARCHAR(20)
)

INSERT INTO @Table SELECT 'Probe1',1,''
INSERT INTO @Table SELECT 'Probe2',1,''
INSERT INTO @Table SELECT 'Probe3',1,''
INSERT INTO @Table SELECT 'Tank1',2,''
INSERT INTO @Table SELECT 'Tank2',2,''
INSERT INTO @Table SELECT 'Pump1',3,''
INSERT INTO @Table SELECT 'Pump2',3,''


;WITH Vals AS (
        SELECT  DeviceName,
                DeviceType,
                ROW_NUMBER() OVER(PARTITION BY DeviceType ORDER BY DeviceName) Label
        FROM    @Table
)
UPDATE  @Table
SET     Label = Vals.Label
FROM    @Table t INNER JOIN
        Vals    ON  t.DeviceName = Vals.DeviceName
                AND t.DeviceType = Vals.DeviceType
SELECT  *
FROM    @Table