62,041
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE tbtmp([name] VARCHAR(10),[time] VARCHAR(40))
INSERT dbo.tbtmp
SELECT '张三', '2011-6-18 00:00:01.....' UNION ALL
SELECT '张三', '2011-6-18 00:00:02 ' UNION ALL
SELECT '李四', '2011-6-18 00:00:20.....' UNION ALL
SELECT '李四', '2011-6-18 00:00:40.....' UNION ALL
SELECT '小五', '2011-6-18 00:02:20.....' UNION ALL
SELECT '小五', '2011-6-18 00:04:20.....' UNION ALL
SELECT '张三', '2011-6-17 00:00:01.....' UNION ALL
SELECT '张三', '2011-6-17 00:00:02 ' UNION ALL
SELECT '李四', '2011-6-17 00:00:20.....' UNION ALL
SELECT '李四', '2011-6-17 00:00:40.....' UNION ALL
SELECT '小五', '2011-6-17 00:02:20.....' UNION ALL
SELECT '小五', '2011-6-17 00:04:20.....'
SELECT a.[name] ,
LEFT(a.[time], 9) AS [time]
FROM dbo.tbtmp a
WHERE NOT EXISTS ( SELECT 1
FROM dbo.tbtmp
WHERE [name] = a.[name]
AND LEFT([time], 9) = LEFT(a.[time], 9)
AND LEFT([time], 18) > LEFT(a.[time], 18) )
DROP TABLE dbo.tbtmp
/*
name time
---------- ------------------
张三 2011-6-18
李四 2011-6-18
小五 2011-6-18
张三 2011-6-17
李四 2011-6-17
小五 2011-6-17
(6 行受影响)
*/
select top([pagesize]) * from datatable where 日期 not in
(select top ([pageindex]*[pagesize]) rdate from datatable order by 姓名,日期 desc) order by 姓名,日期 desc
select * from datatable order by 姓名,日期 desc