各位大神，分组取最大记录啊
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 'aaaa@163.com','000000000000000','2013-01-14 04:41:12.000'
UNION ALL
SELECT 'bbbb@163.com','000000000000000','2013-01-13 22:22:30.000'
UNION ALL
SELECT 'cccc@163.com','000000000000000','2013-01-13 04:03:13.000'
UNION ALL
SELECT 'dddd@163.com','6f52a254806809263ef9c426fbf77e890b173887','2013-01-12 14:07:31.000'
UNION ALL
SELECT 'eee@163.com','6f52a254806809263ef9c426fbf77e890b173887','2013-01-11 15:05:04.000'
UNION ALL
SELECT 'ffff@163.com','868655000382418','2013-01-13 17:01:48.000'
UNION ALL
SELECT 'gggg@163.com','868655000382418','2013-01-12 18:29:45.000'
SELECT * FROM test_table
/*
sid email imei lastseen
----------- -------------------------------------------------- -------------------------------------------------- -----------------------
1 aaaa@163.com 000000000000000 2013-01-14 04:41:12.000
2 bbbb@163.com 000000000000000 2013-01-13 22:22:30.000
3 cccc@163.com 000000000000000 2013-01-13 04:03:13.000
4 dddd@163.com 6f52a254806809263ef9c426fbf77e890b173887 2013-01-12 14:07:31.000
5 eee@163.com 6f52a254806809263ef9c426fbf77e890b173887 2013-01-11 15:05:04.000
6 ffff@163.com 868655000382418 2013-01-13 17:01:48.000
7 gggg@163.com 868655000382418 2013-01-12 18:29:45.000
*/
--我想要的结果
/*
sidemailimeilastseen
1aaaa@163.com0000000000000002013-01-14 04:41:12.000
4dddd@163.com6f52a254806809263ef9c426fbf77e890b1738872013-01-12 14:07:31.000
6ffff@163.com8686550003824182013-01-13 17:01:48.000
*/
sql 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 'aaaa@163.com', '000000000000000', '2013-01-14 04:41:12.000'
UNION ALL
SELECT 'bbbb@163.com', '000000000000000', '2013-01-13 22:22:30.000'
UNION ALL
SELECT 'cccc@163.com', '000000000000000', '2013-01-13 04:03:13.000'
UNION ALL
SELECT 'dddd@163.com', '6f52a254806809263ef9c426fbf77e890b173887', '2013-01-12 14:07:31.000'
UNION ALL
SELECT 'eee@163.com', '6f52a254806809263ef9c426fbf77e890b173887', '2013-01-11 15:05:04.000'
UNION ALL
SELECT 'ffff@163.com', '868655000382418', '2013-01-13 17:01:48.000'
UNION ALL
SELECT 'gggg@163.com', '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 aaaa@163.com 000000000000000 2013-01-14 04:41:12.000
2 bbbb@163.com 000000000000000 2013-01-13 22:22:30.000
3 cccc@163.com 000000000000000 2013-01-13 04:03:13.000
4 dddd@163.com 6f52a254806809263ef9c426fbf77e890b173887 2013-01-12 14:07:31.000
5 eee@163.com 6f52a254806809263ef9c426fbf77e890b173887 2013-01-11 15:05:04.000
6 ffff@163.com 868655000382418 2013-01-13 17:01:48.000
7 gggg@163.com 868655000382418 2013-01-12 18:29:45.000
*/
--我想要的结果
/*
sid email imei lastseen
1 aaaa@163.com 000000000000000 2013-01-14 04:41:12.000
4 dddd@163.com 6f52a254806809263ef9c426fbf77e890b173887 2013-01-12 14:07:31.000
6 ffff@163.com 868655000382418 2013-01-13 17:01:48.000
*/
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
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 )
我看的是英文版，你确定你有信息看得懂？：《TShootPerfProbs2008》、《dissectingsqlserverexecutionplans》、《Microsoft SQL Server 2012 Performance Tuning Cookbook》、《SQL2008Server企业级平台管理实践》、《SqlServer2008查询性能优化》
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
)
