create table t(a char(4),b char(8),c int)
insert into t(a,b,c)
select '张山','20060606',1
union all select '张山','20040303',2
union all select '张山','20030303',5
union all select '里斯','20060303',10
union all select '里斯','20020202',12
union all select '里斯','20010101',15select * from t t2 where b not in (select b from t t1 where exists(select top 2 * from t where t.a = t1.a and t.b < t1.b order by t.b))
insert into t(a,b,c)
select '张山','20060606',1
union all select '张山','20040303',2
union all select '张山','20030303',5
union all select '里斯','20060303',10
union all select '里斯','20020202',12
union all select '里斯','20010101',15select * from t t2 where b not in (select b from t t1 where exists(select top 2 * from t where t.a = t1.a and t.b < t1.b order by t.b))
insert into t(a,b,c)
select '张山','20060606',1
union all select '张山','20040303',2
union all select '张山','20030303',5
union all select '里斯','20060303',10
union all select '里斯','20020202',12
union all select '里斯','20010101',15SELECT *,IDENTITY(INT) ID INTO #t FROM t
SELECT * FROM #t a WHERE 1=(SELECT COUNT(1) FROM #t b WHERE b.a=a.a AND b.id<a.id)drop table #t
drop table t有标识列的话,那就会像类似于我建的临时表#t这样的结构,那么写法也是一样.
楼主是取以日期倒序的第二条SELECT * FROM t a WHERE 1=(SELECT COUNT(1) FROM t b WHERE b.a=a.a AND DATEDIFF(dd,b.b,a.b)>0)
DECLARE @Tb TABLE(a char(4),b char(8),c int)
insert into @Tb(a,b,c)
select '张山','20060606',1
union all select '张山','20040303',2
union all select '张山','20030303',5
union all select '里斯','20060303',10
union all select '里斯','20020202',12
union all select '里斯','20010101',15--SELECT * FROM @TB
SELECT ID=IDENTITY(INT,1,1),* INTO # FROM @Tb
SELECT * FROM #
GO
SELECT * FROM # T WHERE (SELECT COUNT(1)FROM # WHERE A=T.a AND ID<T.ID)=1GO
DROP TABLE #
ID a b c
----------- ---- -------- -----------
2 张山 20040303 2
5 里斯 20020202 12(所影响的行数为 2 行)
/* --完成 --
KAO,和上面的怎么有点像...其实思路都是差不多的...需要一个自增列,然后进行判断选择(相同名字的情况下,比所记录集中大于此ID的记录数是不是只有一个,2>1)
SELECT * FROM # T WHERE (SELECT COUNT(1)FROM # WHERE A=T.a AND ID<T.ID)=1
where exists (select 1 from db b where a.a = b.a and a.c>b.c having count(1) = 2)
为 a.c>=b.c
where exists (select 1 from db b where a.a = b.a and a.b<=b.b having count(1) = 2)
declare @db table(
a nvarchar(10),
b datetime,
c int
)
insert @db select '张山', '2006-06-06', 1
union all select '张山', '2004-03-03', 2
union all select '张山', '2003-03-03', 5
union all select '里斯', '2006-03-03', 10
union all select '里斯', '2002-02-02', 12
union all select '里斯', '2001-01-01', 15select ID=identity(int, 1, 1), T1.* into #
from @db as T1
select tmp_db.a, convert(char(8), tmp_db.b, 112) as b, tmp_db.c from # as tmp_db
where 2=(
select count(1) from # as tmp_db2 where tmp_db.a=tmp_db2.a and tmp_db.b>=tmp_db2.b
)drop table #