在这份工会声明中，该把订单放在哪里？
在这份工会声明中，该把订单放在哪里？
提问于 2015-07-19 13:31:51
我得到的这份工会声明似乎在起作用：
SELECT q.id, q.hits, q.type, q.title, q.date, q.author, q.wfurl
FROM (SELECT id AS id, hits AS hits, type AS type, title AS title, date AS date, author AS author, wfurl AS wfurl, status AS status
FROM articles WHERE status = 1
UNION SELECT id, hits, type, title, published, author_id, url, status
FROM new_articles WHERE status = 1) AS q
GROUP BY q.id我试着按类型来排序整个东西，所以ORDER BY type，但是不管我把它放哪，似乎都会出错。我已经把它放在了第一行，在两个选择之后，没有运气。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-07-19 13:37:15
order by会追上group by
SELECT q.id, q.hits, q.type, q.title, q.date, q.author, q.wfurl
FROM ((SELECT id AS id, hits AS hits, type AS type, title AS title, date AS date, author AS author, wfurl AS wfurl, status AS status
FROM articles
WHERE status = 1
) UNION
(SELECT id, hits, type, title, published, author_id, url, status
FROM new_articles
WHERE status = 1
)) q
GROUP BY q.id
ORDER BY type;如果您知道这两个表没有重复项，那么应该使用UNION ALL而不是UNION。UNION通过删除重复项而引起开销。分配与名称相同的表别名也是多余的，因此hits as hits是不必要的(以此类推)。
编辑：
如果您想要高效的查询，下面的查询可能会更快，并且可能会满足您的需要：
select a.*
from articles a
where status = 1
union all
select na.*
from new_articles na
where status = 1 and
not exists (select 1 from articles a where a.id = na.id)
order by type;这消除了union的开销。如果两个表中都有一个id，那么它接受第一个表中的值(您可以反转逻辑的顺序，从第二个表中获取值)。唯一真正的开销是最后的order by，而您的版本有union、group by和order by的开销。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31501760
复制相关文章
相似问题
腾讯云开发者
