求一个取指定列的SQL

[wapit]

数据库有表Test1125，含列ID及CID两字段。现求一语句查出每个CID的前2条记录，并按ID升序排序。表及数据附后。想要的结果是：
ID,CID
1,1
2,1
6,2
7,2
11,3
12,3

谢谢！

/****** Object:  Table [dbo].[Test1125]    Script Date: 11/25/2011 15:32:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test1125]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Test1125](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CID] [int] NULL,
 CONSTRAINT [PK_Test1125] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET IDENTITY_INSERT [dbo].[Test1125] ON
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (1, 1)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (2, 1)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (3, 1)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (4, 1)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (5, 1)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (6, 2)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (7, 2)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (8, 2)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (9, 2)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (10, 2)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (11, 3)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (12, 3)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (13, 3)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (14, 3)
INSERT [dbo].[Test1125] ([ID], [CID]) VALUES (15, 3)
SET IDENTITY_INSERT [dbo].[Test1125] OFF

[yibey]

select * from [Test1125] a where  (select count(1) from Test1125 where cid= a.cid and  id<a.id)<2

[wapit]

[Quote=引用 1 楼 yibey 的回复:]
select * from [Test1125] a where (select count(1) from Test1125 where cid= a.cid and id<a.id)<2
[/Quote]
谢谢，先理解消化下

[yibey]

这个查询的前提是ID按照由小到大排序的，如果不是的话,使用rownumber造一个由小到大的ID出来

[accomp]

写了下 没写出来
继续想

[wapit]

问题来了，我实际的应用中，排序不是按照ID来的，按另一列，暂时定义为“CD”吧，CD的值有重复……
请问下这个如何处理？ 谢谢！
[code=SQL]
/****** Object: Table [dbo].[Test1125] Script Date: 11/25/2011 16:05:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test1125]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Test1125](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CID] [int] NULL,
[CD] [int] NULL,
CONSTRAINT [PK_Test1125] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET IDENTITY_INSERT [dbo].[Test1125] ON
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (1, 1, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (2, 1, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (3, 1, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (4, 1, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (5, 1, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (6, 2, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (7, 2, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (8, 2, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (9, 2, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (10, 2, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (11, 3, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (12, 3, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (13, 3, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (14, 3, 0)
INSERT [dbo].[Test1125] ([ID], [CID], [CD]) VALUES (15, 3, 0)
SET IDENTITY_INSERT [dbo].[Test1125] OFF
[/Code]

[yibey]

请问你上面的数据需要什么结果

[阿非]

按照CD,你期望的结果？

[风2013]

[Quote=引用 5 楼 wapit 的回复:]
问题来了，我实际的应用中，排序不是按照ID来的，按另一列，暂时定义为“CD”吧，CD的值有重复……
请问下这个如何处理？ 谢谢！
[/Quote]
没看懂

[wapit]

[Quote=引用 6 楼 yibey 的回复:]请问你上面的数据需要什么结果
[/Quote]
[Quote=引用 7 楼 sandy945 的回复:]按照CD,你期望的结果？
[/Quote]
不好意思，没表达清楚……期望的结果还是：
ID,CID,CD
1,1,0
2,1,0
6,2,0
7,2,0
11,3,0
12,3,0
即先按CD排序，再按ID排序……

[阿非]

你在#5贴的数据 不行，因为cd都是0，和没有一样

[风2013]

看不出特色..似乎和第一个查询没区别..

[yibey]

with cte as
(
   select row_number() over (order by cd,id) as newid,* from [Test1125]
)

select * from cte a where  (select count(1) from cte where cid= a.cid and  newid<a.newid)<2

如果没有指定的ID就造一个ID了 使用row_number方法咯

[wapit]

[Quote=引用 10 楼 sandy945 的回复:]

你在#5贴的数据 不行，因为cd都是0，和没有一样
[/Quote]问题就是因为CD有重复，使用把一楼的条件id<a.id替换成CD<a.CD时，查出来的记录不是想要的……

[wapit]

OL, 12楼基本实现我要达到的结果了

遗憾的是，我把这语句移到项目数据库中时（数据表有几万行数据，排序列为日期型），执行了好几分钟还没有出结果，与项目中毫秒级的响应时间要求相差太大了，只能想其它办法实现了。

谢谢yibey与所有朋友们。