我如何在SAME表上分组，按日期排序并限制返回的行数？ [英] How can I group on the SAME table, order by date and limit the number of rows returned?
本文介绍了我如何在SAME表上分组，按日期排序并限制返回的行数？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想返回一个由ID_F分组的集合，它在ID_F中的数量是有限的，并在ID_F中按日期排序。
这是我的数据库设置： p>
CREATE TABLE [dbo]。[U]（
[ID] [uniqueidentifier] NOT NULL，
[名称] [nvarchar]（max）NULL，
CONSTRAINT [PK_U] 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]
CREATE TABLE [dbo]。[F]（
[ID] [ uniqueidentifier] NOT NULL，
[Name] [nvarchar]（max）NOT NULL，
CONSTRAINT [PK_F] 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] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [ dbo]。[E]（
[ID] [uniqueidentifier] NOT NULL，
[Name] [nvarch ar]（max）NOT NULL，
CONSTRAINT [PK_E] 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] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo]。[U_F_E]（
[ID ] [uniqueidentifier] NOT NULL，
[ID_U] [uniqueidentifier] NOT NULL，
[ID_F] [uniqueidentifier] NOT NULL，
[ID_E] [uniqueidentifier] NOT NULL，
[LastUsed] [DateTime] NOT NULL，
CONSTRAINT [PK_U_F_E] 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]
declare @ID uniqueidentifier
declare @N nvarchar（50）
$ b $ set @ID ='53159BEC-88C7-48C3-B2D1-63926ED28A64'
set @N ='Bob'
插入[U]
值（
@ ID，
@N
）
$ b $ set @ID ='3DF035A6-C456-4AB4-8BBB-E8FF86A2A033'
set @N ='Tom'
插入[U]
值（
@ID，
@N
）
设置@ID ='F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A'
set @N ='F1'
插入[F]
值（
@ID，
@N
）
set @ID ='220AD0BC-3F20-4F1C-A9B8-81AC08EA679A'
set @N ='f2'
插入[F]
值（
@ID，
@N
）
$ b $ set @ID ='3708E1E8-6E7E-4E8F-B415-3F09CF22CB54'
set @N ='f3'
插入[F]
值（
@ID，
@N
）
set @ID ='EA219DA4-6C13-42AA-A1B6-DE786724A554'
set @N ='e1'
插入[E]
值（
@ID，
@N
）
set @ID ='6158853D-E91A-4AA4-AC0F-5D26F766C677'
set @N ='e2'
插入[E]
值（
@ID，
@N
）
set @ID ='F697F78A-6990-443E-9D4B-D9BCF046DAF5'
set @N ='e3'
插入[E]
值（
@ID，
@N
）
set @ID ='A7DB1FA5-28D6-4F5A-8BA0-628EA2A630A8'
set @N ='e4 '
插入[E]
值（
@ID，
@N
）
set @ID = '8ABEE9FA-B255-4409-A2DD-30CFF62EEE3C'
set @N ='e5'
插入[E]
值（
@ID，
@N
）
set @ID ='DA644D24-AFA5-467E-A76C-A5E94DA4FA62'
set @N ='e6'
插入[E]
值（
@ID，
@N
）
插入[U_F_E]
值（
NEWID（），
'53159BEC-88C7-48C3-B2D1-63926ED28A64'， - bob
'F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A'， - f1
'EA219DA4-6C13-42AA-A1B6-DE786724A554'， - e1
GetDate（）
）
WAITFOR DELAY '00：00：01';
插入[U_F_E]
值（
NEWID（），
'53159BEC-88C7-48C3-B2D1-63926ED28A64'， - bob
'3708E1E8-6E7E -4E8F-B415-3F09CF22CB54'， - f3
'EA219DA4-6C13-42AA-A1B6-DE786724A554'， - e1
GetDate（）
）
WAITFOR DELAY'00：00：01';
插入[U_F_E]
值（
NEWID（），
'3DF035A6-C456-4AB4-8BBB-E8FF86A2A033'， - tom
'220AD0BC-3F20 -4F1C-A9B8-81AC08EA679A'， - f2
'8ABEE9FA-B255-4409-A2DD-30CFF62EEE3C'， - e5
GetDate（）
）
WAITFOR DELAY '00：00：01';
插入[U_F_E]
值（
NEWID（），
'53159BEC-88C7-48C3-B2D1-63926ED28A64'， - bob
'220AD0BC-3F20 -4F1C-A9B8-81AC08EA679A'， - f2
'8ABEE9FA-B255-4409-A2DD-30CFF62EEE3C'， - e5
GetDate（）
）
WAITFOR DELAY'00：00：01';
插入[U_F_E]
值（
NEWID（），
'53159BEC-88C7-48C3-B2D1-63926ED28A64'， - bob
'220AD0BC-3F20 -4F1C-A9B8-81AC08EA679A'， - f2
'F697F78A-6990-443E-9D4B-D9BCF046DAF5'， - e3
GetDate（）
）
WAITFOR DELAY'00：00：01';
插入[U_F_E]
值（
NEWID（），
'53159BEC-88C7-48C3-B2D1-63926ED28A64'， - bob
'220AD0BC-3F20 -4F1C-A9B8-81AC08EA679A'， - f2
'6158853D-E91A-4AA4-AC0F-5D26F766C677'， - e2
GetDate（）
）
WAITFOR DELAY'00：00：01';
插入[U_F_E]
值（
NEWID（），
'3DF035A6-C456-4AB4-8BBB-E8FF86A2A033'， - tom
'220AD0BC-3F20 -4F1C-A9B8-81AC08EA679A'， - f2
'EA219DA4-6C13-42AA-A1B6-DE786724A554'， - e1
GetDate（）
）
WAITFOR DELAY'00：00：01';
插入[U_F_E]
值（
NEWID（），
'53159BEC-88C7-48C3-B2D1-63926ED28A64'， - bob
'220AD0BC-3F20 -4F1C-A9B8-81AC08EA679A'， - f2
'A7DB1FA5-28D6-4F5A-8BA0-628EA2A630A8'， - e4
GetDate（）
）
$ b TRY 0：
declare @限制int
set @limit = 3
declare @myuser uniqueidentifier
set @myuser ='53159BEC-88C7-48C3-B2D1-63926ED28A64'
从[U_F_E]中选择ID_F，e.Name，LastUsed作为ufe
在ufe.ID_E = e.ID
上加入E作为e其中ID_U = @myuser
通过ID_F组合，e.Name ，LastUsed
order by LastUsed desc
这里是输出，注意它被分组和排序按日期...但它不限制返回的ID_F行的数量
ID_F名称LastUsed
220AD0BC-3F20 -4F1C-A9B8-81AC08EA679A e3 2016-04-14 07：58：54.953
220AD0BC-3F20-4F1C-A9 B8-81AC08EA679A e4 2016-04-14 07：51：49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07：51：47.537
220AD0BC-3F20-4F1C-A9B8- 81AC08EA679A e5 2016-04-14 07：51：45.533
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07：51：43.530
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07：51：42.530
当我尝试限制使用返回的行数这：
declare @limit int
set @limit = 3
declare @myuser uniqueidentifier
set @myuser ='53159BEC-88C7-48C3-B2D1-63926ED28A64'
选择TOP（@limit）ID_F，e.Name，LastUsed from [U_F_E] as ufe
在ufe.ID_E = e.ID
其中ID_U = @myuser
group by ID_F，e.Name，LastUsed
by order by LastUsed desc
我得到这个：
ID_F名称LastUsed
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58 ：54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07：51：49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07：51：47.537
当我真的需要这个时：
ID_F姓名LastUsed
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07：58：54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07：51：49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07：51：47.537
3708E1E8-6E7E-4E8F-B415- 3F09CF22CB54 e1 2016-04-14 07:51:43.530
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
select id_f，name，lastused
from（
select ID_F，e.Name，LastUsed
，dense_rank（）over（partition by ID_U order由ID_F）从[U_F_E]作为rnk
作为ufe
将e作为e加入到ufe.ID_E = e.ID
其中ID_U = @myuser）t
其中rnk< = 3
订单通过lastused desc
返回，但不考虑返回的限制行：
id_f lastused
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC -3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07：51：49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07：51：47.537
220AD0BC-3F20 -4F1C-A9B8-81AC08EA679A e5 2016-04-14 07：51：45.533
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07：51：43.530
F20AD0BC-3F20-4F1C -A9B8-81AC08EA679A e1 2016-04-14 07：51：42.530
我假设3 应该限制ID_F行像这样返回？
id_f lastused
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07：58：54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07：51：49.560
220AD0BC-3F20-4F1C-A9B8- 81AC08EA679A e2 2016-04-14 07：51：47.537
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07：51：43.530
F20AD0BC-3F20-4F1C- A9B8-81AC08EA679A e1 2016-04-14 07：51：42.530
TRY 2：
SELECT id_f，fe。*
FROM [U_F_E] uf
CROSS APPLY
（
SELECT TOP（@limit）e.Name，LastUsed
FROM [U_F_E] fe
JOIN [E] e
ON e.id = fe.id_e
WHERE id_f = uf。 id_f
ORDER BY LastUsed DESC
）fe
WHERE id_u = @myuser
id_f名称LastUsed
220AD0BC -3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07：58：54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 07：55：38.870
220AD0BC-3F20 -4F1C-A9B8-81AC08EA679A e4 2016-04-14 07：51：49.560
22 0AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07：58：54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 07：55：38.870
220AD0BC- 3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07：51：49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07：58：54.953
220AD0BC-3F20- 4F1C-A9B8-81AC08EA679A e5 2016-04-14 07：55：38.870
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07：51：49.560
F20AD0BC-3F20-4F1C- A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07:51:43.530
220AD0BC-3F20-4F1C-A9B8- 81AC08EA679A e3 2016-04-14 07：58：54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 07：55：38.870
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07：51：49.560
TRY 3：
select id_f，name，lastused
from（
select ID_F，e.Name，LastUsed
，row_number（）over按ID_F顺序进行分区，由lastused分区d esc）从[U_F_E]作为rnk
作为ufe
将e作为e加入到ufe.ID_E = e.ID
其中ID_U = @myuser）t
其中rnk< = @limit
by lastused desc
给出这个：
id_f lastused
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07：58：54.953
220AD0BC-3F20 -4F1C-A9B8-81AC08EA679A e4 2016-04-14 07：51：49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07：51：47.537
3708E1E8-6E7E-4E8F -B415-3F09CF22CB54 e1 2016-04-14 07:51:43.530
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
但是当我在表中添加另一行时，请注意370未分组
id_f lastused
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e5 2016-04-14 08：53：21.233
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016- 04-14 08：50：03.187
220AD0BC- 3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 08：45：00.897
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 08：43：42.253
220AD0BC-3F20- 4F1C-A9B8-81AC08EA679A e2 2016-04-14 07：51：47.537
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07：51：43.530
F20AD0BC-3F20-4F1C- A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
TRY 4：
select id_f，name，lastused
from（
select ID_F，e.Name，LastUsed
，row_number （）over（由ID_F order by lastused desc分区）作为[u_F_E]中的rnk
作为ufe
将e作为e加入到ufe.ID_E = e.ID
其中ID_U = @myuser） t
其中rnk< = @limit
按ID_F排序，lastused desc
产生：（当限制设置为3时）
id_f lastused
3708E1E8-6E7E-4E8F-B415- 3F09CF22CB54 e5 2016-04-14 08：53：21.233
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07：51：43.530
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 08：50：03.187
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 08： 45：00.897
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 08：43：42.253
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 09:00： 27.780
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07：51：42.530
谢谢dnoeth！
解决方案在 chat 原来，这主要是一个显示问题：
<$ p $ （
select ID_F，e.Name，LastUsed
，row_number（） - ranking $ b $（
）select ID_f，name，lastused
from
b over（由ID_F分区 - 对于每个ID_F
order by lastused desc）作为rnk - 基于降序日期
从[U_F_E]作为ufe
在ufe.ID_E = e.ID
处加入E作为e，其中ID_U = @myuser
）t
其中rnk< = @limit
按ID_F排序，lastused desc - 排序正确的方法:-)
I would like to return a set that is grouped by ID_F, limited in count within ID_F and sorted by date within ID_F
Here is my database setup:
CREATE TABLE [dbo].[U](
[ID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](max) NULL,
CONSTRAINT [PK_U] 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]
CREATE TABLE [dbo].[F](
[ID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_F] 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] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[E](
[ID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_E] 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] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[U_F_E](
[ID] [uniqueidentifier] NOT NULL,
[ID_U] [uniqueidentifier] NOT NULL,
[ID_F] [uniqueidentifier] NOT NULL,
[ID_E] [uniqueidentifier] NOT NULL,
[LastUsed][DateTime] NOT NULL,
CONSTRAINT [PK_U_F_E] 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]
declare @ID uniqueidentifier
declare @N nvarchar(50)
set @ID = '53159BEC-88C7-48C3-B2D1-63926ED28A64'
set @N = 'Bob'
insert into [U]
Values(
@ID,
@N
)
set @ID = '3DF035A6-C456-4AB4-8BBB-E8FF86A2A033'
set @N = 'Tom'
insert into [U]
Values(
@ID,
@N
)
set @ID = 'F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A'
set @N = 'F1'
insert into [F]
Values(
@ID,
@N
)
set @ID = '220AD0BC-3F20-4F1C-A9B8-81AC08EA679A'
set @N = 'f2'
insert into [F]
Values(
@ID,
@N
)
set @ID = '3708E1E8-6E7E-4E8F-B415-3F09CF22CB54'
set @N = 'f3'
insert into [F]
Values(
@ID,
@N
)
set @ID = 'EA219DA4-6C13-42AA-A1B6-DE786724A554'
set @N = 'e1'
insert into [E]
Values(
@ID,
@N
)
set @ID = '6158853D-E91A-4AA4-AC0F-5D26F766C677'
set @N = 'e2'
insert into [E]
Values(
@ID,
@N
)
set @ID = 'F697F78A-6990-443E-9D4B-D9BCF046DAF5'
set @N = 'e3'
insert into [E]
Values(
@ID,
@N
)
set @ID = 'A7DB1FA5-28D6-4F5A-8BA0-628EA2A630A8'
set @N = 'e4'
insert into [E]
Values(
@ID,
@N
)
set @ID = '8ABEE9FA-B255-4409-A2DD-30CFF62EEE3C'
set @N = 'e5'
insert into [E]
Values(
@ID,
@N
)
set @ID = 'DA644D24-AFA5-467E-A76C-A5E94DA4FA62'
set @N = 'e6'
insert into [E]
Values(
@ID,
@N
)
insert into [U_F_E]
Values(
NEWID(),
'53159BEC-88C7-48C3-B2D1-63926ED28A64', -- bob
'F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A', -- f1
'EA219DA4-6C13-42AA-A1B6-DE786724A554', -- e1
GetDate()
)
WAITFOR DELAY '00:00:01';
insert into [U_F_E]
Values(
NEWID(),
'53159BEC-88C7-48C3-B2D1-63926ED28A64', -- bob
'3708E1E8-6E7E-4E8F-B415-3F09CF22CB54', -- f3
'EA219DA4-6C13-42AA-A1B6-DE786724A554', -- e1
GetDate()
)
WAITFOR DELAY '00:00:01';
insert into [U_F_E]
Values(
NEWID(),
'3DF035A6-C456-4AB4-8BBB-E8FF86A2A033', -- tom
'220AD0BC-3F20-4F1C-A9B8-81AC08EA679A', -- f2
'8ABEE9FA-B255-4409-A2DD-30CFF62EEE3C', -- e5
GetDate()
)
WAITFOR DELAY '00:00:01';
insert into [U_F_E]
Values(
NEWID(),
'53159BEC-88C7-48C3-B2D1-63926ED28A64', -- bob
'220AD0BC-3F20-4F1C-A9B8-81AC08EA679A', -- f2
'8ABEE9FA-B255-4409-A2DD-30CFF62EEE3C', -- e5
GetDate()
)
WAITFOR DELAY '00:00:01';
insert into [U_F_E]
Values(
NEWID(),
'53159BEC-88C7-48C3-B2D1-63926ED28A64', -- bob
'220AD0BC-3F20-4F1C-A9B8-81AC08EA679A', -- f2
'F697F78A-6990-443E-9D4B-D9BCF046DAF5', -- e3
GetDate()
)
WAITFOR DELAY '00:00:01';
insert into [U_F_E]
Values(
NEWID(),
'53159BEC-88C7-48C3-B2D1-63926ED28A64', -- bob
'220AD0BC-3F20-4F1C-A9B8-81AC08EA679A', -- f2
'6158853D-E91A-4AA4-AC0F-5D26F766C677', -- e2
GetDate()
)
WAITFOR DELAY '00:00:01';
insert into [U_F_E]
Values(
NEWID(),
'3DF035A6-C456-4AB4-8BBB-E8FF86A2A033', -- tom
'220AD0BC-3F20-4F1C-A9B8-81AC08EA679A', -- f2
'EA219DA4-6C13-42AA-A1B6-DE786724A554', -- e1
GetDate()
)
WAITFOR DELAY '00:00:01';
insert into [U_F_E]
Values(
NEWID(),
'53159BEC-88C7-48C3-B2D1-63926ED28A64', -- bob
'220AD0BC-3F20-4F1C-A9B8-81AC08EA679A', -- f2
'A7DB1FA5-28D6-4F5A-8BA0-628EA2A630A8', -- e4
GetDate()
)
TRY 0:
declare @limit int
set @limit = 3
declare @myuser uniqueidentifier
set @myuser = '53159BEC-88C7-48C3-B2D1-63926ED28A64'
select ID_F, e.Name, LastUsed from [U_F_E] as ufe
join E as e on ufe.ID_E = e.ID
where ID_U = @myuser
group by ID_F, e.Name, LastUsed
order by LastUsed desc
here is the output, notice it is grouped and sorted by date... BUT it does not limit the number of ID_F rows returned
ID_F Name LastUsed
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07:51:49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07:51:47.537
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 07:51:45.533
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07:51:43.530
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
When I try to limit the number of rows returned using this:
declare @limit int
set @limit = 3
declare @myuser uniqueidentifier
set @myuser = '53159BEC-88C7-48C3-B2D1-63926ED28A64'
select TOP(@limit) ID_F, e.Name, LastUsed from [U_F_E] as ufe
join E as e on ufe.ID_E = e.ID
where ID_U = @myuser
group by ID_F, e.Name, LastUsed
order by LastUsed desc
I get this:
ID_F Name LastUsed
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07:51:49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07:51:47.537
When I really want this:
ID_F Name LastUsed
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07:51:49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07:51:47.537
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07:51:43.530
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
Can you help me?
TRY 1:
select id_f,name,lastused
from (
select ID_F, e.Name, LastUsed
,dense_rank() over(partition by ID_U order by ID_F) as rnk
from [U_F_E] as ufe
join E as e on ufe.ID_E = e.ID
where ID_U = @myuser) t
where rnk <= 3
order by lastused desc
returns, but does not take into account limiting rows returned:
id_f name lastused
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07:51:49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07:51:47.537
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 07:51:45.533
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07:51:43.530
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
I assume that the "3" should limit the ID_F rows returned like this?
id_f name lastused
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07:51:49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07:51:47.537
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07:51:43.530
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
TRY 2:
SELECT id_f, fe.*
FROM [U_F_E] uf
CROSS APPLY
(
SELECT TOP (@limit) e.Name, LastUsed
FROM [U_F_E] fe
JOIN [E] e
ON e.id = fe.id_e
WHERE id_f = uf.id_f
ORDER BY LastUsed DESC
) fe
WHERE id_u = @myuser
but that is WAY off ... it returns crazy number of rows.
id_f Name LastUsed
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 07:55:38.870
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07:51:49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 07:55:38.870
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07:51:49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 07:55:38.870
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07:51:49.560
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07:51:43.530
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 07:55:38.870
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07:51:49.560
TRY 3:
select id_f,name,lastused
from (
select ID_F, e.Name, LastUsed
,row_number() over(partition by ID_F order by lastused desc) as rnk
from [U_F_E] as ufe
join E as e on ufe.ID_E = e.ID
where ID_U = @myuser) t
where rnk <= @limit
order by lastused desc
Gives this:
id_f name lastused
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 07:58:54.953
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 07:51:49.560
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07:51:47.537
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07:51:43.530
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
but when I added another row to the table, notice 370 is NOT grouped
id_f name lastused
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e5 2016-04-14 08:53:21.233
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 08:50:03.187
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 08:45:00.897
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 08:43:42.253
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e2 2016-04-14 07:51:47.537
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07:51:43.530
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
TRY 4:
select id_f,name,lastused
from (
select ID_F, e.Name, LastUsed
,row_number() over(partition by ID_F order by lastused desc) as rnk
from [U_F_E] as ufe
join E as e on ufe.ID_E = e.ID
where ID_U = @myuser) t
where rnk <= @limit
order by ID_F, lastused desc
Produces: (when limit is set to 3)
id_f name lastused
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e5 2016-04-14 08:53:21.233
3708E1E8-6E7E-4E8F-B415-3F09CF22CB54 e1 2016-04-14 07:51:43.530
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 08:50:03.187
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e5 2016-04-14 08:45:00.897
220AD0BC-3F20-4F1C-A9B8-81AC08EA679A e3 2016-04-14 08:43:42.253
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e4 2016-04-14 09:00:27.780
F20AD0BC-3F20-4F1C-A9B8-81AC08EA679A e1 2016-04-14 07:51:42.530
Thank you dnoeth !
解决方案
After some discussion in the chat it turned out that it was mainly a display issue:
select id_f,name,lastused
from
(
select ID_F, e.Name, LastUsed
,row_number() -- ranking
over(partition by ID_F -- for each ID_F
order by lastused desc) as rnk -- based on descending dates
from [U_F_E] as ufe
join E as e on ufe.ID_E = e.ID
where ID_U = @myuser
) t
where rnk <= @limit
order by ID_F, lastused desc -- sort the correct way :-)
这篇关于我如何在SAME表上分组，按日期排序并限制返回的行数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
