帖子沉了，重新开个，求SQL语句（group by order by count）解决方法

-- table dbo.Usersif OBJECT_ID('Users') is not nulldrop table UsersgoCREATE TABLE [dbo].[Users](    [Id] [uniqueidentifier] NOT NULL,    [Name] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Users] 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]GOALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_Id]  DEFAULT (newid()) FOR [Id]GOinsert into dbo.Users(Name)select 'test1' union all select 'test2' union all select 'test3'-- table dbo.Users end-- table dbo.Lettersif OBJECT_ID('Letters') is not nulldrop table LettersgoCREATE TABLE [dbo].[Letters](    [Id] [uniqueidentifier] NOT NULL,    [SenderId] [uniqueidentifier] NOT NULL,    [ReceiverId] [uniqueidentifier] NOT NULL,    [Content] [nvarchar](50) NOT NULL,    [CreatedAt] [datetime] NOT NULL, CONSTRAINT [PK_Letters] 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]GOALTER TABLE [dbo].[Letters] ADD  CONSTRAINT [DF_Letters_Id]  DEFAULT (newid()) FOR [Id]GOALTER TABLE [dbo].[Letters] ADD  CONSTRAINT [DF_Letters_CreatedAt]  DEFAULT (getdate()) FOR [CreatedAt]GOdeclare @test1 uniqueidentifierdeclare @test2 uniqueidentifierdeclare @test3 uniqueidentifierselect @test1 = id from dbo.users where name = 'test1'select @test2 = id from dbo.users where name = 'test2'select @test3 = id from dbo.users where name = 'test3'insert into dbo.Letters(senderId, receiverId, [content])select @test1, @test2, 'test1 to test2' union allselect @test2, @test1, 'test2 to test1' union allselect @test1, @test3, 'test1 to test3' union allselect @test1, @test3, 'test1 to test3' union allselect @test2, @test3, 'test2 to test3' union allselect @test3, @test1, 'test3 to test1' union allselect @test2, @test1, 'test2 to test1'

-- table dbo.Usersif OBJECT_ID('Users') is not nulldrop table UsersgoCREATE TABLE [dbo].[Users](    [Id] [uniqueidentifier] NOT NULL,    [Name] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Users] 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]GOALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_Id]  DEFAULT (newid()) FOR [Id]GOinsert into dbo.Users(Name)select 'test1' union all select 'test2' union all select 'test3'-- table dbo.Users end-- table dbo.Lettersif OBJECT_ID('Letters') is not nulldrop table LettersgoCREATE TABLE [dbo].[Letters](    [Id] [uniqueidentifier] NOT NULL,    [SenderId] [uniqueidentifier] NOT NULL,    [ReceiverId] [uniqueidentifier] NOT NULL,    [Content] [nvarchar](50) NOT NULL,    [CreatedAt] [datetime] NOT NULL, CONSTRAINT [PK_Letters] 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]GOALTER TABLE [dbo].[Letters] ADD  CONSTRAINT [DF_Letters_Id]  DEFAULT (newid()) FOR [Id]GOALTER TABLE [dbo].[Letters] ADD  CONSTRAINT [DF_Letters_CreatedAt]  DEFAULT (getdate()) FOR [CreatedAt]GOdeclare @test1 uniqueidentifierdeclare @test2 uniqueidentifierdeclare @test3 uniqueidentifierselect @test1 = id from dbo.users where name = 'test1'select @test2 = id from dbo.users where name = 'test2'select @test3 = id from dbo.users where name = 'test3'insert into dbo.Letters(senderId, receiverId, [content])select @test1, @test2, 'test1 to test2' union allselect @test2, @test1, 'test2 to test1' union allselect @test1, @test3, 'test1 to test3' union allselect @test1, @test3, 'test1 to test3' union allselect @test2, @test3, 'test2 to test3' union allselect @test3, @test1, 'test3 to test1' union allselect @test2, @test1, 'test2 to test1'select     Id,senders,receiver,Content,CreatedAt,Totalsfrom    (select     ROW_NUMBER()over(order by getdate()) as [rank],    Id,senders,receiver,Content,CreatedAt,Totalsfrom     (select     a.Id,b.name as senders,c.Name as receiver,    a.Content,a.CreatedAt,    --统计每个人有多少个相关的letters    COUNT(1)over(partition by b.name) as Totals,    --按时间降序给每个人的没风letters排序    ROW_NUMBER()over(partition by b.name order by CreatedAt desc) as Rowfrom     Letters ainner join     Users bon     a.SenderId=b.Idinner join    Users con     a.ReceiverId=c.Id    )twhere    Row=1    )mwhere    [rank] between 起始位置 And 结束位置 --实现分页