可能前次的问题我没有说清,这次具体一点。
数据:
检验时间
2010-1-1 9:00
2010-1-1 10:00
2010-1-1 11:00
2010-1-1 12:00
2010-1-1 13:00按时间倒序排列时用
ORDER BY
检验时间 Desc
将返回第一个数据 2010-1-1 13:00
但是我想要返回的是按倒序排列时的第二个数据,也就是2010-1-1 12:00
请教怎么能实现
数据:
检验时间
2010-1-1 9:00
2010-1-1 10:00
2010-1-1 11:00
2010-1-1 12:00
2010-1-1 13:00按时间倒序排列时用
ORDER BY
检验时间 Desc
将返回第一个数据 2010-1-1 13:00
但是我想要返回的是按倒序排列时的第二个数据,也就是2010-1-1 12:00
请教怎么能实现
-- Author :SQL77(只为思齐老)
-- Date :2010-03-28 20:30:39
-- Version:
-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
-- May 3 2005 23:18:38
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([检验时间] datetime)
insert #TB
select '2010-1-1 9:00' union all
select '2010-1-1 10:00' union all
select '2010-1-1 11:00' union all
select '2010-1-1 12:00' union all
select '2010-1-1 13:00'
--------------开始查询--------------------------select
TOP 1 *
from
#TB T
WHERE 检验时间<>(SELECT MAX(检验时间) FROM #TB)
ORDER BY 检验时间 DESC
----------------结果----------------------------
/* (所影响的行数为 5 行)检验时间
------------------------------------------------------
2010-01-01 12:00:00.000(所影响的行数为 1 行)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-28 20:33:14
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([检验时间] datetime)
insert [tb]
select '2010-1-1 9:00' union all
select '2010-1-1 10:00' union all
select '2010-1-1 11:00' union all
select '2010-1-1 12:00' union all
select '2010-1-1 13:00'
--------------开始查询--------------------------
select
检验时间
from
(
select id=row_number()over(order by 检验时间 desc),* from tb
)t
where
id=2
----------------结果----------------------------
/* 检验时间
-----------------------
2010-01-01 12:00:00.000(1 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-28 20:33:14
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([检验时间] datetime)
insert [tb]
select '2010-1-1 9:00' union all
select '2010-1-1 10:00' union all
select '2010-1-1 11:00' union all
select '2010-1-1 12:00' union all
select '2010-1-1 13:00'
--------------开始查询--------------------------
select top 1 * from (select top 2 * from tb order by 检验时间 Desc)t order by t.检验时间
---------------结果----------------------------
/* 检验时间
-----------------------
2010-01-01 12:00:00.000(1 行受影响)
*/
还要多谢别的各位大哥,谢谢了