Mysql 如何实现多张无关联表查询数据并分页

在三张没有主外键关联的表中取出自己想要的数据，并且分页。

水果表：

坚果表：

饮料表：

数据库随便建的，重在方法。

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为表的别名最终结果

部门表：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 eLEFT JOIN tbl_dept d ON d.dept_id = e.d_id LIMIT 1,10

使用子查询方式解决问题

SELECT* FROM ( SELECT *FROMtbl_emp eLEFT JOIN tbl_dept dON d.dept_id = e.d_idGROUP BY e.d_idLIMIT 1,10 ) e LEFT JOIN tbl_dept dON d.dept_id = e.d_id

下面代码与之无关 仅为备份

SELECTft.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 ( SELECTf.id,f.partner_id ,f.code ,f.end_update_date ,f.name ,f.type ,f.area ,f.is_default ,f.is_del,f.create_dateFROMbus_freight_template fLEFT JOIN bus_freight_provice pON f.id = p.template_idWHERE f.code = p.codeAND f.code = #[code]GROUP BY f.idLIMIT #{startPage},#{pageSize} ) ft LEFT JOIN bus_freight_provice fpON 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

以上为个人经验，希望能给大家一个参考，也希望大家多多支持好吧啦网。