在sql server中，如何用sql语句查询出图中每个GroupID分组IsRemove = 1所在行的LastDate之后时间点的数据行，也就是图中标出部分。谢谢大家啦！
数据：
ID GroupID LastDate Value IsRemove
1 101 2014/11/06 14:19:10 100 NULL
2 101 2014/11/06 14:19:20 50 1
3 101 2014/11/06 14:19:30 33.5 NULL
4 101 2014/11/06 14:19:40 45.5 NULL
5 101 2014/11/06 14:19:50 45.5 NULL
6 102 2014/11/06 14:19:10 49.5 NULL
7 102 2014/11/06 14:19:20 20.4 NULL
8 102 2014/11/06 14:19:30 75 NULL
9 102 2014/11/06 14:19:40 26 1
10 102 2014/11/06 14:19:50 18.9 NULL
11 103 2014/11/06 14:19:10 27 NULL
12 103 2014/11/06 14:19:20 21 NULL
13 103 2014/11/06 14:19:30 10 1
14 103 2014/11/06 14:19:40 110 NULL
15 103 2014/11/06 14:19:50 50 NULL
16 104 2014/11/06 14:19:10 33 NULL
17 104 2014/11/06 14:19:20 45 NULL
18 104 2014/11/06 14:19:30 45 1
19 104 2014/11/06 14:19:40 54 NULL
20 104 2014/11/06 14:19:50 54 NULL
------解决思路----------------------
如果同一个GroupID有多少IsRemove=1的话，建议用EXISTS
SELECT
*
FROM
TB T1
WHERE EXISTS(SELECT 1 FROM TB WHERE T1.GroupID=GroupID AND IsRemove=1 AND T1.LastDate>LastDate)
------解决思路----------------------
如果没有同一个GroupID有多个IsRemove=1的话，感觉JOIN 可能会快点
SELECT
T1.*
FROM
TB T1
JOIN TB T2 ON T1.GroupID = T2.GroupID AND T2.IsRemove=1 AND T1.LastDate > T2.LastDate
------解决思路----------------------
IF OBJECT_ID('tempdb..#test','U') IS NOT NULL DROP TABLE #test
CREATE TABLE #test
(
ID INT
,GroupID INT
,LastDate DATETIME
,VALUE FLOAT
,IsRemove INT
)
INSERT INTO #test
SELECT 1, 101, '2014/11/06 14:19:10', 100, NULL
UNION ALL SELECT 2, 101, '2014/11/06 14:19:20', 50, 1
UNION ALL SELECT 3, 101, '2014/11/06 14:19:30', 33.5, NULL
UNION ALL SELECT 4, 101, '2014/11/06 14:19:40', 45.5, NULL
UNION ALL SELECT 5, 101, '2014/11/06 14:19:50', 45.5, NULL
UNION ALL SELECT 6, 102, '2014/11/06 14:19:10', 49.5, NULL
UNION ALL SELECT 7, 102, '2014/11/06 14:19:20', 20.4, NULL
UNION ALL SELECT 8, 102, '2014/11/06 14:19:30', 75, NULL
UNION ALL SELECT 9, 102, '2014/11/06 14:19:40', 26, 1
UNION ALL SELECT 10, 102, '2014/11/06 14:19:50', 18.9, NULL
UNION ALL SELECT 11, 103, '2014/11/06 14:19:10', 27, NULL
UNION ALL SELECT 12, 103, '2014/11/06 14:19:20', 21, NULL
UNION ALL SELECT 13, 103, '2014/11/06 14:19:30', 10, 1
UNION ALL SELECT 14, 103, '2014/11/06 14:19:40', 110, NULL
UNION ALL SELECT 15, 103, '2014/11/06 14:19:50', 50, NULL
UNION ALL SELECT 16, 104, '2014/11/06 14:19:10', 33, NULL
UNION ALL SELECT 17, 104, '2014/11/06 14:19:20', 45, NULL
UNION ALL SELECT 18, 104, '2014/11/06 14:19:30', 45, 1
UNION ALL SELECT 19, 104, '2014/11/06 14:19:40', 54, NULL
UNION ALL SELECT 20, 104, '2014/11/06 14:19:50', 54, NULL
SELECT A.*
FROM #test A
JOIN (SELECT * FROM #test WHERE IsRemove=1) B
ON A.ID>B.ID AND A.GroupID=b.GroupID
SELECT * FROM #test
/*
ID GroupID LastDate VALUE IsRemove
3 101 2014-11-06 14:19:30.000 33.5 NULL
4 101 2014-11-06 14:19:40.000 45.5 NULL
5 101 2014-11-06 14:19:50.000 45.5 NULL
10 102 2014-11-06 14:19:50.000 18.9 NULL
14 103 2014-11-06 14:19:40.000 110 NULL
15 103 2014-11-06 14:19:50.000 50 NULL
19 104 2014-11-06 14:19:40.000 54 NULL
20 104 2014-11-06 14:19:50.000 54 NULL
*/------解决思路----------------------
where后加exists返回真假 当 where 后面的条件成立，则存在，否则。。。。