chenpeng3190 2016-07-02 14:06 采纳率: 100%
浏览 6677
已采纳

MYSQL合并两个表的查询结果并排序

已解决,SQL语句如下:

 SELECT
    *
FROM
    (
        (
            SELECT
                post.post_id AS id,
                'P' AS type,
                post.title AS title,
                post_time AS time
            FROM
                `ts_weiba_post` AS `post`
            WHERE
                `post`.`post_uid` = 41
        )
        UNION
            (
                SELECT
                    reply.reply_id AS id,
                    'R' AS type,
                    reply.content AS title,
                    reply.ctime AS time
                FROM
                    `ts_weiba_reply` AS `reply`
                WHERE
                    `reply`.`uid` = 41
            )
    ) AS alldate
ORDER BY
    time DESC

ts_weiba_post是用户发帖表
ts_weiba_reply是用户评论表

问题:
需要将用户的发帖数据和回复数据合并在一起并依据时间排序。
即从ts_weiba_post中查询post_uid等于41的数据和ts_weiba_reply中uid等于41的数据,并合并查询结果后依据ts_weiba_post的post_time和ts_weiba_reply的ctime排序

 /*
Navicat MySQL Data Transfer

Source Server         : 127.0.0.1
Source Server Version : 50547
Source Host           : 127.0.0.1:3306
Source Database       : xiaotiane

Target Server Type    : MYSQL
Target Server Version : 50547
File Encoding         : 65001

Date: 2016-07-02 21:58:15
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for ts_weiba_post
-- ----------------------------
DROP TABLE IF EXISTS `ts_weiba_post`;
CREATE TABLE `ts_weiba_post` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '帖子ID',
  `weiba_id` int(11) NOT NULL COMMENT '所属微吧ID',
  `post_uid` int(11) NOT NULL COMMENT '发表者uid',
  `title` varchar(255) NOT NULL COMMENT '帖子标题',
  `content` text NOT NULL COMMENT '帖子内容',
  `post_time` int(11) NOT NULL COMMENT '发表时间',
  `reply_count` int(10) DEFAULT '0' COMMENT '回复数',
  `read_count` int(10) DEFAULT '0' COMMENT '浏览数',
  `last_reply_uid` int(11) DEFAULT '0' COMMENT '最后回复人',
  `last_reply_time` int(11) DEFAULT '0' COMMENT '最后回复时间',
  `digest` tinyint(1) DEFAULT '0' COMMENT '全局精华 0-否 1-是',
  `top` tinyint(1) DEFAULT '0' COMMENT '置顶帖 0-否 1-吧内 2-全局',
  `lock` tinyint(1) DEFAULT '0' COMMENT '锁帖(不允许回复)0-否 1-是',
  `recommend` tinyint(1) DEFAULT '0' COMMENT '是否设为推荐',
  `recommend_time` int(11) DEFAULT '0' COMMENT '设为推荐的时间',
  `is_del` tinyint(2) DEFAULT '0' COMMENT '是否已删除 0-否 1-是',
  `feed_id` int(11) NOT NULL COMMENT '对应的分享ID',
  `reply_all_count` int(11) NOT NULL DEFAULT '0' COMMENT '全部评论数目',
  `attach` varchar(255) DEFAULT NULL,
  `praise` int(11) DEFAULT '0' COMMENT '喜欢',
  `from` tinyint(2) DEFAULT '0' COMMENT '客户端类型,0:网站;1:手机网页版;2:android;3:iphone',
  `top_time` int(11) DEFAULT NULL,
  `is_index` tinyint(2) DEFAULT '0',
  `index_img` int(11) DEFAULT NULL,
  `is_index_time` int(11) DEFAULT NULL,
  PRIMARY KEY (`post_id`),
  KEY `id_recommend` (`recommend_time`,`weiba_id`,`recommend`) USING BTREE,
  KEY `post_time` (`post_time`,`weiba_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=895 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of ts_weiba_post
-- ----------------------------
INSERT INTO `ts_weiba_post` VALUES ('883', '8', '41', 'title_3', 'content_3', '1467078402', '0', '1', '1', '1467078402', '0', '0', '0', '0', '0', '0', '32', '0', null, '0', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('881', '8', '41', 'title_1', 'content_1', '1467078185', '0', '0', '1', '1467078185', '0', '0', '0', '0', '0', '0', '0', '0', null, '0', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('882', '8', '41', 'title_2', 'content_2', '1467078260', '0', '1', '1', '1467078260', '0', '0', '0', '0', '0', '0', '31', '0', null, '0', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('885', '9', '41', 'title_4', 'content_4', '1467078496', '0', '1', '1', '1467078496', '0', '0', '0', '0', '0', '0', '33', '0', null, '0', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('886', '9', '41', 'title_5', 'content_5', '1467078606', '0', '6', '1', '1467078606', '0', '0', '0', '0', '0', '0', '34', '0', null, '1', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('887', '10', '41', 'title_6', 'content_6', '1467078692', '0', '1', '1', '1467078692', '0', '0', '0', '0', '0', '0', '35', '0', null, '0', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('888', '10', '41', 'title_7', 'content_7', '1467078911', '2', '8', '578', '1467083920', '0', '0', '0', '0', '0', '0', '36', '2', null, '0', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('889', '10', '41', 'title_8', 'content_8', '1467078989', '0', '6', '1', '1467078989', '0', '0', '0', '0', '0', '0', '37', '0', null, '0', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('890', '5', '232', 'title_9', 'content_9', '1467084362', '0', '4', '232', '1467084362', '0', '0', '0', '0', '0', '0', '38', '0', null, '0', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('891', '3', '579', 'title_10', 'content_10', '1467084380', '0', '5', '579', '1467084380', '0', '0', '0', '0', '0', '0', '39', '0', null, '0', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('892', '6', '232', 'title_11', 'content_11', '1467084538', '0', '8', '232', '1467084538', '0', '0', '0', '0', '0', '0', '40', '0', null, '0', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('893', '3', '536', 'title_12', 'content_12', '1467090574', '3', '18', '579', '1467091548', '0', '0', '0', '0', '0', '0', '41', '3', null, '1', '0', null, '0', null, null);
INSERT INTO `ts_weiba_post` VALUES ('894', '3', '41', 'title_13', 'content_13', '1467464403', '0', '1', '41', '1467464403', '0', '0', '0', '0', '0', '0', '42', '0', null, '0', '0', null, '0', null, null);

-- ----------------------------
-- Table structure for ts_weiba_reply
-- ----------------------------
DROP TABLE IF EXISTS `ts_weiba_reply`;
CREATE TABLE `ts_weiba_reply` (
  `reply_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '回复ID',
  `weiba_id` int(11) NOT NULL COMMENT '所属微吧',
  `post_id` int(11) NOT NULL COMMENT '所属帖子ID',
  `post_uid` int(11) NOT NULL COMMENT '帖子作者UID',
  `uid` int(11) NOT NULL COMMENT '回复者ID',
  `to_reply_id` int(11) NOT NULL DEFAULT '0' COMMENT '回复的评论id',
  `to_uid` int(11) NOT NULL DEFAULT '0' COMMENT '被回复的评论的作者的uid',
  `ctime` int(11) NOT NULL COMMENT '回复时间',
  `content` text NOT NULL COMMENT '回复内容',
  `is_del` tinyint(2) DEFAULT '0' COMMENT '是否已删除 0-否 1-是',
  `comment_id` int(11) NOT NULL COMMENT '对应的分享评论ID',
  `storey` int(11) NOT NULL DEFAULT '0' COMMENT '绝对楼层',
  `attach_id` int(11) NOT NULL,
  `digg_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`reply_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2333 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of ts_weiba_reply
-- ----------------------------
INSERT INTO `ts_weiba_reply` VALUES ('1', '1', '1', '1', '1', '0', '0', '1464331112', '123123123', '0', '1', '0', '0', '1');
INSERT INTO `ts_weiba_reply` VALUES ('5', '1', '25', '14', '1', '0', '0', '1464630201', 'lalala[bizui]', '0', '5', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('6', '1', '25', '14', '1', '0', '0', '1464630224', '111', '0', '6', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('12', '6', '702', '1', '579', '0', '0', '1464774363', '?', '0', '12', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('11', '6', '705', '1', '578', '0', '0', '1464766121', '[ciya]', '0', '11', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('13', '2', '718', '579', '1', '0', '0', '1464859276', '121212', '0', '13', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('14', '5', '747', '12', '12', '0', '0', '1467082511', '发布会已经成功举办', '0', '14', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('15', '2', '58', '30', '14', '0', '0', '1467082621', '[ciya]', '0', '15', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('16', '2', '58', '30', '232', '0', '0', '1467083224', '[guzhang]', '0', '16', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('17', '5', '749', '12', '578', '0', '0', '1467083594', '[qiang]', '0', '17', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('18', '10', '888', '1', '578', '0', '0', '1467083701', '[ciya]', '0', '18', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('22', '2', '58', '30', '578', '0', '0', '1467083878', '[liuhan][liuhan]', '0', '22', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('21', '2', '58', '30', '578', '0', '0', '1467083854', '[huaixiao]', '0', '21', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('23', '10', '888', '1', '578', '0', '0', '1467083920', '[ku][ku]', '0', '23', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('24', '3', '893', '536', '536', '0', '0', '1467091173', '有人在吗', '0', '24', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('25', '3', '893', '536', '536', '0', '0', '1467091306', '有人在吗?', '0', '25', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('26', '3', '893', '536', '579', '0', '0', '1467091548', '[aoman][baiyan]', '0', '26', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('27', '2', '58', '30', '536', '0', '0', '1467092560', '[ciya][ciya][ciya][ciya][ciya]', '0', '27', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('28', '2', '58', '30', '41', '0', '0', '1467265038', '11111111111', '0', '28', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('29', '2', '58', '30', '41', '0', '0', '1467464445', '发布了一条回复1', '0', '29', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('30', '2', '58', '30', '41', '0', '0', '1467464445', '发布了一条回复2', '0', '29', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('31', '2', '58', '30', '41', '0', '0', '1467464445', '发布了一条回复3', '0', '29', '0', '0', '0');
INSERT INTO `ts_weiba_reply` VALUES ('32', '2', '58', '30', '41', '0', '0', '1467464445', '发布了一条回复4', '0', '29', '0', '0', '0');

  • 写回答

3条回答 默认 最新

  • chenpeng3190 2016-07-03 03:02
    关注

    已解决,需要UNION两个表再进行子查询

    SELECT
        *
    FROM
        (
            (
                SELECT
                    post.post_id,
                    post.title AS title,
                    post_time AS time
                FROM
                    `ts_weiba_post` AS `post`
                WHERE
                    `post`.`post_uid` = 41
            )
            UNION
                (
                    SELECT
                        reply.reply_id,
                        reply.content AS title,
                        reply.ctime AS time
                    FROM
                        `ts_weiba_reply` AS `reply`
                    WHERE
                        `reply`.`uid` = 41
                )
        ) AS alldate
    ORDER BY
        time DESC
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题