SQL - 删除重复结果

Question

I have a table that looks like this:

name     | surname
------------------
John     |  John
Jessica  |  Madson

我有一个类似这样的查询：

SELECT *
FROM TABLE
WHERE name LIKE '%j%'
    OR surname LIKE '%j%'

What I get:

John John
John John
Jessica Madson

我需要的是：

John John
Jessica Madson

如何去除重复结果？

- yturgun

Answer 1

使用DISTINCT关键字：

SELECT DISTINCT name, surname
FROM yourtable
WHERE name LIKE '%j%' OR surname LIKE '%j%'

- Mark Byers

Answer 2

尝试:

SELECT DISTINCT name, surname FROM table WHERE name LIKE '%j%' OR surname LIKE '%j%'

- Orbiting Eden

Answer 3

你可以使用“group by”进行分组。

SELECT name, surname
FROM yourtable
WHERE name LIKE '%j%' OR surname LIKE '%j%'
GROUP BY name, surname

- zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

Answer 4

您可以在SQL Server中使用DISTINCT来仅获取不同的记录。除此之外，您还可以使用ROW_NUMBER (Transact-SQL)函数，通过为结果集分配编号来获取不同的结果。 row_number()的语法如下所示。

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

下面是实现的代码。

Create table TempTable (name varchar(50), surname varchar(50))

Insert into TempTable Values 
               ('John', 'John')
             , ('John', 'John') 
             , ('Jessica', 'Madson')
             , ('Jessica', 'Madson') 
             , ('Suraj', 'Kumar')
             , ('Peter', 'Smith')

Select * from TempTable -- All records.

Select * from(
SELECT name
       ,surname
       ,row_number() over (partition by name, surname order by name, surname) as RN -- For partition
FROM TempTable
WHERE name LIKE '%j%' OR surname LIKE '%j%'
)temp
where RN = 1 -- For Distinct records only

输出将如下所示：

你可以在这里找到演示 - db<>fiddle。

- Suraj Kumar