請(qǐng)注意，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為表的別名
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);
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
