20200417SQL练手

-- 建表语句--

#创建订单库

create database db_order_20200417;
use db_order_20200417;

#创建订单表

create table hy_order_20170410 (id varchar(100),order_money float,memeber_id int,create_time datetime,status int);

-- 插入数据
insert into hy_order_20170410 values ('A001',100.15,1,'2017-01-01 13:23:12',2); 
insert into hy_order_20170410 values ('A001',100.15,1,'2017-01-01 13:23:12',2); 
insert into hy_order_20170410 values ('A002',100.0,2,'2017-04-01 14:23:12',2); 
insert into hy_order_20170410 values ('A003',12.1,3,'2017-01-01 13:23:12',2); 
insert into hy_order_20170410 values ('A004',200.15,4,'2017-01-01 13:23:12',2); 
insert into hy_order_20170410 values ('A005',1200.15,5,'2017-01-01 13:23:12',2); 
insert into hy_order_20170410 values ('A006',0.15,11,'2017-01-01 17:23:12',2); 
insert into hy_order_20170410 values ('A007',1215.1,31,'2017-01-01 10:23:12',2); 
insert into hy_order_20170410 values ('A008',100.75,15,'2017-01-01 14:23:12',2); 
insert into hy_order_20170410 values ('A009',100.15,8,'2017-01-01 15:23:12',2); 
insert into hy_order_20170410 values ('A010',20.15,9,'2017-01-01 16:23:12',2); 
insert into hy_order_20170410 values ('A011',110.15,21,'2017-01-01 14:23:12',2); 
insert into hy_order_20170410 values ('A012',1.15,13,'2017-01-01 13:03:12',2); 
insert into hy_order_20170410 values ('A013',20.15,14,'2017-02-01 13:53:12',2); 
insert into hy_order_20170410 values ('A014',30.15,15,'2017-03-01 13:23:12',2); 
insert into hy_order_20170410 values ('A015',40.5,13,'2017-01-01 13:23:12',2); 
insert into hy_order_20170410 values ('A016',65.0,1,'2016-01-01 13:23:12',2); 
insert into hy_order_20170410 values ('A017',78.15,1,'2016-03-01 09:23:12',1);
insert into hy_order_20170410 values ('A018',88.15,1,'2016-11-01 20:23:12',2); 
insert into hy_order_20170410 values ('A019',99.0,12,'2017-02-11 13:23:12',1); 
insert into hy_order_20170410 values ('A020',10.3,13,'2017-04-01 13:23:12',2); 

#创建订单明细表

create table hy_orderitem_20170410 (id varchar(100),order_id varchar(100),item_id int,item_name varchar(200), price float,item_num int);



insert into hy_orderitem_20170410 values('AB001','A001',1001,'A',0.15,1);
insert into hy_orderitem_20170410 values('AB002','A001',1002,'B',20,1);
insert into hy_orderitem_20170410 values('AB003','A001',1003,'C',40,2);
insert into hy_orderitem_20170410 values('AB004','A005',1001,'A',0.15,1);
insert into hy_orderitem_20170410 values('AB005','A005',1004,'D',200,6);
insert into hy_orderitem_20170410 values('AB0015','A015',1005,'E',40.5,1);
insert into hy_orderitem_20170410 values('AB0016','A019',1006,'F',50,1);
insert into hy_orderitem_20170410 values('AB0017','A019',1007,'G',24.5,2);
insert into hy_orderitem_20170410 values('AB0018','A020',1008,'H',10.3,1);

-- 练习代码-- 

-- 熟悉数据
SELECT * FROM hy_order_20170410 -- LIMIT 3

# 从订单表中查询：

-- 最大订单金额

SELECT MAX(order_money) FROM hy_order_20170410  

-- 统计累计金额 累计购买订单数 累计会员数 平均客单价

SELECT  

sum(order_money),
count(id),
count(DISTINCT memeber_id),
sum(order_money)/count(DISTINCT memeber_id) 客单价
FROM hy_order_20170410;

-- 统计每位会员：累计金额 累计购买订单数，并按累计金额做降序

SELECT
memeber_id,
SUM(order_money) AS sum_total,
COUNT(id)
FROM hy_order_20170410
GROUP BY memeber_id
ORDER BY sum_total desc;

-- 统计每位会员第一次和最后一次的购买时间
SELECT 
memeber_id,
min(create_time) as firsttime_buy,
max(create_time) as lasttime_buy
FROM hy_order_20170410
GROUP BY memeber_id
ORDER BY memeber_id ASC;

-- 按订单状态统计：累计订单金额，累计会员数，累计订单量

SELECT 
`status`,
sum(order_money),
count(DISTINCT memeber_id),
count(id)
FROM hy_order_20170410
GROUP BY `status`

# 从订单明细表中查询：

-- 统计每个订单中购买的商品数量
SELECT 
order_id,
sum(item_num) AS total_num
FROM hy_orderitem_20170410
GROUP BY order_id;

-- 按商品名称统计每个商品的销售数量
SELECT 
item_name,
sum(item_num) 销售数量
FROM hy_orderitem_20170410
GROUP BY item_name;

-- 按商品名称统计每个商品产生的订单数

SELECT 
item_name,
count(order_id) 订单数
FROM hy_orderitem_20170410
GROUP BY item_name;

-- 按商品名称统计每个商品产生的订单数，同时筛选出订单量大于1的记录

SELECT 
item_name,
count(order_id) 订单数
FROM hy_orderitem_20170410
GROUP BY item_name
HAVING 订单数 > 1;

aishuju3.36

附图：

---

练习题：

3.3.6 对汇总结果进行过滤：having
从订单表中查询：
最大订单金额
统计累计金额 累计购买订单数 累计会员数 客单价
统计每位会员：累计金额 累计购买订单数，并按累计金额做降序
统计每位会员第一次和最后一次的购买时间
按订单状态统计：累计订单金额，累计会员数，累计订单量

从订单明细表中查询：

统计每个订单中购买的商品数量
按商品名称统计每个商品的销售数量
按商品名称统计每个商品产生的订单数
按商品名称统计每个商品产生的订单数，同时筛选出订单量大于1的记录