mysql 数据查询

伟洪winni 2019-11-14 05:34:22

//成交的订单表
......
order_deal_201909
order_deal_201910
order_deal_201911
//待处理的订单表
order_peding （订单成交后，会将数据迁移到成交表）

现在要按下单时间查询出所有订单状态，这要怎么查询？（我要查所有的表，查询出来后对数据按时间排序，再返回每页条数？）

...全文

182 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

吾日三省贾斯汀 2019-11-15

打赏
举报

分析：订单成交后，会将数据迁移到成交表？？？有两种情况，不知道你的是哪种情况1：订单成交后，数据迁移到"成交表"，但是"待处理表"还保留该成交数据情况2：订单成交后，数据迁移到"成交表"，同时"待处理表"中的对应记录删除如果是情况1的话，那就简单的，说明"待处理表"就是一张数据庞大的表，存放着所有的交易记录，直接查询"待处理表"就行了，SQL语句如下：

select * from TEST.ORDER_PEDING order by CREATE_TIME DESC limit 0,6;

这个太简单了，我猜，你的是情况2，比如当前是11月份，那么实现如下 一、建表

DROP TABLE IF EXISTS TEST.ORDER_DEAL_201909;
create table TEST.ORDER_DEAL_201909(
	ID VARCHAR(64) primary key not null,
	ORDER_NUM VARCHAR(64),
	CREATE_TIME TIMESTAMP,		-- yyyy-MM-dd hh:mm:ss SSS
	PAYMENT_TIME  TIMESTAMP,	-- yyyy-MM-dd hh:mm:ss SSS
	STATUS CHAR(1)				-- 0-已付款,1-待付款
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

insert into TEST.ORDER_DEAL_201909(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID0901','OU0901','2019-09-09 01:01:01','2019-09-09 01:11:11','0');
insert into TEST.ORDER_DEAL_201909(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID0902','OU0902','2019-09-09 02:02:02','2019-09-09 02:12:12','0');
insert into TEST.ORDER_DEAL_201909(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID0903','OU0903','2019-09-09 03:03:03','2019-09-09 03:13:13','0');
insert into TEST.ORDER_DEAL_201909(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID0904','OU0904','2019-09-09 04:04:04','2019-09-09 04:14:14','0');
insert into TEST.ORDER_DEAL_201909(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID0905','OU0905','2019-09-09 05:05:05','2019-09-09 05:15:15','0');


DROP TABLE IF EXISTS TEST.ORDER_DEAL_201910;
create table TEST.ORDER_DEAL_201910(
	ID VARCHAR(64) primary key not null,
	ORDER_NUM VARCHAR(64),
	CREATE_TIME TIMESTAMP,		-- yyyy-MM-dd hh:mm:ss SSS
	PAYMENT_TIME  TIMESTAMP,	-- yyyy-MM-dd hh:mm:ss SSS
	STATUS CHAR(1)				-- 0-已付款,1-待付款
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

insert into TEST.ORDER_DEAL_201910(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1001','OU1001','2019-10-10 01:01:01','2019-10-10 01:11:11','0');
insert into TEST.ORDER_DEAL_201910(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1002','OU1002','2019-10-10 02:02:02','2019-10-10 02:12:12','0');
insert into TEST.ORDER_DEAL_201910(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1003','OU1003','2019-10-10 03:03:03','2019-10-10 03:13:13','0');
insert into TEST.ORDER_DEAL_201910(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1004','OU1004','2019-10-10 04:04:04','2019-10-10 04:14:14','0');
insert into TEST.ORDER_DEAL_201910(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1005','OU1005','2019-10-10 05:05:05','2019-10-10 05:15:15','0');


DROP TABLE IF EXISTS TEST.ORDER_DEAL_201911;
create table TEST.ORDER_DEAL_201911(
	ID VARCHAR(64) primary key not null,
	ORDER_NUM VARCHAR(64),
	CREATE_TIME TIMESTAMP,		-- yyyy-MM-dd hh:mm:ss SSS
	PAYMENT_TIME  TIMESTAMP,	-- yyyy-MM-dd hh:mm:ss SSS
	STATUS CHAR(1)				-- 0-已付款,1-待付款
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

insert into TEST.ORDER_DEAL_201911(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1101','OU1101','2019-11-11 01:01:01','2019-11-11 01:11:11','0');
insert into TEST.ORDER_DEAL_201911(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1102','OU1102','2019-11-11 02:02:02','2019-11-11 02:12:12','0');
insert into TEST.ORDER_DEAL_201911(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1103','OU1103','2019-11-11 03:03:03','2019-11-11 03:13:13','0');
insert into TEST.ORDER_DEAL_201911(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1104','OU1104','2019-11-11 04:04:04','2019-11-11 04:14:14','0');
insert into TEST.ORDER_DEAL_201911(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1105','OU1105','2019-11-11 05:05:05','2019-11-11 05:15:15','0');


DROP TABLE IF EXISTS TEST.ORDER_PEDING;
create table TEST.ORDER_PEDING(
	ID VARCHAR(64) primary key not null,
	ORDER_NUM VARCHAR(64),
	CREATE_TIME TIMESTAMP,		-- yyyy-MM-dd hh:mm:ss SSS
	PAYMENT_TIME  TIMESTAMP,	-- yyyy-MM-dd hh:mm:ss SSS
	STATUS CHAR(1)				-- 0-已付款,1-待付款
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

insert into TEST.ORDER_PEDING(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1106','OU1106','2019-11-11 11:01:01','2019-11-11 11:11:11','1');
insert into TEST.ORDER_PEDING(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1107','OU1107','2019-11-11 12:02:02','2019-11-11 12:12:12','1');
insert into TEST.ORDER_PEDING(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1108','OU1108','2019-11-11 13:03:03','2019-11-11 13:13:13','1');
insert into TEST.ORDER_PEDING(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1109','OU1109','2019-11-11 14:04:04','2019-11-11 14:14:14','1');
insert into TEST.ORDER_PEDING(ID,ORDER_NUM,CREATE_TIME,PAYMENT_TIME,STATUS) values('ID1110','OU1110','2019-11-11 15:05:05','2019-11-11 15:15:15','1');

二、实现 三张成交表是按月份来划分的，业务上肯定是不会有重复的记录，一张待处理表，存放是待处理的订单，有可能是当前月或者更前面月份的，前面分析说了，迁移数据后"待处理表"的记录会被删除，所以"待处理表"也不会跟其他三张成交表有记录重复。此时要查询所有交易信息，就相当于查询三张成交表+一张待处理表ORDER_PEDING的所有交易信息，根据下单时间（CREATE_TIME）排序，一般是最新订单在前面（降序DESC） SQL语句：

select allorder.* FROM
(
select * from TEST.ORDER_PEDING
UNION
select * from TEST.ORDER_DEAL_201909
UNION
select * from TEST.ORDER_DEAL_201910
UNION
select * from TEST.ORDER_DEAL_201911
) allorder
order by CREATE_TIME DESC limit 0,6;	-- limit第一行从0开始去，后面表示取多少行

三、总结 关键词1：UNION 合并这里这里你的业务能保证没有重复的记录，UNION也可以用 UNION ALL，订单数据量大的话查询效率更快，关键词2：limit 分页 MySQL分页limit https://blog.csdn.net/JustinQin/article/details/77861867

伟洪winni 2019-11-14