删除 SQL 重复项

Question

我在 tsql 中遇到了重复的问题。如何删除？这是我的输出：

  id          name       ref
----------- ---------- --------------
1           John       null
1           John       RE
2           Smith      null
2           Smith      DOS
3           Van        null
4           Sam        CR
5           Bob        null

法典：

 SELECT DISTINCT
    u.id,
    u.name,
    c.ref
    FROM users u
    LEFT JOIN con c on c.id=u.id

我希望输出是这样的：

  id          name       ref
----------- ---------- --------------
1           John       RE
2           Smith      DOS
3           Van        null
4           Sam        CR
5           Bob        null

这只是一个例子，我很少。

谢谢

Answer 1

使用 MAX 和 GROUP BY 怎么样：

 SELECT 
    u.id,
    u.name,
    MAX(c.ref) as Ref
 FROM users u
    LEFT JOIN con c on c.id=u.id
 GROUP BY 
    u.id,
    u.name

Answer 2

尝试下面的查询，这将为您提供确切的所需输出：

DECLARE @SAMPLEDATA TABLE(id INT,        name VARCHAR(100),      ref VARCHAR(100))
INSERT INTO @SAMPLEDATA VALUES
(1,           'John',       null),
(1,           'John',       'RE'),
(2,           'Smith',      null),
(2,           'Smith',      'DOS'),
(3,          'Van',        null),
(4,           'Sam',        'CR'),
(5,           'Bob',        null)
 ;WITH SAMPLEDATA
 AS
 (
 SELECT ROW_NUMBER()OVER (ORDER BY (SELECT 100))SNO,* FROM @SAMPLEDATA
 )
 SELECT ID,NAME,REF FROM(
 SELECT * ,ISNULL((SELECT CASE WHEN T2.ID=T1.id AND T1.ref IS NULL THEN 'R'     ELSE 'NR' END
 FROM SAMPLEDATA T2 WHERE T2.SNO=T1.SNO+1),'NR')COL FROM SAMPLEDATA T1) DATA     WHERE COL='NR'

输出

------------------
ID  NAME    REF
------------------
1   John    RE
2   Smith   DOS
3   Van     NULL
4   Sam     CR
5   Bob     NULL
------------------

Answer 3

请使用以下查询：

    DECLARE @TABLE TABLE 
    (id INT, name VARCHAR(10),ref VARCHAR(10))
    INSERT INTO @TABLE VALUES
    (1,'John',null),
    (1,'John','RE'),
    (2,'Smith',null),
    (2,'Smith','DOS'),
    (3,'Van',null),
    (4,'Sam','CR'),
    (5,'Bob',null)
    SELECT id,name,ref FROM
    (
     SELECT 
        id,name,ref,RN = ROW_NUMBER() OVER (PARTITION BY id,name ORDER BY ref DESC)
     FROM 
        @TABLE
    ) AS A
    WHERE RN < 2

您也可以使用它：

     SELECT 
        u.id,
        u.name,
        MAX(u.ref) as Ref
     FROM 
        @TABLE u
     GROUP BY 
        u.id,
        u.name