时间戳

首页 > 解决方案 > mysql - 按第三个表中的值对 2 个表中的数据进行排序

mysql - mysql - 按第三个表中的值对 2 个表中的数据进行排序

问题描述

我想对与 UNION 连接的 2 个不同表中的数据进行排序，排序参数必须来自第三个表。（驱动程序 > queno）

如果我对 1 个表中的数据进行排序，我会使用以下代码（完美运行）：

SELECT quotedb.*
FROM   quotedb
       LEFT JOIN drivers
              ON quotedb.driver = drivers.id
ORDER  BY IF(queno = ''
              OR queno IS NULL, 1, 0)

因此，当我加入 2 张桌子时，我尝试了这个但没有成功......

  (
 SELECT DISTINCT driver
 FROM            quotedb
 LEFT JOIN       drivers
 ON              quotedb.driver=drivers.id)
UNION ALL
          (
                          SELECT DISTINCT driver
                          FROM            packaging
                          LEFT JOIN       drivers
                          ON              packaging.driver=drivers.id )
ORDER BY
order by  IF(queno = ''
OR        queno IS NULL,1,0)

我需要做什么才能让它工作？先感谢您。

标签： mysqlsorting

解决方案

queno您还需要从各个 Select 查询中获取该列。

尝试以下操作：

(
 SELECT DISTINCT 
   qdb.driver AS driver, 
   d.queno AS queno 
 FROM quotedb AS qdb 
 LEFT JOIN drivers AS d ON qdb.driver = d.id
)
UNION ALL
(
 SELECT DISTINCT 
   p.driver AS driver, 
   d.queno AS queno 
 FROM packaging AS p 
 LEFT JOIN drivers AS d ON p.driver = d.id 
)
ORDER BY 
  (CASE WHEN queno = '' OR queno IS NULL THEN 1 
   ELSE 0
   END)

推荐阅读