mysql - 'order clause' 中的未知列

Question

我正在使用这个 sql 请求:

SET @C := 0;

SELECT _s.*, @C := @C + 1 AS `number` FROM `server` _s
INNER JOIN servers_services _ss ON (_s.id = _ss.server_id)
INNER JOIN service _s2 ON (_ss.service_id = _s2.id)
WHERE _s.is_vip != 0 AND _s.is_hidden != 1 AND _s.is_banned != 1

UNION

SELECT _s.*, @C := @C + 1 AS `number` FROM `server` _s
INNER JOIN servers_services _ss ON (_s.id = _ss.server_id)
INNER JOIN service _s2 ON (_ss.service_id = _s2.id)
WHERE _s.is_vip != 0 AND _s.is_hidden != 1 AND _s.is_banned != 1

ORDER BY _s2.priority DESC

输出错误:

1054 - Unknown column '_s2.priority' in 'order clause'

Answer 1

你可以这样尝试:

select * from (
SELECT _s.*, @C := @C + 1 AS `number` FROM `server` _s
INNER JOIN servers_services _ss ON (_s.id = _ss.server_id)
INNER JOIN service _s2 ON (_ss.service_id = _s2.id)
WHERE _s.is_vip != 0 AND _s.is_hidden != 1 AND _s.is_banned != 1

UNION

SELECT _s.*, @C := @C + 1 AS `number` FROM `server` _s
INNER JOIN servers_services _ss ON (_s.id = _ss.server_id)
INNER JOIN service _s2 ON (_ss.service_id = _s2.id)
WHERE _s.is_vip != 0 AND _s.is_hidden != 1 AND _s.is_banned != 1
) t

ORDER BY priority DESC