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,
java代码逻辑
`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,'业务部'),uc browser download
(6,'销售部'),
(7,'⼈事部');
returnfalse什么意思
/*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,
mysql面试题sql语句多表联查ft.end_update_date AS endUpdateDate, ft.name ,
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.provice_text AS fpProviceText ,
FROM
(
SELECT
f.id,
f.partner_id ,
f.name ,
f.area ,
f.is_default ,
f.is_del,
itembay
FROM
bus_freight_template f
LEFT JOIN bus_freight_provice p
ON f.id = p.template_id
de = p.code
de = #{code}
GROUP BY f.id
LIMIT #{startPage},#{pageSize}
) ft
LEFT JOIN bus_freight_provice fp
ON ft.id = fp.template_id
de = fp.code
plate_id IS NOT NULL
de = #{code}
AND fp.is_del = '0'
AND ft.is_del = '0'
order ate_date desc双语教育研究cssci
解决 mysql多表联合查询时出现的分页问题
标签：where  desc  creat  fps  group by  base  exists  The  null 本条技术⽂章来源于互联⽹，如果⽆意侵犯您的权益请点击此处反馈版权投诉