本篇文章小编给大家分享一下Mysql实现多张无关联表查询数据并分页代码示例,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
Mysql 多张无关联表查询数据并分页
功能需求
在三张没有主外键关联的表中取出自己想要的数据,并且分页。
数据库表结构
水果表:
坚果表:
饮料表:
数据库随便建的,重在方法。
主要使用UNION ALL 操作符
UNION ALL 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION ALL内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同 ; 另外,UNION ALL结果集中的列名总是等于 UNION ALL中第一个 SELECT 语句中的列名。
// 详细sql语句 select * FROM ( (select fid,fname,price,type from fruits) UNION ALL (select nid,name,price,6 as type from nut) UNION ALL (select did,dname,price,7 as type from drinks) ) as fnd limit 0,10 -----fnd为表的别名
最终结果
mysql多表联合查询时出现的分页问题的解决
mysql一对多分页问题
部门表:tbl_dept
员工表:tbl_emp
数据库sql文件
CREATE DATABASE /*!32312 IF NOT EXISTS*/`ssm-crud` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `ssm-crud`; /*Table structure for table `tbl_dept` */ DROP TABLE IF EXISTS `tbl_dept`; CREATE TABLE `tbl_dept` ( `dept_id` int(11) NOT NULL AUTO_INCREMENT, `dept_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; /*Data for the table `tbl_dept` */ insert into `tbl_dept`(`dept_id`,`dept_name`) values (1,'技术部'), (2,'业务部'), (6,'销售部'), (7,'人事部'); /*Table structure for table `tbl_emp` */ DROP TABLE IF EXISTS `tbl_emp`; CREATE TABLE `tbl_emp` ( `emp_id` int(11) NOT NULL AUTO_INCREMENT, `emp_name` varchar(255) DEFAULT NULL, `emp_gender` char(1) DEFAULT NULL, `emp_email` varchar(255) DEFAULT NULL, `d_id` int(11) DEFAULT NULL, PRIMARY KEY (`emp_id`), KEY `FK_tbl_emp` (`d_id`), CONSTRAINT `FK_tbl_emp` FOREIGN KEY (`d_id`) REFERENCES `tbl_dept` (`dept_id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8; /*Data for the table `tbl_emp` */ insert into `tbl_emp`(`emp_id`,`emp_name`,`emp_gender`,`emp_email`,`d_id`) values (1,'xiaoshen','2',NULL,6), (4,'晓明','1',NULL,1), (5,'xiaohong','2',NULL,2), (6,'xiaohei','2',NULL,6), (7,'xiaozhang','1',NULL,1), (8,'xiaogao','1',NULL,1), (9,'xiaohua','1',NULL,1), (10,'xiaoyan','2',NULL,1), (11,'xiaohai','2',NULL,2), (12,'xiaoqiang','1',NULL,6), (13,'xiaoqi','2',NULL,7);
分页错误写法(主查询员工表)
SELECT * FROM tbl_emp e LEFT JOIN tbl_dept d ON d.dept_id = e.d_id LIMIT 1,10
使用子查询方式解决问题
SELECT * FROM ( SELECT * FROM tbl_emp e LEFT JOIN tbl_dept d ON d.dept_id = e.d_id GROUP BY e.d_id LIMIT 1,10 ) e LEFT JOIN tbl_dept d ON d.dept_id = e.d_id
下面代码与之无关 仅为备份
SELECT ft.id, ft.partner_id AS partnerId, ft.code , ft.end_update_date AS endUpdateDate, ft.name , ft.type , ft.area , ft.is_default AS isDefault, fp.id fpId, fp.shop_id AS fpShopId , fp.provice_id AS fpProviceId , fp.provice_name AS fpProviceName , fp.start_num AS fpStartNum , fp.start_fee AS fpStartFee , fp.increase_num AS fpIncreaseNum , fp.increase_fee AS fpIncreaseFee , fp.code AS fpCode , fp.provice_text AS fpProviceText , fp.template_id AS fpTemplateId FROM ( SELECT f.id, f.partner_id , f.code , f.end_update_date , f.name , f.type , f.area , f.is_default , f.is_del, f.create_date FROM bus_freight_template f LEFT JOIN bus_freight_provice p ON f.id = p.template_id WHERE f.code = p.code AND f.code = #[code] GROUP BY f.id LIMIT #{startPage},#{pageSize} ) ft LEFT JOIN bus_freight_provice fp ON ft.id = fp.template_id WHERE ft.code = fp.code AND fp.template_id IS NOT NULL AND ft.code = #[code] AND fp.is_del = '0' AND ft.is_del = '0' order by ft.create_date desc