SQL 从 1 列中选择不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1918556/
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
Select distinct values from 1 column
提问by Parm
I want to select distinct values from only one column (the BoekingPlaatsId column) with this query:
我想使用此查询仅从一列（BoekingPlaatsId 列）中选择不同的值：
SELECT MAX(BoekingPlaatsId), BewonerId, Naam, VoorNaam
FROM table
GROUP BY BewonerId, Naam, VoorNaam
How do I do that in SQL Server?
我如何在 SQL Server 中做到这一点？
回答by doza
DISTINCTshould work if you just want the user names:
DISTINCT如果您只想要用户名，应该可以工作：
SELECT DISTINCT BewonerId, Naam, Voornaam
FROM TBL
but if you need the minimum ID values, group by the names...
但如果您需要最小 ID 值，请按名称分组...
SELECT MIN(BoekingPlaatsId), MIN(BewonerId), Naam, Voornaam
FROM TBL
GROUP BY Naam, Voornaam
回答by Sander Rijken
I think you should be able to use
我认为你应该能够使用
SELECT DISTINCT BewonerId, Naam, VoorNaam
You can't add BoekingPlaatsId, because:
你不能添加BoekingPlaatsId，因为：
DISTINCTlooks for unique rows- You need to specify what
BoekingPlaatsIdvalue you want
(In case of Jan Janssens, do you want BoekingPlaatsId 1 or 2?)
DISTINCT寻找唯一的行- 您需要指定
BoekingPlaatsId您想要的值
（对于 Jan Janssens，您想要 BoekingPlaatsId 1 还是 2？）
What also works is this:
同样有效的是：
SELECT MAX(BoekingPlaatsId), BewonerId, Naam, VoorNaam
FROM ...
GROUP BY BewonerId, Naam, VoorNaam
回答by Paul Creasey
I don't do alot of this so i'm not 100% certain of the syntax so you may need to tweak it slightly, google rank over and partition. Try this...
我没有做很多这样的事情，所以我不是 100% 确定语法，所以你可能需要稍微调整它，谷歌排名和分区。尝试这个...
SELECT
*,
RANK() OVER(PARTITION BY Naam order by Naam ) as Rank
FROM
TABLE
WHERE
Rank = 1
This is overkill for a 4 column table, but if you have a fairly denormalised table with alot of columns, this approach is invaluable for select distinct on 1 column.
这对于一个 4 列的表来说太过分了，但是如果您有一个包含大量列的相当非规范化的表，这种方法对于在 1 列上选择不同的选择是非常宝贵的。
回答by Thrillseeker419
This is how you can select from a table with only unique values for your column:
这是您如何从只有列唯一值的表中进行选择的方法：
CREATE VIEW [yourSchema].[v_ViewOfYourTable] AS
WITH DistinctBoekingPlaats AS
(
SELECT [BewonerId],
[Naam],
[VoorNaam],
[BoekingPlaatsId],
ROW_NUMBER() OVER(PARTITION BY [BoekingPlaatsId] ORDER BY DESC) AS 'RowNum'
FROM [yourSchema].[v_ViewOfYourTable]
)
SELECT *
FROM DistinctProfileNames
WHERE RowNum = 1
--if you would like to apply group by you can do it in this bottom select clause but you don't need it to gather distinct values
You don't need group by to accomplish this.
您不需要 group by 来完成此操作。
回答by S.Joshi
I ran into a similar problem and for me the solution was using the GROUP BYclause. So basically, I grouped all the blogs with same title as one group.
我遇到了类似的问题，对我来说，解决方案是使用GROUP BY子句。所以基本上，我将所有标题相同的博客归为一组。
Syntax:
句法：
SELECT post_title, post_link
FROM blogs
WHERE [ conditions ]
GROUP BY post_title
ORDER BY post_title;
Maybe you are grouping multiple columns
也许您正在对多个列进行分组
回答by Charles Bretana
just group By those 2 columns
只需按这 2 列分组
Select Min(BoekingPlaatsId), Min(bewonerId), naam, voornaam
from table
group By naam, voornaam
回答by BlackTigerX
select Naam, Voornaam, min(BewonerId), min(BoekingPlaatsId) from tableName
group by Naam, Voornaam
回答by Michael
I think what you're looking for is something like this:
我认为你正在寻找的是这样的：
select distinct column1 from table1 where column2 = (select distinct column2 from table1)
回答by mmorrisson
Sounds like you want something like
听起来你想要类似的东西
select distinct(BewonerId), Naam, Voornaam from table_name
