查询分组数据指行条件满足的行的上一条记录的技巧

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-03-26 11:01:09

--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

--          Mar 29 2009 10:27:29

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

--------------------------------------------------------------------------

--> 生成测试数据表:tb

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([A] NVARCHAR(10),[B] NVARCHAR(10),[C] NVARCHAR(10))

INSERT [tb]

SELECT '00001','01','aaa' UNION ALL

SELECT '00001','02','bbb' UNION ALL

SELECT '00002','01','www' UNION ALL

SELECT '00002','02','yyy' UNION ALL

SELECT '00002','03','ttt' UNION ALL

SELECT '00002','04','bbb'

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

 

declare @c nvarchar(10)

set @c='bbb'

 

select a,b,c=(select top 1 c from tb where a=t.A and b<t.B order by b desc)

from tb t

where c='bbb'

/*

a          b          c

---------- ---------- ----------

00001      02         aaa

00002      04         ttt

 

(2 行受影响)

*/