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
SQL - 'DISTINCT' based on only some columns?
提问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 Addresses
has an ID
field:
如果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 BY
instead of DISTINCT
in 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 top
that 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;