java面试题_SQL_取某年的各个月份的发邮件数

来源：互联网发布：淘宝助理怎么导出宝贝编辑：程序博客网时间：2024/05/17 07:51

1、算出07年每个月发送邮件的总数

准备数据：
user表
create table users(
id int primary key auto_increment,
name varchar(25)
);

insert into users(name) values ('sunhaojie1');
insert into users(name) values ('sunhaojie2');
insert into users(name) values ('sunhaojie3');

create table events(
id int primary key auto_increment,
create_date Date,
type int,
user_id int,
foreign key (user_id) references users (id)
);

type=1,发送邮件
type=2,接受邮件

insert into events(create_date,type,user_id) values('2007-01-12','1',1);
insert into events(create_date,type,user_id) values('2007-01-12','2',2);
insert into events(create_date,type,user_id) values('2007-01-12','1',3);
insert into events(create_date,type,user_id) values('2007-02-12','1',1);
insert into events(create_date,type,user_id) values('2007-02-12','1',1);
insert into events(create_date,type,user_id) values('2007-02-12','2',3);
insert into events(create_date,type,user_id) values('2007-03-12','1',1);
insert into events(create_date,type,user_id) values('2007-03-12','1',1);
insert into events(create_date,type,user_id) values('2007-03-12','1',2);
insert into events(create_date,type,user_id) values('2007-04-12','1',1);
insert into events(create_date,type,user_id) values('2007-04-12','2',1);
insert into events(create_date,type,user_id) values('2007-04-12','1',1);
insert into events(create_date,type,user_id) values('2007-05-12','1',3);
insert into events(create_date,type,user_id) values('2007-05-12','2',1);
insert into events(create_date,type,user_id) values('2007-05-12','1',1);
insert into events(create_date,type,user_id) values('2007-06-12','1',1);
insert into events(create_date,type,user_id) values('2007-06-12','3',3);
insert into events(create_date,type,user_id) values('2007-06-12','1',1);
insert into events(create_date,type,user_id) values('2007-07-12','1',1);
insert into events(create_date,type,user_id) values('2007-07-12','2',1);
insert into events(create_date,type,user_id) values('2007-07-12','1',2);
insert into events(create_date,type,user_id) values('2007-08-12','1',1);
insert into events(create_date,type,user_id) values('2007-08-12','2',2);
insert into events(create_date,type,user_id) values('2007-08-12','1',3);
insert into events(create_date,type,user_id) values('2007-09-12','1',1);
insert into events(create_date,type,user_id) values('2007-09-12','1',1);
insert into events(create_date,type,user_id) values('2007-09-12','2',1);
insert into events(create_date,type,user_id) values('2007-10-12','1',2);
insert into events(create_date,type,user_id) values('2007-10-12','1',1);
insert into events(create_date,type,user_id) values('2007-10-12','1',1);
insert into events(create_date,type,user_id) values('2007-11-12','1',1);
insert into events(create_date,type,user_id) values('2007-11-12','2',3);
insert into events(create_date,type,user_id) values('2007-11-12','1',1);
insert into events(create_date,type,user_id) values('2007-11-12','1',1);
insert into events(create_date,type,user_id) values('2007-12-12','1',1);
insert into events(create_date,type,user_id) values('2007-12-12','2',3);
insert into events(create_date,type,user_id) values('2007-12-12','2',1);
insert into events(create_date,type,user_id) values('2007-12-12','2',1);
insert into events(create_date,type,user_id) values('2007-10-12','1',1);
insert into events(create_date,type,user_id) values('2007-08-12','1',3);

查询语句：
select count(month), month from
(select type, date_format(create_date,'%c') month from events where type = 1) as te
group by te.month;

0 0