在sql中选择只有一个不同列的多个列

I need to query an SQL database to find all distinct values of one column and I need an arbitrary value from another two columns. For example, consider the following table with three columns: CurrencyCode, BuyRate and SellRate:

我需要查询SQL数据库以查找一列的所有不同值，我需要另外两列中的任意值。例如，考虑下面的表有三列：CurrencyCode，BuyRate和SellRate：

CurrencyCode  BuyRate  SellRate
AUD           1.037    1.97
AUD           1.079    1.99
EUR           0.7288   0.8763
EUR           0.731    0.88
GBP           0.59     0.72

I wish to retrieve one row with distinct CurrencyCode, perhaps getting these three rows:

我想检索具有不同CurrencyCode的一行，也许获取这三行：

CurrencyCode  BuyRate  SellRate
AUD           1.037    1.97
EUR           0.7288   0.8763
GBP           0.59     0.72

I tried my SQL query like:

我试过我的SQL查询，如：

SELECT distinct CurrencyCode, BuyRate, SellRate FROM  Currencies

But I am not getting the desired result as it districts all the columns.

但是我没有得到理想的结果，因为它划分了所有列。

2 个解决方案

#1

Try with GROUP BY clause and MIN function as below

尝试使用GROUP BY子句和MIN函数，如下所示

SELECT CurrencyCode, MIN(BuyRate), MIN(SellRate)
FROM  Currencies
GROUP BY CurrencyCode

#2

Till now I found this is the best answer to get Min(SellRate) on the basis of Min(BuyRate)

直到现在我发现这是以Min（BuyRate）为基础获得Min（SellRate）的最佳答案

eg.

例如。

    CurrencyCode    BuyRate   SellRate
       AUD           1.037     1.97
       AUD           1.079     1.89  //Changed from 1.99 to 1.89
       AUD           1.038     1.77  //New row added
       EUR           0.7288    0.8763
       EUR           0.731     0.88
       GBP           0.59      0.72

Here I am expecting AUDrows for BuyRate and SaleRate will be 1.037 and 1.97

在这里，我预计BuyRate和SaleRate的AUDrows将分别为1.037和1.97

select CurrencyCode, Min(BuyRate) as BuyRate, 
(select top 1 SellRate from Currencies as C 
where C.CurrencyCode=Currencies.CurrencyCode 
and 
C.BuyRate= Min(Currencies.BuyRate) order by SellRate) as SellRate
from Currencies 
group
by CurrencyCode

#1