select a.id,a.name,b.plan,c.adddate from author a,plan b,(select authorid,max(adddate) as addate from plan group by authorid) c where a.id=b.authorid and b.adddate=c.adddate
select a.*,b.plan,b.adddate from author a, plan b where a.id=b.authorid and not exists(select 1 from plan where authorid=b.authorid and adddate>b.adddate)
--建表author create table author(author_ID varchar(3), author_name varchar(3))--建表tplan create table tplan(plan_ID varchar(3), author_ID varchar(3), plan_plan varchar(20), plan_adddate datetime)--将数据插入到表author insert into author select '1', 'aaa' union select '2', 'bbb' union select '3', 'ccc' union select '4', 'ddd' union select '5', 'eee' --将数据插入到表tplan insert into tplan select '1', '1', 'sdasfdfaf', '2005-12-1' union select '2', '1', 'aaa', '2005-12-2' union select '3', '3', 'aaa', '2005-12-3' union select '4', '2', 'aaa', '2005-12-4' union select '5', '2', 'aaa', '2005-12-5' union select '6', '2', 'aaa', '2005-12-6' union select '7', '4', 'aaa', '2005-12-7' --查询 select plan_ID, d.author_name, tplan.plan_plan, d.plan_adddate from tplan, author, ( select author_name, plan_adddate = max(plan_adddate) from ( select b.plan_ID, a.author_name as author_name, b.plan_plan, plan_adddate from author a, tplan b where a.author_ID = b.author_ID ) c group by author_name ) d where tplan.author_ID = author.author_ID and author.author_name = d.author_name and tplan.plan_adddate = d.plan_adddate order by plan_ID --drop表 drop table author drop table tplan/* --显示结果 play_ID author_name plan_play play_adddate 7 ddd aaa 2005-12-07 00:00:00.000 3 ccc aaa 2005-12-03 00:00:00.000 6 bbb aaa 2005-12-06 00:00:00.000 2 aaa aaa 2005-12-02 00:00:00.000 */
不明
a.*,b.plan,b.adddate
from
author a,
plan b
where
a.id=b.authorid
and
not exists(select 1 from plan where authorid=b.authorid and adddate>b.adddate)
create table author(author_ID varchar(3), author_name varchar(3))--建表tplan
create table tplan(plan_ID varchar(3), author_ID varchar(3), plan_plan varchar(20), plan_adddate datetime)--将数据插入到表author
insert into author
select '1', 'aaa' union
select '2', 'bbb' union
select '3', 'ccc' union
select '4', 'ddd' union
select '5', 'eee'
--将数据插入到表tplan
insert into tplan
select '1', '1', 'sdasfdfaf', '2005-12-1' union
select '2', '1', 'aaa', '2005-12-2' union
select '3', '3', 'aaa', '2005-12-3' union
select '4', '2', 'aaa', '2005-12-4' union
select '5', '2', 'aaa', '2005-12-5' union
select '6', '2', 'aaa', '2005-12-6' union
select '7', '4', 'aaa', '2005-12-7' --查询
select plan_ID, d.author_name, tplan.plan_plan, d.plan_adddate
from tplan, author,
(
select author_name, plan_adddate = max(plan_adddate)
from
(
select b.plan_ID, a.author_name as author_name, b.plan_plan, plan_adddate
from author a, tplan b
where a.author_ID = b.author_ID
) c
group by author_name
) d
where tplan.author_ID = author.author_ID
and author.author_name = d.author_name
and tplan.plan_adddate = d.plan_adddate
order by plan_ID
--drop表
drop table author
drop table tplan/*
--显示结果
play_ID author_name plan_play play_adddate
7 ddd aaa 2005-12-07 00:00:00.000
3 ccc aaa 2005-12-03 00:00:00.000
6 bbb aaa 2005-12-06 00:00:00.000
2 aaa aaa 2005-12-02 00:00:00.000
*/