Mysql 多重排序依据

Question

我正在编写一个mysql查询，其中我必须按优先级和价格排序。这是我的表结构。

TABLE1
------
id | name | priority_order
 1    A        1
 2    B        2
 3    C        0
 4    D        0
 5    E        4
 6    F        0
 7    G        0
 8    H        3
 9    I        5
10    J        0  

TABLE2
------
    id | productid | listname | Color | price 
   100     5012        A        Blue     700
   101     5012        B        Blue     400
   102      "          C        Blue     850
   103      "          A        Red      650
   104      "          H        Red      550
   105      "          B        Red      600  
   106      "          E        Green    780
   107      "          E        Blue     650
   108      "          F        Blue     300           
   109      "          G        Red      355
   110      "          B        Black    550
   111      "          A        Black    480
   112      "          C        Red      800
   113      "          H        Black    785  
   114      "          I        Black    625
   115      "          I        Red      480

现在我必须按优先级以低价订购。即我必须在第一优先级显示低价，在第二优先级显示低价，在第三优先级显示低价..其余价格处于其位置。

Expected Output :
----------------
       id | productid | listname | Color | price  | priority_order 
       111     5012        A        Black    480        1
       101     5012        B        Blue     400        2  
       104      "          H        Red      550        3
       106      "          E        Blue     650        4
       115      "          I        Red      480        5
=========== The above order is taken low price in each priority ========
=========== Now the remaining should be in any priority order but should be in low price ASC====
       108      "          F        Blue     300        0
       109      "          G        Red      355        0
       110      "          B        Black    550        2
       105      "          B        Red      600        2
       114      "          I        Black    625        5
..................... and so on..

我尝试的查询是

select a.name , a.priority_order , b.color , b.productid , b.price from table1 a inner join table2 b on a.name = b.listname where productid = 5012 order by
case when priority_order = 0 then 2
when priority_order > 0 then 1
b.price ASC

Answer 1

您必须执行相同的选择两次；一次获取记录，一次查找每个优先订单的最低价格。连接这些并使用价格来确定排序顺序。

select b.id , b.productid , b.listname , b.color , b.price , a.priority_order
from table1 a 
inner join table2 b on a.name = b.listname 
inner join
(
  select x.priority_order , min(y.price) as min_price
  from table1 x 
  inner join table2 y on x.name = y.listname 
  --where y.productid = 5012
  group by x.priority_order
) m on m.priority_order = a.priority_order
--where b.productid = 5012
order by
  case when a.priority_order > 0 and b.price = m.min_price then 1 else 2 end,
  a.priority_order,
  b.price;

SQL fiddle :http://www.sqlfiddle.com/#!2/2af6a/7 .

编辑:这是一个没有派生表但带有exists子句的替代方案。这可能被认为更具可读性，但当结果集很大时通常会更慢，因为需要对每个记录进行查找选择。

select b.id , b.productid , b.listname , b.color , b.price , a.priority_order
from table1 a 
inner join table2 b on a.name = b.listname 
--where b.productid = 5012
order by
  case when a.priority_order = 0 or exists
  (
    select *
    from table1 x 
    inner join table2 y on x.name = y.listname
    where x.priority_order = a.priority_order
    and y.price < b.price 
  ) then 2 else 1 end,
  a.priority_order,
  b.price;

SQL fiddle :http://www.sqlfiddle.com/#!2/2af6a/5 .