SQL 按组别过滤指定条件的首行记录

源贴：http://topic.csdn.net/u/20100417/19/ebdc216a-acc9-4e04-80f3-dbada0b4f3fe.html?74846

 

数据类型：
Code char(6)
CreateTime datetime
Price float
Status bit

数据如下：

Code       CreateTime        Price Status
031002 2008-10-17 15:00:15 3.58    1
031002 2008-10-17 15:00:16 5.53    1
031002 2008-10-17 15:00:17 4.54    1
031002 2008-10-17 15:00:18 4.44    1
031002 2008-10-17 15:00:19 4.34    1
031002 2008-10-17 15:00:20 4.24    1
031012 2008-10-17 15:00:15 3.58    0
031012 2008-10-17 15:00:16 5.53    0
031012 2008-10-17 15:00:17 4.54    0
031012 2008-10-17 15:00:18 4.44    0
031012 2008-10-17 15:00:19 4.34    1
031012 2008-10-17 15:00:20 4.24    1
031012 2008-10-17 15:00:18 3.51    0
031012 2008-10-17 15:00:19 3.52    0
031012 2008-10-17 15:00:20 2.50    0
031012 2008-10-17 15:00:21 3.51    0
031012 2008-10-17 15:00:22 3.52    0
031012 2008-10-17 15:00:23 2.50    0
031012 2008-10-17 15:00:24 3.51    0
031012 2008-10-17 15:00:25 3.52    0
031012 2008-10-17 15:00:26 2.50    0
031033 2008-10-17 15:00:21 4.53    0
031033 2008-10-17 15:00:22 3.55    0
031033 2008-10-17 15:00:23 3.55    0
031033 2008-10-17 15:00:24 5.57    0
031033 2008-10-17 15:00:25 5.57    1
031033 2008-10-17 15:00:26 5.57    1
031033 2008-10-17 15:00:27 5.57    1
031033 2008-10-17 15:00:28 5.57    1


我想要的结果是

031012 2008-10-17 15:00:19 4.34    1
031033 2008-10-17 15:00:25 5.57    1


得出这个结果的要求是: 按照Code分组，按照时间asc排序 Status从0变为1的第一条，
如果该Code的Status全是0或者是1就不要，只有当Status为0，比他晚的时间突然变成1了就要该第一条。

 

/*------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-17 19:56:07
--  Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
    Jul  9 2008 14:43:34
    Copyright (c) 1988-2008 Microsoft Corporation
    Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

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

IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([Code] NVARCHAR(10),[CreateTime] DATETIME,[Price] DECIMAL(18,2),[Status] NVARCHAR(10))
INSERT [tb]
SELECT '031002',N'2008-10-17 15:00:15',3.58,'1' UNION ALL
SELECT '031002',N'2008-10-17 15:00:16',5.53,'1' UNION ALL
SELECT '031002',N'2008-10-17 15:00:17',4.54,'1' UNION ALL
SELECT '031002',N'2008-10-17 15:00:18',4.44,'1' UNION ALL
SELECT '031002',N'2008-10-17 15:00:19',4.34,'1' UNION ALL
SELECT '031002',N'2008-10-17 15:00:20',4.24,'1' UNION ALL
SELECT '031012',N'2008-10-17 15:00:15',3.58,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:16',5.53,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:17',4.54,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:18',4.44,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:19',4.34,'1' UNION ALL
SELECT '031012',N'2008-10-17 15:00:20',4.24,'1' UNION ALL
SELECT '031012',N'2008-10-17 15:00:18',3.51,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:19',3.52,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:20',2.50,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:21',3.51,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:22',3.52,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:23',2.50,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:24',3.51,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:25',3.52,'0' UNION ALL
SELECT '031012',N'2008-10-17 15:00:26',2.50,'0' UNION ALL
SELECT '031033',N'2008-10-17 15:00:21',4.53,'0' UNION ALL
SELECT '031033',N'2008-10-17 15:00:22',3.55,'0' UNION ALL
SELECT '031033',N'2008-10-17 15:00:23',3.55,'0' UNION ALL
SELECT '031033',N'2008-10-17 15:00:24',5.57,'0' UNION ALL
SELECT '031033',N'2008-10-17 15:00:25',5.57,'1' UNION ALL
SELECT '031033',N'2008-10-17 15:00:26',5.57,'1' UNION ALL
SELECT '031033',N'2008-10-17 15:00:27',5.57,'1' UNION ALL
SELECT '031033',N'2008-10-17 15:00:28',5.57,'1' UNION ALL
SELECT '031034',N'2008-10-17 15:00:26',5.57,'1' UNION ALL
SELECT '031034',N'2008-10-17 15:00:27',5.57,'0' UNION ALL
SELECT '031034',N'2008-10-17 15:00:28',5.57,'1'
GO
--SELECT * FROM [tb]

-->SQL查询如下:
select * from tb t
where CreateTime=(select min(CreateTime) from tb a where Code=t.Code and Status=1
        and CreateTime>(select min(CreateTime) from tb where Code=a.Code and Status=0))
    and Status=1

/*
Code       CreateTime              Price                                   Status
---------- ----------------------- --------------------------------------- ----------
031012     2008-10-17 15:00:19.000 4.34                                    1
031033     2008-10-17 15:00:25.000 5.57                                    1
031034     2008-10-17 15:00:28.000 5.57                                    1

(3 行受影响)
*/