我想从与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)