SQL查询多列仅在一列上使用唯一

I am trying to write a SQL query that selects multiple columns from a table with the distinct operator on one column only.

我正在尝试编写一个SQL查询，从一个表中选择多个列，只在一列上使用distinct运算符。

The table is simple. The columns are:

桌子很简单。列是：

tblFruit_ID, tblFruit_FruitType, tblFruit_FruitName
int          NVarChar            Text

I am trying to select all the tblFruit_FruitType with their corresponding tblFruit_ID.

我试图选择所有tblFruit_FruitType及其对应的tblFruit_ID。

I have tried:

我努力了：

Select Distinct(tblFruit_FruitType), tblFruit_ID FROM tblFruit

-Returns all results, not just distinct

- 回报所有结果，而不仅仅是截然不同

Select tblFruit_FruitType, tblFruit_ID FROM tblFruit Group By tblFruit_FruitType

-Errors with Column tblFruit_ID is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

列tblFruit_ID的错误在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

Select tblFruit_FruitType, tblFruit_ID FROM tblFruit Group By tblFruit_FruitType, tblFruit_ID

-Returns all results, not just distinct

- 回报所有结果，而不仅仅是截然不同

I also checked out these similar posts and could not get anything to work :(

我也检查了这些类似的帖子，无法得到任何工作:(

mySQL select one column DISTINCT, with corresponding other columns

mySQL选择一列DISTINCT，以及相应的其他列

SQL Server Distinct Union for one column

SQL Server Distinct Union用于一列

Hopefully this is enough information for an answer.

希望这是一个足够的信息来回答。

Thank you for your time!

感谢您的时间！

EDIT (Sample Data and Desired Results)

编辑（样本数据和期望结果）

tblFruit_ID, tblFruit_FruitType, tblFruit_FruitName
int          NVarChar            Text
1            Citrus              Orange
2            Citrus              Lime
3            Citrus              Lemon
4            Seed                Cherry
5            Seed                Banana

Results:

结果：

1            Citrus
4            Seed

3 个解决方案

#1

select * from tblFruit where
tblFruit_ID in (Select max(tblFruit_ID) FROM tblFruit group by tblFruit_FruitType)

#2

You must use an aggregate function on the columns against which you are not grouping. In this example, I arbitrarily picked the Min function. You are combining the rows with the same FruitType value. If I have two rows with the same FruitType value but different Fruit_Id values for example, what should the system do?

您必须对未进行分组的列使用聚合函数。在这个例子中，我随意选择了Min函数。您正在使用相同的FruitType值组合行。如果我有两行具有相同的FruitType值但不同的Fruit_Id值，例如，系统应该做什么？

Select Min(tblFruit_id) As tblFruit_id
    , tblFruit_FruitType
From tblFruit
Group By tblFruit_FruitType

SQL Fiddle example

SQL小提琴示例

#3

I needed to do the same and had to query a query to get the result

我需要做同样的事情，不得不查询查询以获得结果

I set my first query up to bring in all IDs from the table and all other information needed to filter:

我设置了我的第一个查询以引入表中的所有ID以及过滤所需的所有其他信息：

SELECT tMAIN.tLOTS.NoContract, tMAIN.ID
FROM tMAIN INNER JOIN tLOTS ON tMAIN.ID = tLOTS.id
WHERE (((tLOTS.NoContract)=False));

Save this as Q04_1 -0 this returned 1229 results (there are 63 unique records to query - soime with multiple LOTs)

保存为Q04_1 -0这返回了1229个结果（有63个唯一记录可供查询 - 有多个LOTs的soime）

SELECT DISTINCT ID
FROM q04_1;

Saved that as q04_2

保存为q04_2

I then wrote another query which brought in the required information linked to the ID

然后我写了另一个查询，它带来了与ID相关的必要信息

SELECT q04_2.ID, tMAIN.Customer, tMAIN.Category
FROM q04_2 INNER JOIN tMAIN ON q04_2.ID = tMAIN.ID;

Worked a treat and got me exactly what I needed - 63 unique records returned with customer and category details.

工作了一个款待，让我得到我所需要的 - 63个独特的记录与客户和类别详细信息一起返回。

This is how I worked around it as I couldn't get the Group By working at all - although I am rather "wet behind the ears" weith SQL (so please be gentle and constructive with feedback)

这就是我解决这个问题的方法，因为我无法通过工作来完成工作 - 虽然我很“浑浑噩噩”，但我仍然感到厌倦了SQL（所以请保持温和和建设性的反馈）

#1

select * from tblFruit where
tblFruit_ID in (Select max(tblFruit_ID) FROM tblFruit group by tblFruit_FruitType)

#2

Select Min(tblFruit_id) As tblFruit_id
    , tblFruit_FruitType
From tblFruit
Group By tblFruit_FruitType