我有一个查询,它返回来自用户的所有流帖子(带评论):
SELECT *
FROM (SELECT *
FROM stream_ps
ORDER BY stream_ps_timestamp DESC
LIMIT $offset, $limit) sps
JOIN user_profiles
ON user_id = sps.stream_ps_author_id
LEFT JOIN (SELECT stream_cm_id,
stream_cm_author_id,
stream_id_fk,
stream_ps_id_fk,
stream_cm_text,
stream_cm_timestamp,
first_name as comm_first_name,
last_name as comm_last_name,
facebook_id as comm_fb_id,
picture as comm_picture
FROM stream_cm
JOIN user_profiles
ON user_id = stream_cm_author_id) AS c
ON sps.stream_ps_id = c.stream_ps_id_fk
ORDER BY sps.stream_ps_id DESC, c.stream_cm_id ASC和另一个返回所有论坛帖子的查询:
SELECT qa.*,
user_profiles.*,
n.pid,
Ifnull(n.ans_count, 0) AS ans_count
FROM (SELECT * FROM forum_qa
ORDER BY forum_qa_type,forum_qa_timestamp DESC
LIMIT $offset, $limit) qa
LEFT JOIN user_profiles
ON user_id = qa.forum_qa_author_id
LEFT JOIN (SELECT forum_qa_parent_id AS pid,
COUNT(*) AS ans_count
FROM forum_qa
WHERE forum_qa_parent_id IS NOT NULL
GROUP BY forum_qa_parent_id) AS n
ON qa.forum_qa_id = n.pid
WHERE qa.forum_qa_type = 1
ORDER BY qa.forum_qa_timestamp DESC我想将两者合并到一个查询中,并重新排序结果,以便它们是反向时间顺序(即,时间戳DESC,最近显示在第一位)。
因此,最终结果将显示最新条目的线程,无论是帖子还是论坛。
有没有办法做到这一点?
发布于 2012-02-01 05:36:14
由于表具有不同的字段,如果您确实需要在SQL中进行排序,则可以将两个结果集合并到一个包含这两个结果集的大表中。一些类似的东西
SELECT * FROM (
SELECT col1, col2, null, null, time_stamp FROM table1
UNION
SELECT null, null, colA, colB, time_stamp FROM table2
) entries
ORDER BY time_stamp DESC
LIMIT $offset, $limit发布于 2012-02-01 05:22:22
我想你要找的是UNION。
发布于 2012-02-01 05:22:36
听起来你想要UNION。要使用UNION,您需要确保列匹配;来自文档
UNION用于将多个SELECT语句的结果合并为一个结果集。
第一个SELECT语句中的列名用作返回结果的列名。在每个SELECT语句的相应位置中列出的所选列应该具有相同的数据类型。
基本用法如下
SELECT id, value FROM tableA
UNION
SELECT id, value FROM tableB
ORDER BY id因此,如果您的两个查询具有在类型和顺序上匹配的字段列表,则可以非常简单地将它们放在一起。
https://stackoverflow.com/questions/9087088
复制相似问题