存在oder表格和order_detail表，其中order_detail.order_id是外键，与order.id关联

order表

id

team_id

pay_user_id

order_status

create_time

update_time

order_detail表

id

order_id

product_type

price

1.查询product_order表中create_time大于2022-06-11 16:15:07的数据，按create_time倒序排列

2.查询product_type=3的全部订单的team_id，查询结果需要按team_id去重

3.按team计算每个team下所有订单的价格之和

4.查询价格最高的订单信息

注意：表名和字段都不能用关键字，不然报语法错误

-- 建表
CREATE TABLE `product_order` (
`id` BIGINT(20) AUTO_INCREMENT NOT NULL COMMENT '主键ID',
`team_id` INT(12) DEFAULT NULL COMMENT '组id',
`pay_user_id` INT(11) DEFAULT NULL COMMENT '支付用户id',
`order_status` VARCHAR(50) DEFAULT NULL COMMENT '状态',
`create_time` TIMESTAMP  DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` TIMESTAMP  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `product_order_detail` (
`id` BIGINT(20) AUTO_INCREMENT NOT NULL COMMENT '主键ID',
`order_id` INT(12) DEFAULT NULL COMMENT '订单id',
`product_type` INT(12) DEFAULT NULL COMMENT '商品类型',
`price` FLOAT(5,2) DEFAULT NULL COMMENT '价格',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


DROP TABLE `product_order_detail`;
DROP TABLE `product_order`;

TRUNCATE TABLE `product_order_detail`;
TRUNCATE TABLE `product_order`;

-- 插入数据
INSERT INTO product_order (team_id,pay_user_id,order_status) VALUES
(5965,18,'支付'),
(5945,28,'未支付'),
(5985,38,'支付'),
(5545,58,'未支付'),
(5745,88,'支付'),
(6945,27,'未支付')
;

INSERT INTO product_order (team_id,pay_user_id,order_status) VALUES
(6965,17,'支付'),
(6945,25,'未支付'),
(6985,35,'支付'),
(5645,68,'未支付')
;

INSERT INTO product_order (team_id,pay_user_id,order_status) VALUES
(7965,97,'支付'),
(7945,96,'未支付')
;
INSERT INTO product_order (team_id,pay_user_id,order_status) VALUES (5965,47,'支付');
INSERT INTO product_order (team_id,pay_user_id,order_status) VALUES (5965,49,'支付');

-- -------------------------

INSERT INTO product_order_detail (order_id,product_type,price) VALUES
(1,3,8.56),
(2,4,7.56)
;
INSERT INTO product_order_detail (order_id,product_type,price) VALUES (3,3,8.56);
INSERT INTO product_order_detail (order_id,product_type,price) VALUES (3,3,8.56);
INSERT INTO product_order_detail (order_id,product_type,price) VALUES (13,4,18.56);
INSERT INTO product_order_detail (order_id,product_type,price) VALUES (14,3,18.46);

-- 执行查询
-- 1.查询product_order表中create_time大于2022-06-11 16:15:07的数据，按create_time倒序排列
SELECT * FROM `product_order` WHERE create_time > '2022-06-11 16:15:07' ORDER BY create_time DESC;
-- 2.查询product_type=3的全部订单的team_id，查询结果需要按team_id去重
SELECT DISTINCT team_id FROM `product_order`,`product_order_detail` WHERE product_type=3 AND `product_order_detail`.order_id = `product_order`.id;
-- 3.按team计算每个team下所有订单的价格之和
SELECT team_id, SUM(price) FROM `product_order`,`product_order_detail` WHERE  `product_order_detail`.order_id = `product_order`.id
GROUP BY team_id;
-- 4.查询价格最高的订单信息
SELECT MAX(price) FROM `product_order_detail`;
SELECT * FROM `product_order_detail` WHERE price=(SELECT MAX(price) FROM `product_order_detail`);