问题:请设计一个关于用户的订单列表
1.查询用户名为张三的用户(id="TianMao1003")的所有未支付的订单信息,并且按照添加时间排序。
2.针对问题1,进行分页
3.针对问题1,进行分类,也就是该商品属于那种属性
(就是简单的问题,字段属性信息没有认真的分析)
综合三个问题(用户信息表除外)
首先得有一个分类表
id type_name type_id
1 衣服 0
2 配饰 0
3 上衣 1
4 裤子 1
5 袜子 1
6 内衣 1
7 项链 2
8 手镯 2
9 钻戒 2
10 耳环 2
商品信息表
id goods_id goods_name type_id kucun shop_id
1 1001 艾玛夹克 3 1000 201701021213
2 1002 艾玛秋裤 4 1200 201701021213
3 1003 艾玛袜子 5 1200 201701021213
4 1004 艾玛内衣 6 1200 201701021213
5 1005 艾玛项链 7 1200 201701021213
6 1006 艾玛手镯 8 1200 201701021213
7 1007 艾玛毛裤 4 1200 201701021213
8 1008 艾玛皮裤 4 1200 201701021213
用户订单表
id order_num goods_id user_id add_time pay_time pay_state
1 201802031213 1002 TianMao1003 201802031213 201802031259 1
1 201802031215 1004 TianMao1003 201802031215 201802031259 1
1 201802031216 1004 TianMao1003 201802031216 0
1 201802031217 1002 TianMao1003 201802031217 201802031259 1
1 201802031218 1007 TianMao1003 201802031218 0
CREATE TABLE typer(
id TINYINT PRIMARY KEY AUTO_INCREMENT,
typer_name VARCHAR(20) NOT NULL,
typer_id TINYINT NOT NULL,
UNIQUE(typer_name),
INDEX(typer_id)
);
INSERT INTO typer VALUES(1,'衣服',0);
INSERT INTO typer VALUES(2,'配饰',0);
INSERT INTO typer VALUES(3,'上衣',1);
INSERT INTO typer VALUES(4,'裤子',1);
INSERT INTO typer VALUES(5,'袜子',1);
INSERT INTO typer VALUES(6,'内衣',1);
INSERT INTO typer VALUES(7,'项链',2);
INSERT INTO typer VALUES(8,'手镯',2);
INSERT INTO typer VALUES(9,'钻戒',2);
CREATE TABLE goods(
id TINYINT PRIMARY KEY AUTO_INCREMENT,
goods_id INT NOT NULL,
goods_name VARCHAR(50) NOT NULL,
typer_id TINYINT NOT NULL,
kucun INT NOT NULL,
shop_id INT NOT NULL,
UNIQUE(goods_id),
INDEX(goods_name),
INDEX(typer_id),
INDEX(shop_id));
DROP TABLE goods;
INSERT INTO goods VALUES(1,1001,'艾玛夹克',3,1000,20171231);
INSERT INTO goods VALUES(2,1002,'艾玛秋裤',4,1000,20171232);
INSERT INTO goods VALUES(3,1003,'艾玛袜子',5,1000,20171233);
INSERT INTO goods VALUES(4,1004,'艾玛内衣',6,1000,20171234);
INSERT INTO goods VALUES(5,1005,'艾玛项链',7,1000,20171235);
INSERT INTO goods VALUES(6,1006,'艾玛手镯',8,1000,20171236);
INSERT INTO goods VALUES(7,1007,'艾玛毛裤',4,1000,20171237);
INSERT INTO goods VALUES(8,1008,'艾玛皮裤',4,1000,20171238);
CREATE TABLE my_order(
id TINYINT PRIMARY KEY AUTO_INCREMENT,
order_num INT NOT NULL,
goods_id INT NOT NULL,
user_id VARCHAR(30),
add_time TIMESTAMP NOT NULL,
pay_time TIMESTAMP,
pay_state INT NOT NULL DEFAULT 0,
FOREIGN KEY(goods_id) REFERENCES goods(goods_id),
INDEX(goods_id),
INDEX(user_id)
);my_order
insert into `my_order` (`id`, `order_num`, `goods_id`, `user_id`, `add_time`, `pay_time`, `pay_state`) values('1','201802031213','1002','TianMao1003','2018-10-04 11:24:46','0000-00-00 00:00:00','0');
insert into `my_order` (`id`, `order_num`, `goods_id`, `user_id`, `add_time`, `pay_time`, `pay_state`) values('2','201802031215','1004','TianMao1003','2018-10-04 11:25:44','2018-02-03 14:13:56','1');
insert into `my_order` (`id`, `order_num`, `goods_id`, `user_id`, `add_time`, `pay_time`, `pay_state`) values('3','201802031216','1004','TianMao1003','2018-10-04 11:26:05','0000-00-00 00:00:00','0');
insert into `my_order` (`id`, `order_num`, `goods_id`, `user_id`, `add_time`, `pay_time`, `pay_state`) values('4','201802031019','1004','TianMao1003','2018-10-04 11:27:38','0000-00-00 00:00:00','0');
##查询
SELECT * FROM my_order WHERE user_id = 'TianMao1003' AND pay_state=0 ORDER BY add_time DESC;
##分页
SELECT * FROM my_order WHERE user_id = 'TianMao1003' AND pay_state=0 ORDER BY add_time DESC limit 0,1;
## 查询指定用户的未支付的商品所属的分类(先是订单跟商品表根据商品id左连接得到用户名,商品名称,分类的编号,把结果作为一张新表在跟分类表连接得到 最终的结果)
SELECT pttable.muid as '用户名',pttable.gtname AS '商品分类' , typer.typer_name AS '所属分类' FROM (SELECT my_order.user_id as muid,my_order.goods_id AS mgid ,goods.goods_name
AS gtname,goods.typer_id AS gtid FROM my_order LEFT JOIN goods ON my_order.goods_id = goods.goods_id
WHERE my_order.user_id='TianMao1003' AND pay_state=0) AS pttable LEFT JOIN typer ON pttable.gtid = typer.id;
结果: