Tricky“分组”在SQL中排序 [英] Tricky "grouped" ordering in SQL

问题描述

我有一段时间遇到问题。

我有一个类似这样的数据库：

  Car_ID Car_Brand Car_Model Car_Price 
 1 Ford Fiesta 4000 
 2 Ford Mustang 29000 
 3福特福克斯12000 
 4本田思域15000 
 6本田Jazz 5000 
 7丰田普锐斯14000 
  

我要执行搜索，

我希望我的输出是这样的：

  Car_ID Car_Brand Car_Model Car_Price 
 1 Ford Fiesta 4000 
 3福特福克斯12000 
 2福特野马29000 
 6本田Jazz 5000 
 4本田思域15000 
 7丰田普锐斯14000 
  

最便宜的车是福特嘉年华让所以和其余的福特车型跟随它直接按价格排序。

这是可能吗？

  SELECT car_id，car_brand，car_model ，价格从汽车C1 
 JOIN（选择car_brand，MIN（价格）AS brand_price从汽车GROUP BY car_brand）C2 
在C1.car_brand = C2.car_brand 
 ORDER由C2.brand_price，C1 .car_brand，C1.price 
  

Been having trouble with this for some time.

I have a database sort of like this:

Car_ID   Car_Brand   Car_Model   Car_Price
   1     Ford        Fiesta       4000
   2     Ford        Mustang     29000
   3     Ford        Focus       12000
   4     Honda       Civic       15000
   6     Honda       Jazz         5000
   7     Toyota      Prius       14000

I want to perform a search that finds the cheapest car then orders the rest of the cars of the same brand by price ascending.

I want my output to be this:

Car_ID   Car_Brand   Car_Model   Car_Price
   1     Ford        Fiesta       4000
   3     Ford        Focus       12000
   2     Ford        Mustang     29000
   6     Honda       Jazz         5000
   4     Honda       Civic       15000
   7     Toyota      Prius       14000

The cheapest car is the Ford Fiesta so that and the rest of the Ford models follow it directly ordered by price. Honda then has the second cheapest model so the Jazz and the rest of the Hondas follow and so on.

Is this possible?

解决方案

What you need to do is create a transient data set that contains car_brand and the lowest price for that brand (which I'll call brand_price), then JOIN that data back to your original cars table. This will give you the additional piece of information (brand_price) that you need to sort the data:

 SELECT car_id, car_brand, car_model, price FROM cars C1
    JOIN (select car_brand, MIN(price) AS brand_price FROM cars GROUP BY car_brand) C2
      ON C1.car_brand = C2.car_brand
    ORDER BY C2.brand_price, C1.car_brand, C1.price

这篇关于Tricky“分组”在SQL中排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！