mysql - 在两个表中搜索并按术语的出现顺序排序

Question

我有这个查询要在两个 SQL 表中搜索。我正在寻找一种按出现次数对结果进行排序的方法。这是我的查询:

SELECT `parent_id`
FROM wp_forum_posts
WHERE text LIKE '%{$term1}%'
UNION
SELECT `id`
FROM wp_forum_threads
WHERE subject LIKE '%{$term1}%

获得结果排序的最佳方式是什么？

Answer 1

技巧是首先使用 UNION ALL，它保留重复项(普通 UNION 删除重复项)，然后从结果中进行选择。这个查询应该可以做到:

select * from (
    select parent_id as mID, count(*) as cnt
    from wp_forum_posts
    where text like '%{$term1}%'
    group by 1
  UNION ALL
    select id, count(*)
    FROM wp_forum_threads
    where subject like '%{$term1}%
    group by 1) x
order by 2, 1