mysql 多表查询分页_解决 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