我想从与UNION连接的两个不同表中对数据进行排序,排序参数必须来自第三个表(drivers>queno)
如果我从一个表中排序数据,我将使用以下代码(工作正常):

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

所以当我加入两张桌子的时候,我试着用这个没有成功。。。
  (
 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)

我需要做什么才能让它工作?提前谢谢你。

最佳答案

您还需要从各个Select查询中获取queno列。
请尝试以下操作:

(
 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)

10-08 04:42