select a.*,b.name,b.data from 主表 as a inner join 从表 as b on a.ID=b.v_id where not exists(select 1 from 从表 where v_id=b.v_id and [date]>a.[date])把ID改为Date
create table meiju( id int null, name varchar(20) null ) create table juji( id int null, v_id int null, name varchar(20) null, data datetime null ) insert into meiju(id,name) select 1,'电影1' union select 2,'电影2' union select 3,'电影3' union select 4,'电影4' union select 5,'电影5' insert into juji(id,v_id,name,data) select 1,1,'第一集',GETDATE()-5 union select 1,2,'第二集',GETDATE()-3 union select 1,3,'第三集',GETDATE()-2 union select 2,1,'第1集',GETDATE()-4 union select 2,2,'第2集',GETDATE()-2 union select 2,3,'第3集',GETDATE()-1 union select 3,1,'第1集12',GETDATE()-1select A.id,A.name,B.name,B.data from meiju A,juji B where A.id=B.id AND exists (select 1 from (select id,MAX(data) data from juji group by id) C where C.data=B.data and C.id=B.id)
select A.id,A.name,B.name,B.data from 主表 A,从表 B where A.id=B.id AND exists (select 1 from (select id,MAX(data) data from 从表 group by id) C where C.data=B.data and C.id=B.id)
--drop table meiju create table meiju( id int null, name varchar(20) null ) --drop table juji create table juji( id int null, v_id int null, name varchar(20) null, data datetime null ) insert into meiju(id,name) select 1,'电影1' union select 2,'电影2' union select 3,'电影3' union select 4,'电影4' union select 5,'电影5' insert into juji(id,v_id,name,data) select 1,1,'第一集',GETDATE()-5 union select 2,1,'第二集',GETDATE()-3 union select 3,1,'第三集',GETDATE()-2 union select 4,2,'第1集',GETDATE()-4 union select 5,2,'第2集',GETDATE()-2 union select 6,2,'第3集',GETDATE()-1 union select 7,3,'第1集12',GETDATE()-1select A.id,A.name,B.name,B.data from meiju A,juji B where A.id=B.v_id AND exists (select 1 from (select v_id,MAX(data) data from juji group by v_id) C where C.data=B.data and C.v_id=B.v_id) 结果
改为b select a.*,b.name,b.data from 主表 as a inner join 从表 as b on a.ID=b.v_id where not exists(select 1 from 从表 where v_id=b.v_id and [date]>b.[date])或max select a.*,b.name,b.data from 主表 as a inner join 从表 as b on a.ID=b.v_id where b.[date]=(select max([date]) from 从表 where v_id=b.v_id )
给出我的查询结果: 版主给我的第一个语句的查询时间最短 [SQL] select a.id,a.name,e.anime_id,e.`name`,e.num from imakiba_anime a,imakiba_episode e where a.id = e.anime_id AND EXISTS (select 1 from (select anime_id,MAX(num) num from imakiba_episode GROUP BY anime_id) c where c.num = e.num and c.anime_id = e.anime_id); 受影响的行: 0 时间: 3.467ms[SQL] select a.id,a.name,e.anime_id,e.`name`,e.num from imakiba_anime a INNER JOIN imakiba_episode e on a.id = e.anime_id where not EXISTS(select 1 from imakiba_episode where anime_id = e.anime_id and num >e.num); 受影响的行: 0 时间: 0.842ms[SQL] select a.id,a.name,e.anime_id,e.`name`,e.num from imakiba_anime a INNER JOIN imakiba_episode e on a.id = e.anime_id where e.num = (select max(num) from imakiba_episode where anime_id = e.anime_id); 受影响的行: 0 时间: 1.488ms
from 主表 as a
inner join 从表 as b on a.ID=b.v_id
where not exists(select 1 from 从表 where v_id=b.v_id and id>a.id)
从表是date字段,剧集的更新时间。
剧集根据时间排序,取最新的剧集。
我按版主的sql执行发现查不出数据。。
而且最后一行的id>a.id让我很疑惑,从表id怎么和主表id比较呢?
from 主表 as a
inner join 从表 as b on a.ID=b.v_id
where not exists(select 1 from 从表 where v_id=b.v_id and [date]>a.[date])把ID改为Date
a没有date字段,b才有。。
可参照
http://bbs.csdn.net/topics/240034273
我看看你的帖子先。
id int null,
name varchar(20) null
)
create table juji(
id int null,
v_id int null,
name varchar(20) null,
data datetime null
)
insert into meiju(id,name)
select 1,'电影1' union
select 2,'电影2' union
select 3,'电影3' union
select 4,'电影4' union
select 5,'电影5'
insert into juji(id,v_id,name,data)
select 1,1,'第一集',GETDATE()-5 union
select 1,2,'第二集',GETDATE()-3 union
select 1,3,'第三集',GETDATE()-2 union
select 2,1,'第1集',GETDATE()-4 union
select 2,2,'第2集',GETDATE()-2 union
select 2,3,'第3集',GETDATE()-1 union
select 3,1,'第1集12',GETDATE()-1select A.id,A.name,B.name,B.data from meiju A,juji B
where A.id=B.id AND
exists (select 1 from (select id,MAX(data) data from juji group by id) C
where C.data=B.data and C.id=B.id)
where A.id=B.id AND
exists (select 1 from (select id,MAX(data) data from 从表 group by id) C
where C.data=B.data and C.id=B.id)
你的语句第二行怎么是a.id=b.id?
a.id关联的应该是b.v_id
查询出的结果主从表对应不上
把第二行的a.id=b.id改为a.id=b.v_id查出了所有的剧集数据3W条
create table meiju(
id int null,
name varchar(20) null
)
--drop table juji
create table juji(
id int null,
v_id int null,
name varchar(20) null,
data datetime null
)
insert into meiju(id,name)
select 1,'电影1' union
select 2,'电影2' union
select 3,'电影3' union
select 4,'电影4' union
select 5,'电影5'
insert into juji(id,v_id,name,data)
select 1,1,'第一集',GETDATE()-5 union
select 2,1,'第二集',GETDATE()-3 union
select 3,1,'第三集',GETDATE()-2 union
select 4,2,'第1集',GETDATE()-4 union
select 5,2,'第2集',GETDATE()-2 union
select 6,2,'第3集',GETDATE()-1 union
select 7,3,'第1集12',GETDATE()-1select A.id,A.name,B.name,B.data from meiju A,juji B
where A.id=B.v_id AND
exists (select 1 from (select v_id,MAX(data) data from juji group by v_id) C
where C.data=B.data and C.v_id=B.v_id)
结果
改为b
select a.*,b.name,b.data
from 主表 as a
inner join 从表 as b on a.ID=b.v_id
where not exists(select 1 from 从表 where v_id=b.v_id and [date]>b.[date])或max
select a.*,b.name,b.data
from 主表 as a
inner join 从表 as b on a.ID=b.v_id
where b.[date]=(select max([date]) from 从表 where v_id=b.v_id )
版主给我的第一个语句的查询时间最短
[SQL] select a.id,a.name,e.anime_id,e.`name`,e.num from imakiba_anime a,imakiba_episode e
where a.id = e.anime_id AND
EXISTS (select 1 from (select anime_id,MAX(num) num from imakiba_episode GROUP BY anime_id) c
where c.num = e.num and c.anime_id = e.anime_id);
受影响的行: 0
时间: 3.467ms[SQL]
select a.id,a.name,e.anime_id,e.`name`,e.num from imakiba_anime a
INNER JOIN imakiba_episode e on a.id = e.anime_id
where not EXISTS(select 1 from imakiba_episode where anime_id = e.anime_id and num >e.num);
受影响的行: 0
时间: 0.842ms[SQL]
select a.id,a.name,e.anime_id,e.`name`,e.num from imakiba_anime a
INNER JOIN imakiba_episode e on a.id = e.anime_id
where e.num = (select max(num) from imakiba_episode where anime_id = e.anime_id);
受影响的行: 0
时间: 1.488ms