我这里有一个跨国公司给我的SQL培训题，给大家分享一下（2）--答案

希望大家把效率更高的代码贴出来，和我分享，这是我们几个培训人员写的，因为公司不给所谓的“答案”，我们也不知道还有没有效率更好的SQL脚本

 

create table picture
(
id int(10) not null auto_increment primary key,
name varchar(20) not null,
path varchar(100) not null
);
create table viewlog
(
id bigint(10) not null auto_increment primary key,
picture_id int(10)not null,
view_date_time datetime,
foreign key(picture_id) references picture(id)
);

Question1
//************************************
1.1.1 select name,count(picture_id) as Access_Count from picture left join (select * from viewlog where view_date_time between '2000-01-01 00:00:00' and '2000-01-01 00:00:02') as newviewlog
on(picture.id=newviewlog.picture_id)  group by picture_id order by Access_Count desc(low)

1.1.2 select name,count(picture_id) as Access_Count from picture left join viewlog on(picture.id=viewlog.picture_id and view_date_time between '2000-01-01 00:00:00' and '2000-01-01 00:00:02')
group by picture_id order by Access_Count desc(--)(55.28)

1.1.3 select name,count(picture_id) as Access_Count
 from picture left join viewlog on(view_date_time between '2000-01-01 00:00:00' and '2000-01-01 00:00:02' and picture.id=viewlog.picture_id )
 group by picture_id order by Access_Count desc(best)(54.58)

1.1.4 select name,count(picture_id) as Access_Count
 from picture left join viewlog on(view_date_time >= '2000-01-01 00:00:00' and  view_date_time <= '2000-01-01 00:00:02' and picture.id=viewlog.picture_id )
 group by picture_id order by Access_Count desc(best)(54.92)
//************************************
1.2.1 select name,count(picture_id) as Access_Count
 from picture left join viewlog on(view_date_time between '2000-01-01 00:00:01' and '2000-01-01 00:00:03' and picture.id=viewlog.picture_id )
 group by picture_id order by Access_Count desc

Qestion2
//************************************
2.1.1 select name,count(view_date_time) as Access_Count
 from picture left join viewlog on(picture.id = viewlog.picture_id )
 where viewlog.view_date_time between '2000-01-01 00:00:00' and '2000-01-01 00:00:02' 
 group by picture.name;--(0.27)

2.1.2 select name,count(view_date_time) as Access_Count
 from picture join viewlog on(viewlog.view_date_time between '2000-01-01 00:00:00' and '2000-01-01 00:00:02' and picture.id = viewlog.picture_id )
 group by picture.name;--(0.27)
//************************************
2.1. select name,count(view_date_time) as Access_Count
 from picture left join viewlog on(picture.id = viewlog.picture_id )
 where viewlog.view_date_time between '2000-01-01 00:00:01' and '2000-01-01 00:00:03' 
 group by picture.name;
//************************************
2.1.1 select name,count(name) as Access_Count from picture left join viewlog on(picture.id = viewlog.picture_id ) where viewlog.view_date_time between '2000-01-01 00:00:00' and '2000-01-01 00:00:02'   group by name;

2.2.1  select name,count(picture_id) as Access_Count from picture left join viewlog on(picture.id=viewlog.picture_id ) where view_date_time between '2000-01-01 00:00:01' and '2000-01-01 00:00:03' group by picture_id order by Access_Count desc