Mysql 多張無關(guān)聯(lián)表查詢數(shù)據(jù)并分頁功能需求

在三張沒有主外鍵關(guān)聯(lián)的表中取出自己想要的數(shù)據(jù)，并且分頁。

數(shù)據(jù)庫表結(jié)構(gòu)

水果表：

堅(jiān)果表：

飲料表：

數(shù)據(jù)庫隨便建的，重在方法。

主要使用UNION ALL 操作符

UNION ALL 操作符用于合并兩個(gè)或多個(gè) SELECT 語句的結(jié)果集。

請注意，UNION ALL內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時(shí)，每條 SELECT 語句中的列的順序必須相同 ; 另外，UNION ALL結(jié)果集中的列名總是等于 UNION ALL中第一個(gè) SELECT 語句中的列名。

// 詳細(xì)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為表的別名最終結(jié)果

mysql多表聯(lián)合查詢時(shí)出現(xiàn)的分頁問題的解決mysql一對多分頁問題

部門表：tbl_dept

員工表：tbl_emp

數(shù)據(jù)庫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,’技術(shù)部’),(2,’業(yè)務(wù)部’),(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);

分頁錯(cuò)誤寫法(主查詢員工表)

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

下面代碼與之無關(guān) 僅為備份

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

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持好吧啦網(wǎng)。