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

发布于 2024-12-24 03:48:03 字数 246 浏览 2 评论 0原文

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

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

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

原文

I have this query to search in two SQL tables. I am looking for a way to sort the result by occurrence. This is my query:

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

Which is the best way, to get the results ordered?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

薆情海 2024-12-31 03:48:03

诀窍是首先使用 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

The trick is first to use UNION ALL, which preserves duplicates (ordinary UNION removes duplicates), then select from that result. This query should do it:

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

回复收藏 0 原文

樱娆 2024-12-31 03:48:03

假设 ID 和 Parent_ID 在表中不重复，否则您可以为每个 id 获取 2 行...如果是这样，您是否希望将它们加在一起，那么 Parent_ID 和 ID 相关吗？

Select mID, cnt
FROM 
(SELECT `parent_id` as mID, count(`parent_ID`) as cnt
FROM wp_forum_posts
WHERE text LIKE '%{$term1}%'
Group by `parent_ID`
UNION
SELECT `id`, count(`id`) as cnt
FROM wp_forum_threads
WHERE subject LIKE '%{$term1}%
GROUP BY `id`)
Order by cnt ASC, mID

Assumes ID and parent_ID are not duplicates in tables otherwise you can get 2 rows per an id... and would you want them summed together if so then are parent_ID and ID related?

Select mID, cnt
FROM 
(SELECT `parent_id` as mID, count(`parent_ID`) as cnt
FROM wp_forum_posts
WHERE text LIKE '%{$term1}%'
Group by `parent_ID`
UNION
SELECT `id`, count(`id`) as cnt
FROM wp_forum_threads
WHERE subject LIKE '%{$term1}%
GROUP BY `id`)
Order by cnt ASC, mID

回复收藏 0 原文

~没有更多了~