从1列中选择不同的值

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中执行此操作？

9 个解决方案

#1

DISTINCT should 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

#2

I think you should be able to use

我想你应该可以使用

SELECT DISTINCT BewonerId, Naam, VoorNaam

You can't add BoekingPlaatsId, because:

您无法添加BoekingPlaatsId，因为：

DISTINCT looks for unique rows
DISTINCT查找唯一的行
You need to specify what BoekingPlaatsId value you want
(In case of Jan Janssens, do you want BoekingPlaatsId 1 or 2?)
你需要指定你想要的BoekingPlaatsId值（如果是Jan Janssens，你想要BoekingPlaatsId 1还是2？）

What also works is this:

还有什么用呢：

SELECT MAX(BoekingPlaatsId), BewonerId, Naam, VoorNaam
FROM ...
GROUP BY BewonerId, Naam, VoorNaam

#3

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％确定语法，所以你可能需要稍微调整一下，google排名和分区。尝试这个...

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列上的select distinct是非常有用的。

#4

I think what you're looking for is something like this:

我认为你要找的是这样的：

select distinct column1 from table1 where column2 = (select distinct column2 from table1)

#5

select Naam, Voornaam, min(BewonerId), min(BoekingPlaatsId) from tableName
group by Naam, Voornaam

#6

just group By those 2 columns

只是分组由这两列

  Select Min(BoekingPlaatsId), Min(bewonerId), naam, voornaam
  from table
  group By naam, voornaam

#7

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来完成此任务。

#8

I ran into a similar problem and for me the solution was using the GROUP BY clause. 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

也许你正在分组多个列

#9

-2

Sounds like you want something like

听起来像你想要的东西

select distinct(BewonerId), Naam, Voornaam from table_name

#1