mysql - 明显没有显示所需的输出

Question

我做了相当多的搜索，但找不到在选择查询中包含多个行和列以获得以下内容的正确答案:

如果您想要选择国家/地区为泰国的行的所有列，并且想要跳过列城市中的重复项并仅从列总计中选择最低价格，该怎么办？

我应该如何应用 DISTINCT 比？从 %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

Answer 1

您需要使用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