求救：一個SQL查詢語句

[wenjinhe]

我現需要將表A中
ID（子段）project(子段) RIGHT(子段) DATE(子段)
1 AAAA ASS 2005/01/02
2 CCCC CSA 2005/02/01
3 CCCC CSB 2005/02/03
4 AAAA ASE 2005/03/02
5 AAAA ASF 2005/03/06
6 BBBB BVS 2005/05/06
7 CCCC CSC 2005/05/08
8 AAAA ASE 2005/07/08
9 CCCC CER 2005/09/08
10 AAAA AER 2005/09/10
.......

project子段下大于三次的RIGHT按日期把超過三條最早的記錄查詢出來
按照上面的數據，查詢出來的應該是以下三條記錄：

ID（子段）project(子段) RIGHT(子段) DATE(子段)
1 AAAA ASS 2005/01/02
2 CCCC CSA 2005/02/01
4 AAAA ASE 2005/03/02


請教該語句怎么寫？
先謝謝各位了~

[dulei115]

select *
from tableA a
where date = (select min(Date) as minDate
from tableA
where project = a.project
group by project
having count(id) > 3)

[dulei115]

if object_id('tableA') is not null drop table tableA
select 1 as [id], 'AAAA' as [project], 'ASS' as [right], '2005/01/02' as [date]
into tableA
union select 2, 'CCCC', 'CSA', '2005/02/01'
union select 3, 'CCCC', 'CSB', '2005/02/03'
union select 4, 'AAAA', 'ASE', '2005/03/02'
union select 5, 'AAAA', 'ASF', '2005/03/06'
union select 6, 'BBBB', 'BVS', '2005/05/06'
union select 7, 'CCCC', 'CSC', '2005/05/08'
union select 8, 'AAAA', 'ASE', '2005/07/08'
union select 9, 'CCCC', 'CER', '2005/09/08'
union select 10, 'AAAA', 'AER', '2005/09/10'
--------------------------------------------------------------------
select *
from tableA a
where [date] = (select min([Date]) as [minDate]
from tableA
where [project] = a.[project]
group by [project]
having count([id]) > 3)
/*
id project right date
1 AAAA ASS 2005/01/02
2 CCCC CSA 2005/02/01
*/
--------------------------------------------------------------------
drop table tableA

[wenjinhe]

感謝 dulei115 兄弟
不過用上面的查詢只能查出最早的一條記錄，我這邊要求是查詢出超過三條以上的早期記錄，比如例子中符合條件的子段AAAA 就查詢出來了兩條
請再次賜教~~
謝謝

[dulei115]

--是不是表达错了：同一个project中，除去date最后的三条？？？？
if object_id('tableA') is not null drop table tableA
select 1 as [id], 'AAAA' as [project], 'ASS' as [right], '2005/01/02' as [date]
into tableA
union select 2, 'CCCC', 'CSA', '2005/02/01'
union select 3, 'CCCC', 'CSB', '2005/02/03'
union select 4, 'AAAA', 'ASE', '2005/03/02'
union select 5, 'AAAA', 'ASF', '2005/03/06'
union select 6, 'BBBB', 'BVS', '2005/05/06'
union select 7, 'CCCC', 'CSC', '2005/05/08'
union select 8, 'AAAA', 'ASE', '2005/07/08'
union select 9, 'CCCC', 'CER', '2005/09/08'
union select 10, 'AAAA', 'AER', '2005/09/10'
--------------------------------------------------------------------
select *
from tableA a
where (select count(*)
from tableA
where [project] = a.[project] and [date] > a.[date]) > 2
/*
id project right date
1 AAAA ASS 2005/01/02
2 CCCC CSA 2005/02/01
4 AAAA ASE 2005/03/02
*/
--------------------------------------------------------------------
drop table tableA

[wenjinhe]

高人 dulei115 非常感謝你~
結案~~