有数据表(Table1)如下 :
GroupID PartID CreateTime TextValue
1 1 2008-10-12 150
1 1 2008-10-10 250
1 2 2008-08-05 200
1 3 2008-10-01 50
2 2 2008-10-11 166
3 1 2008-06-06 110
3 1 2007-08-06 22
4 3 2008-09-10 14希望得到如下结果GroupID PartID CreateTime TextValue
1 1 2008-10-12 150
1 2 2008-08-05 200
1 3 2008-10-01 50
2 2 2008-10-11 166
3 1 2008-06-06 110
4 3 2008-09-10 14也就是以GroupID,PartID为联合主键,取CreateTime最近的值这个SQL应该怎么写?
GroupID PartID CreateTime TextValue
1 1 2008-10-12 150
1 1 2008-10-10 250
1 2 2008-08-05 200
1 3 2008-10-01 50
2 2 2008-10-11 166
3 1 2008-06-06 110
3 1 2007-08-06 22
4 3 2008-09-10 14希望得到如下结果GroupID PartID CreateTime TextValue
1 1 2008-10-12 150
1 2 2008-08-05 200
1 3 2008-10-01 50
2 2 2008-10-11 166
3 1 2008-06-06 110
4 3 2008-09-10 14也就是以GroupID,PartID为联合主键,取CreateTime最近的值这个SQL应该怎么写?
from ta1 a
where not exists(select 1 from tb1 where groupid = a.groupid and PartID = a.PartID and CreateTime> a.CreateTime)
declare @tb table(GroupID int, PartID int, CreateTime smalldatetime, TextValue int)
insert @tb
SELECT 1, 1, '2008-10-12', 150 UNION ALL
SELECT 1, 1, '2008-10-10', 250 UNION ALL
SELECT 1, 2, '2008-08-05', 200 UNION ALL
SELECT 1, 3, '2008-10-01', 50 UNION ALL
SELECT 2, 2, '2008-10-11', 166 UNION ALL
SELECT 3, 1, '2008-06-06', 110 UNION ALL
SELECT 3, 1, '2007-08-06', 22 UNION ALL
SELECT 4, 3, '2008-09-10', 14
select * from @tb as a where not exists(select 1 from @tb where GroupID=a.GroupID and PartID=a.PartID and CreateTime>a.CreateTime)
/*
GroupID PartID CreateTime TextValue
----------- ----------- ------------------------------------------------------ -----------
1 1 2008-10-12 00:00:00 150
1 2 2008-08-05 00:00:00 200
1 3 2008-10-01 00:00:00 50
2 2 2008-10-11 00:00:00 166
3 1 2008-06-06 00:00:00 110
4 3 2008-09-10 00:00:00 14
*/
declare @tb table
(
GroupID int,
PartID int,
CreateTime smalldatetime,
TextValue int
)
insert @tb
SELECT 1, 1, '2008-10-12', 150 UNION ALL
SELECT 1, 1, '2008-10-10', 250 UNION ALL
SELECT 1, 2, '2008-08-05', 200 UNION ALL
SELECT 1, 3, '2008-10-01', 50 UNION ALL
SELECT 2, 2, '2008-10-11', 166 UNION ALL
SELECT 3, 1, '2008-06-06', 110 UNION ALL
SELECT 3, 1, '2007-08-06', 22 UNION ALL
SELECT 4, 3, '2008-09-10', 14
select * from @tb a
where not exists(
select 1 from @tb b
where b.GroupID=a.GroupID and b.PartID=a.PartID and a.CreateTime<b.CreateTime
)结果:
1 1 2008-10-12 00:00:00 150
1 2 2008-08-05 00:00:00 200
1 3 2008-10-01 00:00:00 50
2 2 2008-10-11 00:00:00 166
3 1 2008-06-06 00:00:00 110
4 3 2008-09-10 00:00:00 14
and partid=a.partid and createtime>a.createtime)--或者:
select * from table1 a
where (select count(createtime) from table1 where groupid=a.groupid and partid=a.partid and createtime>a.createtime)=0
insert @tb
SELECT 1, 1, '2008-10-12', 150 UNION ALL
SELECT 1, 1, '2008-10-10', 250 UNION ALL
SELECT 1, 2, '2008-08-05', 200 UNION ALL
SELECT 1, 3, '2008-10-01', 50 UNION ALL
SELECT 2, 2, '2008-10-11', 166 UNION ALL
SELECT 3, 1, '2008-06-06', 110 UNION ALL
SELECT 3, 1, '2007-08-06', 22 UNION ALL
SELECT 4, 3, '2008-09-10', 14SELECT * FROM @TB A
WHERE TEXTVALUE IN
(SELECT TOP 1 TEXTVALUE FROM @TB WHERE PARTID=A.PARTID AND GROUPID=A.GROUPID ORDER BY TEXTVALUE DESC )
/*
1 1 2008-10-10 00:00:00 250
1 2 2008-08-05 00:00:00 200
1 3 2008-10-01 00:00:00 50
2 2 2008-10-11 00:00:00 166
3 1 2008-06-06 00:00:00 110
4 3 2008-09-10 00:00:00 14
SELECT * FROM @TB A
WHERE
(SELECT COUNT(*) FROM @TB WHERE PARTID=A.PARTID AND GROUPID=A.GROUPID AND TEXTVALUE>A.TEXTVALUE )<1