DISTINCT with INNER JOIN

Question

我有一个问题要问

餐桌水果

 FruitID |  Fruit  | Unit Price 
    1    |  Orange |     $3
    2    |  Apple  |     $2
    3    |  Grape  |     $4

表FruitDetails

Picture      | Color   | FruitID
Orange_o.jpg | Orange  |    1
Apple_g.jpg  | Green   |    2
Apple_r.jpg  | Red     |    2
Grape_p.jpg  | Purple  |    3
Grape_g.jpg  | Green   |    3

我想得到的结果是所有的水果都没有重复的名字。

示例

Fruit | UnitPrice | Picture      | Color
Orange|    $3     | Orange_o.jpg | Orange
Apple |    $2     | Apple_g.jpg  | Green
Grape |    $4     | Grape_p.jpg  | Purple

这有可能实现吗？谢谢

Answer 1

为SQL Server编写，但实际查询应在其他数据库上工作。

设置数据：

declare @Fruit table (FruitID int not null,Fruit varchar(10) not null,UnitPrice int not null)
insert into @Fruit(FruitID,Fruit,UnitPrice) values
(1,'Orange',3),
(2,'Apple',2),
(3,'Grape',4)

declare @FruitDetails table (FruitID int not null,Picture varchar(20) not null,Color varchar(10) not null)
insert into @FruitDetails (FruitID,Picture,Color) values
(1,'Orange_o.jpg','Orange'),
(3,'Grape_p.jpg','Purple'),
(3,'Grape_g.jpg','Green'),
(2,'Apple_g.jpg','Green'),
(2,'Apple_r.jpg','Red')

查询：

select
    f.Fruit,
    f.UnitPrice,
    fd.Picture,
    fd.Color
from
    @Fruit f
        inner join
    @FruitDetails fd
        on
            f.FruitID = fd.FruitID
        left join
    @FruitDetails fd_anti
        on
            f.FruitID = fd_anti.FruitID and
            fd_anti.Picture < fd.Picture --This is the condition for picking a better row
where
    fd_anti.FruitID is null --This eliminates rows where a better row was picked

结果：

Fruit      UnitPrice   Picture              Color
---------- ----------- -------------------- ----------
Orange     3           Orange_o.jpg         Orange
Grape      4           Grape_g.jpg          Green
Apple      2           Apple_g.jpg          Green

这与您的预期结果不符，但是您还没有为从FruitDetail中选择“最佳”行的条件给出一个好的定义。

Answer 2

SQL中没有"pick any one“。它不存在是有充分的理由的；它不是确定性的，这将使您在尝试调试应用程序时感到非常头疼。然而，可以手动选择一个，只是需要一些额外的技巧(一些数据库具有可以使其更容易的扩展，但说到通用SQL)。

因此，首先您必须选择一个标准，根据该标准，您将选择要连接的一条线。假设你想要颜色按字母顺序排在第一位的那个(在实际情况下，你可能会对类似的东西有一些优先级或重要性)。对于每个水果，您使用的列必须是唯一的！您可以通过使用group-by或嵌套select的另一个连接来计算它。嵌套的select更易于编写和理解。它将是：

(select min(Color) from FruitDetails as inner where inner.Fruit = Fruit.Fruit)

现在你用condition连接表，颜色就是那一种，所以：

select * from Fruit
    join FruitDetails as outer
        on outer.Fruit = Fruit.Fruit
        and outer.Color = (select min(Color) from FruitDetails as inner where inner.Fruit = Fruit.Fruit)

这假设FruitDetails表有一个您忘记的Fruit列，但是如果没有它，连接就根本不可能实现。并且它有一个unique(Fruit, Color)约束来保证每个水果只有一条色值最小的线。

另一种具有两个连接的替代方案是：

select Fruit.* ThisDetails.*
    from Fruit
    join FruitDetails as ThisDetails using (Fruit)
    join FruitDetails as BestDetails using (Fruit)
    group by Fruit.*, ThisDetails.*
    having ThisDetails.Color = min(BestDetails.Color)

(在大多数(但不是全部) SQL变体中，using (column)是onetable.column = othertable.column的缩写)