with t as (select *,rn=row_number()over(partition by c.Owner order by DateAdd desc) from c,f where c.Owner=f.Id) select * from t where rn=1
sql2000select * from c a,f b where a.Owner=b.Id and (select count(1) from c where Owner=a.Owner and DateAdd>a.DateAdd)=0
if object_id('f') is not null drop table f go create table f ( Id int ,Name varchar(10)) insert f select 1 ,'张三' union all select 2 ,'李四'if object_id('c') is not null drop table c go create table c ( Owner int,Name varchar(10),DateAdd int) insert c select 1 ,'语文', 2009 union all select 2 ,'数学', 2009 union all select 1 ,'英语', 2010 goselect * from c a,f b where a.Owner=b.Id and (select count(1) from c where Owner=a.Owner and DateAdd>a.DateAdd)=0 /* Owner Name DateAdd Id Name ----------- ---------- ----------- ----------- ---------- 1 英语 2010 1 张三 2 数学 2009 2 李四(所影响的行数为 2 行) */
是不是要这样。。
Id,Name
1 张三
2 李四子表c的字段
Owner,Name,DateAdd
1 语文 2009
2 数学 2009
1 英语 2010其中,c.Owner与主表f.Id关联要求查询出子表中 各Owner值 对应的最新的记录
比如得到下面的结果
1 英语 2010 (对应owner=1)
2 数学 2009 (对应owner=2)
谢谢!
with t as
(select *,rn=row_number()over(partition by c.Owner order by DateAdd desc) from c,f where c.Owner=f.Id)
select * from t where rn=1
where a.Owner=b.Id
and (select count(1) from c where Owner=a.Owner and DateAdd>a.DateAdd)=0
if object_id('f') is not null drop table f
go
create table f (
Id int ,Name varchar(10))
insert f
select 1 ,'张三' union all
select 2 ,'李四'if object_id('c') is not null drop table c
go
create table c (
Owner int,Name varchar(10),DateAdd int)
insert c
select 1 ,'语文', 2009 union all
select 2 ,'数学', 2009 union all
select 1 ,'英语', 2010
goselect * from c a,f b
where a.Owner=b.Id
and (select count(1) from c where Owner=a.Owner and DateAdd>a.DateAdd)=0
/*
Owner Name DateAdd Id Name
----------- ---------- ----------- ----------- ----------
1 英语 2010 1 张三
2 数学 2009 2 李四(所影响的行数为 2 行)
*/