各位大神，分组取最大记录啊

Question

GO 
IF OBJECT_ID('test_table') IS NOT NULL
DROP TABLE test_tableCREATE TABLE test_table
(
[sid] INT PRIMARY KEY IDENTITY(1,1),
[email] NVARCHAR(50),
[imei] NVARCHAR(50),
[lastseen] DATETIME
)GO
INSERT INTO test_table
SELECT '[email protected]', '000000000000000', '2013-01-14 04:41:12.000'
UNION ALL
SELECT '[email protected]', '000000000000000', '2013-01-13 22:22:30.000'
UNION ALL
SELECT '[email protected]', '000000000000000', '2013-01-13 04:03:13.000'
UNION ALL
SELECT '[email protected]', '6f52a254806809263ef9c426fbf77e890b173887', '2013-01-12 14:07:31.000'
UNION ALL
SELECT '[email protected]', '6f52a254806809263ef9c426fbf77e890b173887', '2013-01-11 15:05:04.000'
UNION ALL
SELECT '[email protected]', '868655000382418', '2013-01-13 17:01:48.000'
UNION ALL
SELECT '[email protected]', '868655000382418', '2013-01-12 18:29:45.000'
SELECT * FROM test_table/*
sid         email                                              imei                                               lastseen
----------- -------------------------------------------------- -------------------------------------------------- -----------------------
1           [email protected]                                       000000000000000                                    2013-01-14 04:41:12.000
2           [email protected]                                       000000000000000                                    2013-01-13 22:22:30.000
3           [email protected]                                       000000000000000                                    2013-01-13 04:03:13.000
4           [email protected]                                       6f52a254806809263ef9c426fbf77e890b173887           2013-01-12 14:07:31.000
5           [email protected]                                        6f52a254806809263ef9c426fbf77e890b173887           2013-01-11 15:05:04.000
6           [email protected]                                       868655000382418                                    2013-01-13 17:01:48.000
7           [email protected]                                       868655000382418                                    2013-01-12 18:29:45.000
*/--我想要的结果
/*
sid email imei lastseen
1 [email protected] 000000000000000 2013-01-14 04:41:12.000
4 [email protected] 6f52a254806809263ef9c426fbf77e890b173887 2013-01-12 14:07:31.000
6 [email protected] 868655000382418 2013-01-13 17:01:48.000
*/能给出思路或代码，小弟感激不尽！
sql

Answer 1

这样？
GO 
IF OBJECT_ID('test_table') IS NOT NULL
    DROP TABLE test_table
 
CREATE TABLE test_table
(
    [sid] INT PRIMARY KEY IDENTITY(1,1),
    [email] NVARCHAR(50),
    [imei] NVARCHAR(50),
    [lastseen] DATETIME    
)
 
GO
INSERT INTO test_table
SELECT '[email protected]',    '000000000000000',    '2013-01-14 04:41:12.000'
UNION ALL
SELECT '[email protected]',    '000000000000000',    '2013-01-13 22:22:30.000'
UNION ALL
SELECT '[email protected]',    '000000000000000',    '2013-01-13 04:03:13.000'
UNION ALL
SELECT '[email protected]',    '6f52a254806809263ef9c426fbf77e890b173887',    '2013-01-12 14:07:31.000'
UNION ALL
SELECT '[email protected]',    '6f52a254806809263ef9c426fbf77e890b173887',    '2013-01-11 15:05:04.000'
UNION ALL
SELECT '[email protected]',    '868655000382418',    '2013-01-13 17:01:48.000'
UNION ALL
SELECT '[email protected]',    '868655000382418',    '2013-01-12 18:29:45.000'
 
 select *
 from (
SELECT *,row_number() over(partition by imei order by lastseen desc) as num
FROM test_table 
) t where num=1 
/*
sid         email                                              imei                                               lastseen
----------- -------------------------------------------------- -------------------------------------------------- -----------------------
1           [email protected]                                       000000000000000                                    2013-01-14 04:41:12.000
2           [email protected]                                       000000000000000                                    2013-01-13 22:22:30.000
3           [email protected]                                       000000000000000                                    2013-01-13 04:03:13.000
4           [email protected]                                       6f52a254806809263ef9c426fbf77e890b173887           2013-01-12 14:07:31.000
5           [email protected]                                        6f52a254806809263ef9c426fbf77e890b173887           2013-01-11 15:05:04.000
6           [email protected]                                       868655000382418                                    2013-01-13 17:01:48.000
7           [email protected]                                       868655000382418                                    2013-01-12 18:29:45.000
*/
 
--我想要的结果
/*
sid        email            imei                                        lastseen
1        [email protected]    000000000000000                                2013-01-14 04:41:12.000
4        [email protected]    6f52a254806809263ef9c426fbf77e890b173887    2013-01-12 14:07:31.000
6        [email protected]    868655000382418                                2013-01-13 17:01:48.000
*/

Answer 2

select a.* from test_table a
join
(
select [imei],max([lastseen])  as [lastseen]from test_table group by [imei]
)b  on a.imei = b.imei  and a.lastseen =b.lastseen 
order by a.imei 

Answer 3

  SELECT    *
  FROM      test_table a
  WHERE     EXISTS ( SELECT 1
                     FROM   ( SELECT    --email ,
                                        imei ,
                                        MAX(lastseen) lastseen
                              FROM      test_table
                              GROUP BY  --email --,
                                        imei
                            ) b
                     WHERE  --a.email = b.email
                            --AND 
                            a.imei = b.imei
                            AND a.lastseen = b.lastseen )

Answer 4

我贴一下执行计划吧，感谢各位！
【guguda2008】【zc10151】【DBA_Huangzj】

Answer 5

你用我的查询，再在imei ， lastseen这两个字段上加上索引，速度更快。

Answer 6

这东西太大范围了，很难说清楚，但是大量SQLServer书籍上对比查询的运行速度都是放在一起再执行。这方法不是我创建的，是一个台湾的MVP书上的方法。你也可以分开来执行，看看运行结果。最好是多执行几次，因为语句稍微改变，都有可能会引起重编译，编译是非常耗时和好资源的，所以多执行几次，然它真正以编译后的速度来执行。比较准确。

Answer 7

我看的是英文版，你确定你有信息看得懂？：《TShootPerfProbs2008》、《dissectingsqlserverexecutionplans》、《Microsoft SQL Server 2012 Performance Tuning Cookbook》、《SQL2008Server企业级平台管理实践》、《SqlServer2008查询性能优化》

Answer 8

             
 SELECT * FROM Test_Table AS a
WHERE NOT EXISTS(SELECT 1 FROM Test_Table AS x
WHERE x.imei=a.imei
AND x.lastseen>a.lastseen
)    

Answer 9

有时间先把技术内幕那套先看看，基础，很多书都从那里衍生出来，不过你也可以认为技术内幕是集成了其他书上的知识，这个不重要，学到就好。还有管理方面：SQL2008Server企业级平台管理实践 这本书相当不错。而且还是中文的。