事先阅读
http://www.w3school.com.cn/sql/sql_union.asp
表1:
CREATE TABLE `A` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`orderno` bigint(20) DEFAULT NULL ,
`userid` varchar(255) DEFAULT NULL,
`resultid` tinyint(11) DEFAULT NULL,
`remark` varchar(255) DEFAULT NULL,
`createtime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`role` int(1) DEFAULT NULL ,
`nickname` varchar(20) DEFAULT NULL,
`headimgurl` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=756 DEFAULT CHARSET=utf8;
表2:
CREATE TABLE `B` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`orderno` bigint(20) NOT NULL ,
`payamount` decimal(12,2) DEFAULT NULL,
`createdat` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`checkuserid` varchar(32) NOT NULL ,
`picurls` text ,
`status` tinyint(2) NOT NULL ,
`productid` varchar(32) DEFAULT NULL,
`checktype` tinyint(2) NOT NULL,
`detail` varchar(255) DEFAULT NULL,
`newuserid` int(11) NOT NULL,
`nickname` varchar(200) DEFAULT NULL,
`mobile` varchar(32) DEFAULT NULL ,
`productname` varchar(255) DEFAULT NULL,
`remark` varchar(255) DEFAULT NULL ,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
需求:
1.获取两张表的orderno
2.按时间排序
2.区分是从哪张表中获取的orderno
3.时间限定在3个月内,
4.进行分页。
sql
SELECT orderno,resource from
(select DISTINCT orderno,createtime as time,1 as resource
from A where userid =666
UNION
SELECT orderno,createdat as time,0 as resource
from B where checkuserid = 666)
as a where time>=DATE_SUB(SYSDATE(),INTERVAL 3 MONTH) ORDER BY time DESC LIMIT 0,20
解释![](https://img-blog.csdnimg.cn/20181213100737738.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hnbGZz,size_16,color_FFFFFF,t_70)