distinct未显示所需的输出

k3fezbri  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(298)

我做了一些搜索,但找不到一个正确的答案,在一个select查询中有多个行和列,从而得到以下结果:
如果要为country为thailand的行选择所有列,并且要跳过列city中的重复项,而只从列total中选择最低价格,该怎么办。
我应该怎么申请?从%tablename%中选择不同的合作伙伴、城市、价格、费用、合计;当前不“删除”副本。

partner | country   | city    | price | fee | total

   W       | thailand  | bangkok | 400   | 15  | 415
   X       | vietnam   | hanoi   | 400   | 25  | 425 
   Y       | vietnam   | hanoi   | 300   | 30  | 330
   X       | thailand  | bangkok | 300   | 25  | 325
   Y       | thailand  | bangkok | 298   | 30  | 328
   Y       | thailand  | krabi   | 560   | 15  | 575
   Z       | thailand  | krabi   | 550   | 35  | 585

因此,我想要以下内容:

X       | thailand  | bangkok | 300   | 25  | 325
   Y       | thailand  | krabi   | 560   | 15  | 575
vfh0ocws

vfh0ocws1#

您需要使用 Group By :

SELECT t1.partner, 
       t1.country, 
       t1.city, 
       t1.price, 
       t1.fee, 
       t1.total 
FROM your_table AS t1 
JOIN (SELECT city, MIN(total) AS min_total 
      FROM your_table 
      WHERE country = 'thailand' 
      GROUP BY city) AS t2 ON t2.city = t1.city 
                          AND t2.min_total = t1.total  
GROUP BY t1.partner, 
         t1.country, 
         t1.city, 
         t1.price, 
         t1.fee, 
         t1.total
a5g8bdjr

a5g8bdjr2#

使用相关联的子查询,这很容易

select * from tablename t 
     where t.total in ( select min(total) from tablename t1
                      where t1.country=t.country
                            and t1.city=t.city
                      group by t1.country,t1.city
                    ) and t.country='thailand'

相关问题