一个查询问题

[gingerkang]

数据表:info
字段:1content 2company 3riqi 4id 自增1
如何按riqi倒序每个company取两条记录？
希望不要用游标实现

[tx1icenhe]

select * from info a
where id in (select top 2 id from info where company=a.company order by riqi desc )

[tx1icenhe]

或者
select * from info a
where (select count(*) from info where company=a.company and riqi>=a.riqi)<=2

[paoluo]

--方法一：
Select * From info A
Where (Select Count(*) From info Where company = A.company And riqi > A.riqi) < 2
Order By company, riqi

--方法二：
Select * From info A
Where Exists (Select Count(*) From info Where company = A.company And riqi > A.riqi Having Count(*) < 2)
Order By company, riqi

--方法三：
Select * From info A
Where riqi In (Select TOP 2 riqi From info Where company = A.company Order By riqi Desc)
Order By company, riqi

[gingerkang]

CREATE TABLE [info] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[company] [int] NULL ,
[riqi] [int] NULL ,
[content] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[info] ON
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (1, 1, 1, '1')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (2, 1, 1, '1')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (3, 1, 2, '3')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (4, 1, 3, '4')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (5, 1, 3, '333')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (6, 1, 3, '555')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (7, 2, 1, '3')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (8, 2, 2, '2')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (9, 2, 2, '2')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (10, 3, 1, '1')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (11, 3, 1, '1')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (12, 3, 1, '1')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (13, 3, 1, '1')
INSERT INTO [dbo].[info] ([id], [company], [riqi], [content]) VALUES (14, 2, 2, '222')
SET IDENTITY_INSERT [dbo].[info] OFF
GO
就第一个回复能得到正确结果

[paoluo]

...

這樣的數據，看來的確是只能使用這一種了。