Mysql学习第五天

create table `tb_result` (
  `rq` date not null,
  `sf` char(1) not null
) engine=innodb;

insert into `tb_result` values 
    ('2017-04-09','胜'),
    ('2017-04-09','胜'),
    ('2017-04-09','负'),
    ('2017-04-09','负'),
    ('2017-04-10','胜'),
    ('2017-04-10','负'),
    ('2017-04-10','胜');

-- 用上面的表查询出如下所示的结果。
-- | date       | win  | los  |
-- | ---------- | ---- | ---- |
-- | 2017-04-09 | 2    | 2    |
-- | 2017-04-10 | 2    | 1    |

select 
	rq as `date`,
    sum(case sf when '胜' then 1 else 0 end) as win,
    sum(case sf when '负' then 1 else 0 end) as los
from tb_result
group by rq;

create table `tb_course`
(
`course_id` int unsigned not null comment '课程编号',
`course_name` varchar(50) not null comment '课程名称',
primary key (`course_id`)
) engine=innodb;

insert into `tb_course` values 
    (1, 'Python'),
    (2, 'Java'),
    (3, 'Database'),
    (4, 'JavaScript');

drop table if exists `tb_open_date`;

create table `tb_open_date`
(
`month` char(6) not null comment '开课日期',
`course_id` int unsigned not null comment '课程编号'
) engine=innodb;

insert into `tb_open_date` values
    ('202106', 1),
    ('202106', 3),
    ('202106', 4),
    ('202107', 4),
    ('202108', 2),
    ('202108', 4);

-- 用上面的表查询出如下所示的结果。
-- | course_name | Jun | Jul | Aug |
-- | ----------- | --- | --- | --- |
-- | Python      |  o  |  x  |  x  |
-- | Java        |  x  |  x  |  o  |
-- | Database    |  o  |  x  |  x  |
-- | JavaScript  |  o  |  o  |  o  |

select 
    `course_name`,
    case when exists (
        select 'x' from `tb_open_date` as t2
        where t1.`course_id`=t2.`course_id` and `month`='202106'
    ) then 'o' else 'x' end as Jun, 
    case when exists (
        select 'x' from `tb_open_date` as t2
        where t1.`course_id`=t2.`course_id` and `month`='202107'
    ) then 'o' else 'x' end as Jul, 
    case when exists (
        select 'x' from `tb_open_date` as t2
        where t1.`course_id`=t2.`course_id` and `month`='202108'
    ) then 'o' else 'x' end as Aug
from `tb_course` as t1;

create database homework default charset utf8mb4;

use homework;

create table tb_product
(
prod_id varchar(50) not null comment '商品号',
category varchar(50) not null comment '种类',
color varchar(10) not null comment '颜色',
weight decimal(10, 2) not null comment '重量',
price integer not null comment '价格',
primary key (prod_id)
) engine=innodb comment '产品表';

insert into tb_product values 
    ('prodA', 'cateA', 'yellow', 5.6, 100),
    ('prodB', 'cateB', 'red', 3.7, 200),
    ('prodC', 'cateC', 'blue', 10.3, 300),
    ('prodD', 'cateD', 'black', 7.8, 400);

create table tb_order
(
id integer not null auto_increment,
order_no varchar(20) not null comment '订单号',
user_id varchar(50) not null comment '用户号',
order_date date not null comment '下单日期',
store varchar(50) not null comment '店铺号',
product varchar(50) not null comment '商品号',
quantity integer not null comment '购买数量',
primary key (id)
) engine=innodb comment '订单表';

insert into tb_order 
    (order_no, user_id, order_date, store, product, quantity) 
values 
    ('D001', 'customerA', '2018-01-01', 'storeA', 'prodA', 1),
    ('D001', 'customerA', '2018-01-01', 'storeA', 'prodB', 1),
    ('D001', 'customerA', '2018-01-01', 'storeA', 'prodC', 1),
    ('D002', 'customerB', '2018-01-12', 'storeB', 'prodB', 1),
    ('D002', 'customerB', '2018-01-12', 'storeB', 'prodD', 1),
    ('D003', 'customerC', '2018-01-12', 'storeC', 'prodB', 1),
    ('D003', 'customerC', '2018-01-12', 'storeC', 'prodC', 1),
    ('D003', 'customerC', '2018-01-12', 'storeC', 'prodD', 1),
    ('D004', 'customerA', '2018-01-01', 'storeD', 'prodD', 2),
    ('D005', 'customerB', '2018-01-23', 'storeB', 'prodA', 1);

create table tb_store
(
store_id varchar(50) not null comment '店铺号',
city varchar(20) not null comment '城市',
primary key (store_id)
) engine=innodb comment '店铺表';

insert into tb_store values 
    ('storeA', 'cityA'),
    ('storeB', 'cityA'),
    ('storeC', 'cityB'),
    ('storeD', 'cityC'),
    ('storeE', 'cityD'),
    ('storeF', 'cityB');

-- 查出购买总金额不低于800的用户的用户号、总购买金额、总订单数和总购买商品数，订单号相同的算作一单。
select 
	user_id, 
    sum(price*quantity) as 购买总金额,
    count(distinct order_no) as 总订单数,
    sum(quantity) as 总购买商品数
from tb_order, tb_product 
where product=prod_id 
group by user_id
having 购买总金额>=800;

select 
	user_id,
    sum(price*quantity) as 购买总金额,
    count(distinct order_no) as 总订单数,
    sum(quantity) as 总购买商品数
from (
	select user_id, price, quantity, order_no 
	from tb_order inner join tb_product 
	on product=prod_id
) temp 
group by user_id 
having 购买总金额>=800;

-- 查出所有城市（包含无购买记录的城市）的总店铺数，总购买人数和总购买金额。
select 
	city,
    count(distinct store_id) as 总店铺数,
    count(distinct user_id) as 总购买人数,
    coalesce(sum(price*quantity), 0) as 购买总金额
from tb_store 
left outer join tb_order on store=store_id
left outer join tb_product on product=prod_id
group by city;

-- 查出购买过"cateA"产品的用户和他们的平均订单金额，订单号相同的算作一单。
select 
	user_id,
    sum(price*quantity) / count(distinct order_no) as 平均订单金额
from tb_order, tb_product 
where product=prod_id and user_id in (
	select 
		distinct user_id
	from tb_order, tb_product 
	where product=prod_id and category='cateA'
)
group by user_id;