关于筛选子表记录的写法

[duck530]

我现在有主表

main: id date ....
1 2010-09-12 00:00:00 ...
2 2010-09-12 00:00:01 ...

子表
sub: id index con
1 1 XXX
1 2 YYY
2 1 AAA
2 2 BBB
2 3 CCC

其中主表ID为主键 子表ID 和 Index为主键

我想得到主表date大于某个时间的ID 在子表里对应该ID的第一记录
上面数据得结果就是 id index con
1 1 XXX
2 1 AAA

请问该如何写SQL啊?

[dawugui]

select n.* 
from main m , sub n
where m.date > '某个时间' and m.id = n.id and n.[index] = (select min([index]) from sub where id = n.id)

select n.* 
from main m , sub n
where m.date > '某个时间' and m.id = n.id and not exists(select 1 from sub where id = n.id and [index] < n.[index] )

[dawugui]

create table main(id int,date datetime)
insert into main values(1 ,'2010-09-12 00:00:00')
insert into main values(2 ,'2010-09-12 00:00:01')
create table sub(id int,[index] int, con varchar(10))
insert into sub values(1 ,1 ,'XXX')
insert into sub values(1 ,2 ,'YYY')
insert into sub values(2 ,1 ,'AAA')
insert into sub values(2 ,2 ,'BBB')
insert into sub values(2 ,3 ,'CCC')
go

select n.* 
from main m , sub n
where m.date >= '2010-09-12 00:00:00' and m.id = n.id and n.[index] = (select min([index]) from sub where id = n.id)
/*
id          index       con        
----------- ----------- ---------- 
1           1           XXX
2           1           AAA

（所影响的行数为 2 行）
*/

select n.* 
from main m , sub n
where m.date >= '2010-09-12 00:00:00' and m.id = n.id and not exists(select 1 from sub where id = n.id and [index] < n.[index] )
/*
id          index       con        
----------- ----------- ---------- 
1           1           XXX
2           1           AAA

（所影响的行数为 2 行）
*/

drop table main , sub

[envykok]

IF OBJECT_ID('Test_Main') IS NOT NULL
DROP TABLE Test_Main
GO
CREATE TABLE Test_Main
(
   id int,
   date datetime
);
GO
INSERT INTO Test_Main   
SELECT 1,'2010-09-02' 
UNION ALL
SELECT 2,'2010-09-03'
GO

IF OBJECT_ID('Test_Sub') IS NOT NULL
DROP TABLE Test_Sub
GO

CREATE TABLE Test_Sub
(
   id int,
   [index] int,
   con varchar(20)
);

INSERT INTO Test_Sub   
SELECT 1,1,'XXX' 
UNION ALL
SELECT 1,2,'YYY'
UNION ALL
SELECT 2,1,'AAA'
UNION ALL
SELECT 2,2,'BBB'
UNION ALL
SELECT 2,3,'CCC'

GO

SELECT id, [index], con
FROM
(
SELECT tb.*, row_number() over (partition by tb.id order by tb.id) as row_id
FROM Test_Main tm INNER JOIN Test_Sub tb
	ON tm.ID=tb.ID
WHERE tm.date>'2010-09-01 12:12:12'
) AS temp
WHERE row_id=1