T-SQL Group by 求助？Group by 后的各种类型的前2条数据.

aspnetchengxuyuan 2012-03-07 07:00:40

SQL：



CREATE TABLE [dbo].[USERINFO](

	[USER_ID] [int] IDENTITY(1,1) NOT NULL,

	[ORG_ID] [varchar](64) NOT NULL,

	[USER_NAME] [varchar](128) NOT NULL,

	[USER_SEX] [bit] NULL,

 CONSTRAINT [PK_USERINFO] PRIMARY KEY CLUSTERED 

(

	[USER_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]

GO

SET ANSI_PADDING OFF

GO

SET IDENTITY_INSERT [dbo].[USERINFO] ON

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (1, N'1', N'张三', 1)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (2, N'1', N'李四', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (3, N'1', N'王二', 1)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (4, N'2', N'赵六', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (5, N'2', N'孙五', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (6, N'3', N'马七', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (7, N'3', N'周八', 1)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (8, N'2', N'钱九', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (9, N'3', N'武十', 1)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (10, N'2', N'郑I一', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (11, N'1', N'蒋I二', 1)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (12, N'1', N'干I三', 0)

SET IDENTITY_INSERT [dbo].[USERINFO] OFF

Group by [ORG_ID],[USER_NAME] 后，获取[ORG_ID]为1,2,3..n的前2条数据，
即：
[ORG_ID] [USER_NAME]
1 干I三
1 蒋I二
2 钱九
2 孙五
3 马七
3 武十
....

高手请指点一二，小菜拜谢ing...

...全文

193 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

noteasytoregister 2012-03-07

打赏
举报

select [ORG_ID],[USER_NAME]

from (

select *,row_number() over (partition by ORG_ID order by USER_ID desc) rn

from USERINFO

) t

where rn<=2

order by [ORG_ID], [USER_ID] desc

Felixzhaowenzhong 2012-03-07

打赏
举报



CREATE TABLE [dbo].[USERINFO](

    [USER_ID] [int] IDENTITY(1,1) NOT NULL,

    [ORG_ID] [varchar](64) NOT NULL,

    [USER_NAME] [varchar](128) NOT NULL,

    [USER_SEX] [bit] NULL,

 CONSTRAINT [PK_USERINFO] PRIMARY KEY CLUSTERED 

(

    [USER_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]

GO

SET ANSI_PADDING OFF

GO

SET IDENTITY_INSERT [dbo].[USERINFO] ON

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (1, N'1', N'张三', 1)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (2, N'1', N'李四', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (3, N'1', N'王二', 1)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (4, N'2', N'赵六', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (5, N'2', N'孙五', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (6, N'3', N'马七', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (7, N'3', N'周八', 1)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (8, N'2', N'钱九', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (9, N'3', N'武十', 1)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (10, N'2', N'郑I一', 0)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (11, N'1', N'蒋I二', 1)

INSERT [dbo].[USERINFO] ([USER_ID], [ORG_ID], [USER_NAME], [USER_SEX]) VALUES (12, N'1', N'干I三', 0)

SET IDENTITY_INSERT [dbo].[USERINFO] OFF





go



SELECT DISTINCT b.*

FROM USERINFO AS a

CROSS APPLY

(

    SELECT TOP(2) * FROM USERINFO WHERE a.ORG_ID=ORG_ID ORDER BY USER_NAME DESC

) AS b

叶子 2012-03-07

打赏
举报



SELECT  ORG_ID ,

        [USER_NAME]

FROM    [USERINFO] a

WHERE   [USER_ID] IN ( SELECT TOP 2

                                [USER_ID]

                       FROM     [USERINFO]

                       WHERE    [ORG_ID] = a.[ORG_ID]

                       ORDER BY [USER_ID] DESC )

ORDER BY ORG_ID

/*

ORG_ID   USER_NAME

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

1        蒋I二

1        干I三

2        钱九

2        郑I一

3        周八

3        武十

*/

叶子 2012-03-07

打赏
举报



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

--  Author: liangCK 小梁

--  Title : 查每个分组前N条记录

--  Date  : 2008-11-13 17:19:23

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



--> 生成测试数据: #T

IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T

CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)

INSERT INTO #T

SELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALL

SELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALL

SELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALL

SELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALL

SELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALL

SELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALL

SELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALL

SELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALL

SELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALL

SELECT '010',3,'黄占涛','SQL技术手册','2006-01-01'



--SQL查询如下:



--按GID分组,查每个分组中Date最新的前2条记录





--1.字段ID唯一时:

SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC)



--2.如果ID不唯一时:

SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date)



--SQL Server 2005 使用新方法



--3.使用ROW_NUMBER()进行排位分组

SELECT ID,GID,Author,Title,Date

FROM

(

   SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),*

   FROM #T

) AS T

WHERE rid<=2



--4.使用APPLY

SELECT DISTINCT b.*

FROM #T AS a

CROSS APPLY

(

    SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC

) AS b





--结果

/*



ID   GID         Author                        Title                                   Date

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

003  1           格罗夫Groff.J.R.                 SQL完全手册                                 2009-07-01 00:00:00.000

004  1           KalenDelaney                  SQLServer2005技术内幕存储引擎                   2008-08-01 00:00:00.000

005  2           Alex.Kriegel.Boris.M.Trukhnov SQL宝典                                   2007-10-05 00:00:00.000

007  2           胡百敬                           SQLServer2005数据库开发详解                    2008-06-15 00:00:00.000

009  3           赵松涛                           SQLServer2005系统管理实录                     2008-10-01 00:00:00.000

010  3           黄占涛                           SQL技术手册                                 2006-01-01 00:00:00.000



(6 行受影响)

*/