按行中的两列排序

I have two prices from two tables, which are joined. I need order rows by price from both tables. When first price is zero or null, then take second price, else take first price. So order by part has to be changed.

我有两个表的价格，它们是连在一起的。我需要从这两张桌子上按价格订购行。当第一个价格为零或零时，取第二个价格，否则取第一个价格。所以必须改变顺序。

SQL

SQL

SELECT table1.id
     , table1.price
     , table2.price FROM table1
  JOIN table2  
    ON table1.id = table2.f_id 
 ORDER 
    BY table1.price ASC
     , table2.price ASC

Table1

表1

id / price
1 / 50
2 / 0
3 / NULL
4 / 10

Table2

表二

f_id / price
1 / 60
2 / 30
3 / 5
4 / 100

I expect

我希望

4 个解决方案

#1

You can also use case

您还可以使用case

SELECT table1.id
    CASE WHEN table1.price is null THEN table2.price
         WHEN table1.price = 0 THEN table2.price
         ELSE tabel1.price
    END as my_price
JOIN table2   ON table1.id = table2.f_id 
ORDER BY my_price ASC

#2

ifnull is designed for that purpose (http://www.mysqltutorial.org/mysql-ifnull/):

ifnull就是为此而设计的(http://www.mysqltutorial.org/mysql-ifnull/):

SELECT 
   `table1`.id, 
   ifnull(`table1`.price, `table2`.`price`) as finalPrice 
FROM `table1`
  INNER JOIN `table2` ON table1.id = table2.f_id ORDER BY finalPrice ASC

#3

You can try the below query

您可以尝试下面的查询

SELECT 
    t1.id,
    CASE
        WHEN t1.price is null THEN t2.price
        WHEN t1.price = 0 THEN t2.price
        ELSE t1.price
    END as finalPrice
FROM
    table1 t1, table2 t2
WHERE
    t1.id = t2.f_id
ORDER BY finalPrice

Instead of using inner join, directly compare using t1.id = t2.f_id. It will be faster.

不使用内部连接，直接使用t1进行比较。id = t2.f_id。它将更快。

#4

SELECT table1.id
 , table1.price
 , table2.price FROM table1
JOIN table2  
 ON table1.id = table2.f_id 
ORDER BY
  CASE WHEN table1.price is null THEN table2.price
       WHEN table1.price = 0 THEN table2.price
       ELSE tabel1.price
  END ASC

#1