日期:2014-05-17 浏览次数:20597 次
CREATE TABLE #F
(
InvieteID INT IDENTITY(1,1),
FromUserId INT,
ToUserId INT,
AddTime DATETIME,
States INT
)
INSERT INTO #F(FromUserId,ToUserId,AddTime,States)
SELECT 135732,153,'2012-11-02 18:01:48.783',0 UNION
SELECT 135732,184,'2012-11-02 18:00:30.040',0 UNION
SELECT 135732,152,'2012-11-02 17:30:38.513',0 UNION
SELECT 135728,135776,'2012-11-02 16:35:12.977',0 UNION
SELECT 16,7,'2012-11-02 11:25:21.620',0 UNION
SELECT 135732,7,'2012-11-02 11:23:59.220',0 UNION
SELECT 135728,135776,'2012-11-02 09:50:03.023',2 UNION
SELECT 135728,135776,'2012-11-02 09:49:33.303',0 UNION
SELECT 135728,135776,'2012-11-02 09:49:17.303',1 UNION
SELECT 135728,135776,'2012-11-02 09:48:42.507',0 UNION
SELECT 135728,135776,'2012-11-02 09:46:02.663',0 UNION
SELECT 135728,135776,'2012-11-02 09:44:39.617',2 UNION
SELECT 135732,121,'2012-10-31 10:26:09.563',0 UNION
SELECT 135732,127,'2012-10-29 10:02:09.617',0 UNION
SELECT 135732,112,'2012-10-29 10:02:00.750',0 UNION
SELECT 135732,109,'2012-10-29 10:01:58.287',0 UNION
SELECT 135732,102,'2012-10-29 10:01:34.147',0
DROP TABLE #F
/*
想要的结果,相同的FromUserId和ToUserId只取出时间最近的一条记录,即使States不同,也是取出时间最近的一条记录
*/
select
*
from
#F a
where
a.AddTime=(
select
MAX(b.AddTime)
from
#F b
where
a.FromUserId=b.FromUserId
and a.InvieteID=b.InvieteID
and a.States=b.States
)
CREATE TABLE #F
(
InvieteID INT IDENTITY(1,1),
FromUserId INT,
ToUserId INT,
AddTime DATETIME,
States INT
)
INSERT INTO #F(FromUserId,ToUserId,AddTime,States)
SELECT 135732,153,'2012-11-02 18:01:48.783',0 UNION
SELECT 135732,184,'2012-11-02 18:00:30.040',0 UNION
SELECT 135732,152,'2012-11-02 17:30:38.513',0 UNION
SELECT 135728,135776,'2012-11-02 16:35:12.977',0 UNION
SELECT 16,7,'2012-11-02 11:25:21.620',0 UNION
SELECT 135732,7,'2012-11-02 11:23:59.220',0 UNION
SELECT 135728,135776,'2012-11-02 09:50:03.023',2 UNION
SELECT 135728,135776,'2012-11-02 09:49:33.303',0 UNION
SELECT 135728,135776,'2012-11-02 09:49:17.303',1 UNION
SELECT 135728,135776,'2012-11-02 09:48:42.507',0 UNION
SELECT 135728,135776,'2012-11-02 09:46:02.663',0 UNION
SELECT 135728,135776,'2012-11-02 09:44:39.617',2 UNION
SELECT 135732,121,'2012-10-31 10:26:09.563',0 UNION
SELECT 135732,127,'2012-10-29 10:02:09.617',0 UNION
SELECT 135732,112,'2012-10-29 10:02:00.750',0 UNION
SELECT 135732,1