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;
分享到:
相关推荐
直击面试系列_破解Java面试题_全 直击面试系列_破解Java面试题_全
java面试题_高频题
sql面试题,java面试题sql面试题,java面试题sql面试题,java面试题sql面试题,java面试题sql面试题,java面试题sql面试题,java面试题
java面试题_jvm(21题)
1. 消息可靠性: 2. 消息低延迟: 3. 每个消息⾄少投递⼀次: 4. 每个消息只消费⼀次: 5. Broker的Buffer满了怎么办? 6. 回溯消息: 7. 消息堆积: 8. 分布式事务: 9. 定时消息: 10. 消息重试: ...
MLDN魔乐科技_直击面试系列_破解Java面试题_2—Java WEB面试题 word版本
JAVA面试题_适合刚毕业的童鞋; 汇总了之前面试被问到的32个基础问答题,有助于面试成功噢
java面试题包括基础和提高部分,是毕业生复习找工作的极好备战资料
java 面试题基础题,sql 面试题 带答案
java面试题_高并发、高可用、分布式(9题)
java面试题_大数据(3题)
100IT名企java面试必考面试题 你不清楚的18个非技术面试题是这些
10道经典java面试题_实习生必问.docx,10道经典java面试题_实习生必问.docx 10道经典java面试题_实习生必问.docx,10道经典java面试题_实习生必问.docx
尚硅谷大数据技术之企业SQL面试题 doc文档,奥利给
java面试题_多线程(68题)
Java面试宝典_2010Java面试宝典_2010Java面试宝典_2010Java面试宝典_2010Java面试宝典_2010Java面试宝典_2010
java面试题_框架—Spring(14题)
java面试题_源码解读(3题)
JAVA面试题JAVA面试题JAVA面试题JAVA面试题JAVA面试题JAVA面试题JAVA面试题JAVA面试题JAVA面试题
有关java的面试题及相关解析教程,还是比较全面的