似乎无法使查询结果唯一

vjrehmav 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(278)

我的问题是，我只需要对每种饮料（a.drink）做一次/唯一的以下陈述，但我似乎无法做到这一点。

create table LOWEST as 
    select a.DRINK, min(b.PRICE), c.STREET, c.BLDG_NO
    from ALLDRINKS a
    left join SERVES b on a.DRINK = b.DRINK
    left join LOCATED c on b.PUB = c.PUB
    group by a.DRINK, c.STREET, c.BLDG_NO

我得到的最新结果

------------------------------------------------
| DRINK |min(b.PRICE)|     STREET    | BLDG_NO |
| VODKA |    7.10    | FAKE ST.      |   123   |
| VODKA |    4.50    | OAK Ave.      |    13   | 
| VODKA |    8.30    | Rail RD.      |    11   | 
| RUM   |    6.30    | Cherry RD.    |   131   | 
| RUM   |   10.30    | TEST Pl.      |    21   |  
------------------------------------------------

我期待的是：

------------------------------------------------
| DRINK |min(b.PRICE)|     STREET    | BLDG_NO |
| VODKA |    4.50    | OAK Ave.      |    13   |  
| RUM   |    6.30    | Cherry RD.    |   131   |   
------------------------------------------------

如果你也能解释一下需要做的事情背后的逻辑，那对将来会非常有帮助！提前谢谢-使用mysql workbench 8.0

sql mysql Join greatest-n-per-group unique

来源：https://stackoverflow.com/questions/61983426/cant-seem-to-make-the-results-of-query-to-be-unique

2条答案

按热度按时间

np8igboo1#

你想排进去吗 serves 每个都有最低价格 drink ，以及相应的 location . 如果是这样的话，你不应该考虑聚合，而应该考虑过滤。
可以使用相关子查询在 left join :

create table lowest as 
select d.drink, s.price, c.street, l.bldg_no
from alldrinks d
left join serves s
    on  s.drink = d.drink
    and s.price = (select min(s1.price) from serves s1 where s1.drink = s.drink)
left join located l 
    on  l.pub = s.pub

请注意，我重命名了您的表别名，以便它们更有意义，从而使查询更易于遵循。

赞(0）回复(0）举报 2021-07-26

zaqlnxep2#

你可以用 row_number() 表中的窗口函数 SERVES 以获得最低价格 DRINK ，所以你不需要 group by :

create table LOWEST as 
    select a.DRINK, b.PRICE, c.STREET, c.BLDG_NO
    from ALLDRINKS a
    left join (
      select t.* 
      from (
        select *, row_number() over (partition by DRINK order by price) rn
        from SERVES      
      ) t
      where t.rn = 1
    ) b on a.DRINK = b.DRINK
    left join LOCATED c on b.PUB = c.PUB

赞(0）回复(0）举报 2021-07-26

我来回答

似乎无法使查询结果唯一

2条答案

相关问题

热门标签

最新问答