在UNION查询中组合ORDER BY

how do i combine ORDER BY in UNION query?

如何在UNION查询中组合ORDER BY?

I tried this and got error:

我试过这个并得到错误:

SELECT country.country_name AS res
FROM   countries AS country
WHERE (lower (country.country_name) LIKE '%".$_POST['query']."%')
ORDER BY country.lang = '".$_POST['lang']."'
UNION
    SELECT  sec.loc AS res
    FROM    itin_secs AS sec
    WHERE   sec.loc LIKE '%".$_POST['query']."%'

1 个解决方案

#1

I would recommend:

我建议:

SELECT res
FROM (SELECT c.country_name as res, 1 as priority, c.lang
      FROM countries AS c
      WHERE lower(c.country_name) LIKE '%".$_POST['query']."%')
      UNION ALL  -- recommended instead of `UNION`
      SELECT  sec.loc Ares
      FROM itin_secs as sec, 2, NULL
      WHERE sec.loc LIKE '%".$_POST['query']."%'
    ) x
ORDER BY priority, lang = '".$_POST['lang']."';

This query is quite explicit about the final ordering. It also uses UNION ALL, so the query does not incur the overhead of removing duplicates.

该查询对最终排序非常明确。它还使用UNION ALL,因此查询不会产生删除重复项的开销。

As a secondary issue (I mean, the original query doesn't work), you should parameterize the query. You should really start by writing parameterized queries, so they are natural for anything you want to do.

作为次要问题(我的意思是,原始查询不起作用),您应该参数化查询。您应该从编写参数化查询开始,因此它们对于您想要做的任何事情都很自然。

#1