仅对一列使用不同的 SQL 查询多列 [英] SQL Query Multiple Columns Using Distinct on One Column Only

问题描述

我正在尝试编写一个 SQL 查询，该查询从表中选择多个列，并且仅在一列上使用不同的运算符.

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

表格很简单.列是:

tblFruit_ID, tblFruit_FruitType, tblFruit_FruitName
int          NVarChar            Text

我正在尝试选择所有具有相应 tblFruit_ID 的 tblFruit_FruitType.

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

我试过了:

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

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

-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.

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 选择一列 DISTINCT，与对应的其他列

SQL Server Distinct Union 用于一列

希望这是回答问题的足够信息.

Hopefully this is enough information for an answer.

感谢您的宝贵时间！

编辑(示例数据和所需结果)

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

结果:

1            Citrus
4            Seed

推荐答案

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

这篇关于仅对一列使用不同的 SQL 查询多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！