SQL - 'DISTINCT' 仅基于某些列?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/6792285/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 11:26:16  来源:igfitidea点击:

SQL - 'DISTINCT' based on only some columns?

sql

提问by Oliver

I have a database with two tables. One of the tables contains users, the other contains addresses for those users. Each user may have several addresses (though each address is tied to only one user.)

我有一个包含两个表的数据库。其中一个表包含用户,另一个包含这些用户的地址。每个用户可能有多个地址(尽管每个地址只绑定到一个用户。)

I want to create a search that only returns one entry for each user, even if that user has several addresses. It doesn't matter which address the search pulls back - whatever the search finds first is enough.

我想创建一个只为每个用户返回一个条目的搜索,即使该用户有多个地址。搜索撤回哪个地址并不重要 - 无论搜索首先找到什么就足够了。

Here is an example search result:

这是一个示例搜索结果:

tst  olix  Chicago  IL  USA
tst  olix  Los Angeles  CA  USA
tst2 olix2 Houston  TX USA

I need the search to be such that it only returns 2 rows, rather than 3.

我需要搜索只返回 2 行,而不是 3 行。

Any ideas?

有任何想法吗?

SELECT DISTINCT
    Users.Firstname, Users.Surname, Users.UserId, 
    Users.Recommendations, Addresses.City, Addresses.Region,
    Addresses.Country
FROM
    Users INNER JOIN
    Addresses ON FT_TBL.UserId = Addresses.UserId
ORDER BY
    Users.Recommendations

采纳答案by ypercube??

If Addresseshas an IDfield:

如果Addresses有一个ID字段:

(updated for SQL-Server)

(为 SQL-Server 更新)

SELECT 
    Users.Firstname,
    Users.Surname,
    Users.UserId, 
    Users.Recommendations,
    Addresses.City,
    Addresses.Region,
    Addresses.Country
FROM
    Users INNER JOIN
    Addresses ON Users.UserId = Addresses.UserId
WHERE Addresses.ID = 
    ( SELECT TOP 1 A2.ID
      FROM Addresses AS A2
      WHERE Users.UserId = A2.UserId
    )
ORDER BY
    Users.Recommendations


Using SQL-Server's window and ranking functions:

使用 SQL-Server 的窗口和排名功能:

SELECT 
    Users.Firstname,
    Users.Surname,
    Users.UserId, 
    Users.Recommendations,
    Addresses.City,
    Addresses.Region,
    Addresses.Country
FROM
    Users INNER JOIN
     ( SELECT *
            , ROW_NUMBER() OVER (PARTITION BY UserID) AS rn
       FROM Addresses
     ) AS Addresses ON Users.UserId = Addresses.UserId
                    AND Addresses.rn = 1
ORDER BY
    Users.Recommendations

回答by Matthew

You probably need to use GROUP BYinstead of DISTINCTin this case.

在这种情况下,您可能需要使用GROUP BY代替DISTINCT

Post your query now and I will help you more.

现在发布您的查询,我会为您提供更多帮助。

Alternatively, if you just want to return the firstaddress, that's a different query entirely. Does it need to return the address? What data do you need? What does "first" mean in this context? How is the data ordered?

或者,如果您只想返回第一个地址,那完全是一个不同的查询。需要返回地址吗?你需要什么数据?在这种情况下,“第一”是什么意思?数据是如何排序的?

Arbitrarily you could do something like this (untested), depending on your DB:

您可以随意执行这样的操作(未经测试),具体取决于您的数据库:

SELECT 
    userID
    , FIRST(address)
FROM
    yourTable
GROUP BY
    userID

回答by JNK

SELECT Name, MAX(Address), MAX(other field)...
FROM MyTable
GROUP BY Name

Will give you one row per Name.

会给你一行Name

回答by dana

Assuming the address table has an id column:

假设地址表有一个 id 列:

select p.fname, p.lname, a.state, a.country
from person p
join address a on a.personid = p.personid
where not exists
    (select *
    from address a2
    where a2.personid = a.personid
      and a2.addressid < a.addressid)

My query returns all people with addresses. The exists()clause is used to determine that the returned address has the lowest addressid assigned to the person. The result will only contain 1 address per person.

我的查询返回所有有地址的人。该exists()子句用于确定返回的地址具有分配给此人的最低地址 ID。结果将只包含每人 1 个地址。



EDIT: Another way to do this using topthat has not been shown by others:

编辑:使用top其他人未显示的另一种方法来做到这一点:

select p.fname, p.lname, a.state, a.country
from person p
join address a on a.addressid =
    (select top 1 a2.addressid
    from address a2
    where a2.personid = p.personid)

This should be very efficient as the nested query will short circuit on the first address found for each person.

这应该非常有效,因为嵌套查询将在为每个人找到的第一个地址上短路。

回答by Kerrek SB

Try an aggregate:

尝试聚合:

SELECT user, address FROM users
JOIN addresses ON (users.user_id = addresses.user_id)
GROUP BY user;