select * from tb b where not exists(select * from tb where modelId=b.modelId and date<b.date)
declare @tb table(id int,date datetime,modelid int) insert @tb values(1,'2009-2-12',1) insert @tb values(2,'2009-1-4',1) insert @tb values(3,'2009-4-3',2) insert @tb values(4,'2009-4-7',3) insert @tb values(5,'2009-1-7',2) insert @tb values(6,'2009-6-10',3)select a.id,a.date,a.modelid from @tb a join (select modelid,date=min(date) from @tb group by modelid) b on a.modelid=b.modelid and a.date=b.date /* id date modelid ----------- ----------------------- ----------- 2 2009-01-04 00:00:00.000 1 5 2009-01-07 00:00:00.000 2 4 2009-04-07 00:00:00.000 3(3 行受影响) */
--你的结果有问题create table #TT ( id int identity(1,1) primary key, date datetime, modelId int ) insert into #TT select '2009-2-12',1 union all select '2009-1-4',1 union all select '2009-4-3',2 union all select '2009-4-7',3 union all select '2009-1-7',2 union all select '2009-6-10',3select * from #TT b where not exists(select * from #TT where modelId=b.modelId and date<b.date) id date modelId ----------- ----------------------- ----------- 2 2009-01-04 00:00:00.000 1 4 2009-04-07 00:00:00.000 3 5 2009-01-07 00:00:00.000 2(3 行受影响)
if object_id('LI') IS NOT NULL DROP TABLE LI CREATE TABLE LI(ID INT,DATE SMALLDATETIME,MODELID INT) INSERT INTO LI([ID],[DATE],MODELID) SELECT 1, '2009-2-12', 1 UNION ALL SELECT 2, '2009-1-4', 1 UNION ALL SELECT 3, '2009-4-3', 2 UNION ALL SELECT 4, '2009-4-7', 3 UNION ALL SELECT 5, '2009-1-7', 2 UNION ALL SELECT 6, '2009-6-10', 3 select T.* FROM LI T JOIN ( SELECT MIN([DATE]) AS DATE, MODELID FROM LI group by MODELID ) LO ON T.DATE= LO.DATE AND T.MODELID = LO.MODELID ORDER BY LO.DATE/*结果 2 2009-01-04 00:00:00 1 5 2009-01-07 00:00:00 2 4 2009-04-07 00:00:00 3 */
insert @tb values(1,'2009-2-12',1)
insert @tb values(2,'2009-1-4',1)
insert @tb values(3,'2009-4-3',2)
insert @tb values(4,'2009-4-7',3)
insert @tb values(5,'2009-1-7',2)
insert @tb values(6,'2009-6-10',3)select a.id,a.date,a.modelid from @tb a join (select modelid,date=min(date) from @tb group by modelid) b
on a.modelid=b.modelid and a.date=b.date
/*
id date modelid
----------- ----------------------- -----------
2 2009-01-04 00:00:00.000 1
5 2009-01-07 00:00:00.000 2
4 2009-04-07 00:00:00.000 3(3 行受影响)
*/
(
id int identity(1,1) primary key,
date datetime,
modelId int
)
insert into #TT select '2009-2-12',1
union all select '2009-1-4',1
union all select '2009-4-3',2
union all select '2009-4-7',3
union all select '2009-1-7',2
union all select '2009-6-10',3select * from #TT b where not exists(select * from #TT where modelId=b.modelId and date<b.date)
id date modelId
----------- ----------------------- -----------
2 2009-01-04 00:00:00.000 1
4 2009-04-07 00:00:00.000 3
5 2009-01-07 00:00:00.000 2(3 行受影响)
DROP TABLE LI
CREATE TABLE LI(ID INT,DATE SMALLDATETIME,MODELID INT)
INSERT INTO LI([ID],[DATE],MODELID)
SELECT 1, '2009-2-12', 1 UNION ALL
SELECT 2, '2009-1-4', 1 UNION ALL
SELECT 3, '2009-4-3', 2 UNION ALL
SELECT 4, '2009-4-7', 3 UNION ALL
SELECT 5, '2009-1-7', 2 UNION ALL
SELECT 6, '2009-6-10', 3
select T.*
FROM LI T JOIN
(
SELECT MIN([DATE]) AS DATE, MODELID
FROM LI
group by MODELID ) LO
ON T.DATE= LO.DATE AND T.MODELID = LO.MODELID
ORDER BY LO.DATE/*结果
2 2009-01-04 00:00:00 1
5 2009-01-07 00:00:00 2
4 2009-04-07 00:00:00 3
*/