mysql 多表排序_mysql多个不同数据表组成列表并分页排序

需求：有时候根据需求我们需要把多张表的数据组合在一起显示在一个列表中，并且可以对其进行分页、排序等。之前看到这个需求我的第一想法就是每个不同的表数据查询数来再用数组合并，然后对数组进行分页排序等操作。这样虽然可以实现，但效率低下，需要些大量的代码。经过查资料我们引入了SQL UNION 和 UNION ALL 操作符，下面我们来看一下具体的实现方法

关键词解释：UNION 和 UNION ALL 合并两个或多个 SELECT 语句的结果集。不同的是UNION 命令只会选取不同的值，UNION ALL 命令会列出所有的值

具体操作：下面说的是表字段不同的情况下，字段相同同理

建两个数据表并插入数据

CREATE TABLE `member` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`true_name` varchar(12) NOT NULL DEFAULT '' COMMENT '真实姓名',

`insert_time` char(11) NOT NULL DEFAULT '' COMMENT '加入时间',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员表';

INSERT INTO `member` VALUES

(1,'小舞','1544066770'),

(2,'胖子','1544066825'),

(3,'荣荣','1544066869');

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(12) NOT NULL DEFAULT '' COMMENT '姓名',

`reg_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

INSERT INTO `user` VALUES

(1,'小三','2018-12-06 11:25:36'),

(2,'戴老大','2018-12-06 11:26:38'),

(3,'香肠叔叔','2018-12-06 11:27:29'),

(4,'胸大大','2018-12-06 11:28:26');

使用UNION 或 UNION ALL写出sql并执行

SELECT * FROM (

( SELECT `id`,`name`,`reg_datetime`,"user" table_name FROM `user` )

UNION ALL

( SELECT `id`,`true_name`,FROM_UNIXTIME(insert_time,"%Y年%m月%d %H:%i:%s"),"member" table_name FROM `member` )

) m

ORDER BY m.reg_datetime desc

获得结果数组

array(7) {

[0] => array(4) {

["id"] => int(3)

["name"] => string(6) "荣荣"

["reg_datetime"] => string(23) "2018年12月06 11:27:49"

["table_name"] => string(6) "member"

}

[1] => array(4) {

["id"] => int(2)

["name"] => string(6) "胖子"

["reg_datetime"] => string(23) "2018年12月06 11:27:05"

["table_name"] => string(6) "member"

}

[2] => array(4) {

["id"] => int(1)

["name"] => string(6) "小舞"

["reg_datetime"] => string(23) "2018年12月06 11:26:10"

["table_name"] => string(6) "member"

}

[3] => array(4) {

["id"] => int(4)

["name"] => string(9) "胸大大"

["reg_datetime"] => string(19) "2018-12-06 11:28:26"

["table_name"] => string(4) "user"

}

[4] => array(4) {

["id"] => int(3)

["name"] => string(12) "香肠叔叔"

["reg_datetime"] => string(19) "2018-12-06 11:27:29"

["table_name"] => string(4) "user"

}

[5] => array(4) {

["id"] => int(2)

["name"] => string(9) "戴老大"

["reg_datetime"] => string(19) "2018-12-06 11:26:38"

["table_name"] => string(4) "user"

}

[6] => array(4) {

["id"] => int(1)

["name"] => string(6) "小三"

["reg_datetime"] => string(19) "2018-12-06 11:25:36"

["table_name"] => string(4) "user"

}

}

由以上结果我们看到不同字段名使用的是第一张表中的字段名称，而且是顺序一一对应的，我们使用where、order by的时候用的字段对应使用第一张表的，如果使用的是第二张表的则会出错(字段不存在)，可以看到为了排序，两张表的时间格式我们转换成一样的了，并且附上了表名作以区分(列表跳转其他页面比如详情页会用到)。

另外附上tp5查询方式

$user_sql = db('user')->field('id,name,reg_datetime,"user" table_name')->buildSql();

$member_sql = db('member')->field('id,true_name, FROM_UNIXTIME(insert_time,"%Y年%m月%d %H:%i:%s"),"member" table_name')->buildSql();

$list = Db::table('('.$user_sql.' UNION ALL '.$member_sql.') m')->order('m.reg_datetime desc')->select();