帖子沉了，重新开个，求SQL语句（group by order by count）解决方法-MSSQL教程-爱易网页

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

日期：2014-05-18　浏览次数：20740 次

帖子沉了，重新开个，求SQL语句（group by order by count）
先上测试数据

SQL code


-- table dbo.Users
if OBJECT_ID('Users') is not null
drop table Users
go
CREATE 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]

GO

ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_Id]  DEFAULT (newid()) FOR [Id]
GO

insert into dbo.Users(Name)
select 'test1' union all 
select 'test2' union all 
select 'test3'

-- table dbo.Users end

-- table dbo.Letters
if OBJECT_ID('Letters') is not null
drop table Letters
go
CREATE 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]

GO

ALTER TABLE [dbo].[Letters] ADD  CONSTRAINT [DF_Letters_Id]  DEFAULT (newid()) FOR [Id]
GO

ALTER TABLE [dbo].[Letters] ADD  CONSTRAINT [DF_Letters_CreatedAt]  DEFAULT (getdate()) FOR [CreatedAt]
GO

declare @test1 uniqueidentifier
declare @test2 uniqueidentifier
declare @test3 uniqueidentifier

select @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 all
select @test2, @test1, 'test2 to test1' union all
select @test1, @test3, 'test1 to test3' union all
select @test1, @test3, 'test1 to test3' union all
select @test2, @test3, 'test2 to test3' union all
select @test3, @test1, 'test3 to test1' union all
select @test2, @test1, 'test2 to test1'

Letters(id, senderId, recieverId, content, createdAt)

Users(id, name)

获取和我有关的所有用户的最近的一条消息，显示条数，然后在一个页面分页显示出来。

select name, content, total

分页显示

点击这里进入原贴

------解决方案--------------------

SQL code


-- table dbo.Users
if OBJECT_ID('Users') is not null
drop table Users
go
CREATE 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]

GO

ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_Id]  DEFAULT (newid()) FOR [Id]
GO

insert into dbo.Users(Name)
select 'test1' union all 
select 'test2' union all 
select 'test3'

-- table dbo.Users end

-- table dbo.Letters
if OBJECT_ID('Letters') is not null
drop table Letters
go
CREATE 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]

GO

ALTER TABLE [dbo].[Letters] ADD  CONSTRAINT [DF_Letters_Id]  DEFAULT (newid()) FOR [Id]
GO

ALTER TABLE [dbo].[Letters] ADD  CONSTRAINT [DF_Letters_CreatedAt]  DEFAULT (getdate()) FOR [CreatedAt]
GO

declare @test1 uniqueidentifier
declare @test2 uniqueidentifier
declare @test3 uniqueidentifier

select @test1 = id from dbo.users

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

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

相关资料更多>

推荐阅读更多>