时间戳

首页 > 解决方案 > SQL 提取不同的值并创建 ID

sql - SQL 提取不同的值并创建 ID

问题描述

我有这个查询：

select
    p.[PersonID],
    d.[DeviceID],
    'DeviceID' + convert(char, ROW_NUMBER() over(partition by p.PersonID order by d.DeviceID)) as 'key'
from People p
    inner join Devices d
    on p.PersonID = d.PersonID
    where d.DeviceID is not null

从这些表中：

PersonID | Name
---------------
    1       Jim
    2       Bob

DeviceID | PersonID
-------------------
   40         1
   40         1
   70         2
   90         2

给了我这些结果：

PersonID | DeviceID |    key
--------------------------------
    1         40      DeviceID1
    1         40      DeviceID2
    2         70      DeviceID1
    2         90      DeviceID2

但实际上，因为PersonID1 有DeviceID40 两次，所以我只需要这个：

PersonID | DeviceID |    key
--------------------------------
    1         40      DeviceID1
    2         70      DeviceID1
    2         90      DeviceID2

我尝试将 DISTINCT 添加到我的查询中，但它在第一部分不起作用，我不确定如何partition在位中应用它......

非常感谢任何帮助！

标签： sqlsql-serversql-server-2012

解决方案

使用dense_rank()：

select distinct p.[PersonID], d.[DeviceID],
       concat('DeviceID',  dense_rank() over (partition by p.PersonID order by d.DeviceID)) as [key]
from People p join
     Devices d
     on p.PersonID = d.PersonID
where d.DeviceID is not null;

请注意，我还将代码更改为使用concat()而不是+. 您转换为字符串充满了危险，因为您没有在转换中包含长度。在 SQL Server 中，默认长度因上下文而异。在这种情况下，省略长度可能会起作用。但如果你有这样做的习惯，它现在可能适用于另一种情况。

此外，仅对字符串和日期常量使用单引号。 key因为它是 SQL 关键字，所以对于列来说是一个坏名称。如果确实使用它，请使用方括号或双引号。

推荐阅读