sql语句练习题

1.创建表：
id,
标题title, 100字内,
公司f_id, 100以内数字，
类型f_type进口1， 出口2,
金额money，2位小数，
时间date；
并插入几条数据。

create table t_order(id int unsigned primary key auto_increment not null,
					title varchar(100) default '',
					f_id tinyint unsigned,
					f_type enum("1", "2"),
					money decimal(10, 2),
					date date);

insert into t_order(title, f_id, f_type, money, date) values
						  ('a', 5, "1", 100.11, "2020-8-31"),
						  ('b', 5, "2", 200.22, "2020-8-30"),
						  ('c', 6, "1", 300.33, "2020-8-31"),
						  ('d', 6, "2", 400.44, "2020-7-20"),
						  ('e', 7, "1", 500.55, "2020-6-20");

2. 查询最近7天每个公司的进出口总金额。

select f_id, group_concat(date), group_concat(money), sum(money) from t_order where DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date group by f_id;

3. 按月查询每个公司进口总金额，出口总金额。

方法一. 
select f_id, DATE_FORMAT(date,'%Y-%m') month, group_concat(date), group_concat(money), sum(money), f_type from t_order where f_type='1' group by f_id, month
union
select f_id, DATE_FORMAT(date,'%Y-%m') month, group_concat(date), group_concat(money), sum(money), f_type from t_order where f_type='2' group by f_id, month order by f_id, month;
方法二
select f_id, DATE_FORMAT(date,'%Y-%m') month, group_concat(date), group_concat(money), sum(money), f_type from t_order group by f_id, month, f_type order by f_id, month;

指定月份查询每个公司进口总金额，出口总金额：

方法一
select f_id, group_concat(date), group_concat(money), sum(money), f_type from t_order where f_type='1' and DATE_FORMAT(date,'%Y-%m')='2020-08' group by f_id
union
select f_id, group_concat(date), group_concat(money), sum(money), f_type from t_order where f_type='2' and DATE_FORMAT(date,'%Y-%m')='2020-08' group by f_id order by f_id;
方法二
select f_id, group_concat(date), group_concat(money), sum(money), f_type from t_order where DATE_FORMAT(date,'%Y-%m')='2020-08' group by f_id, f_type order by f_id;