sql 统计排名

摘要: 在博客列表中,统计发帖数量从大到小的排行, 输出前三名

 

--select top 3 mem_id,count(mem_id)as 'sta' from tb_blogjournal group by mem_id order by sta desc

select blogjournal.mem_id,Mem_LoginName,mem_pic from (select top 3 mem_id from tb_blogjournal group by mem_id order by count(mem_id) desc)as blogjournal inner join tb_member on blogjournal.mem_id=tb_member.mem_id
 数据库表结构脚本

 

CREATE TABLE [tb_BlogJournal] (
 [BJ_Id] [int] IDENTITY (1, 1) NOT NULL ,
 [Mem_Id] [int] NULL ,
 [BJC_Number] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
 [BJ_Title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [BJ_Author] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [BJ_Content] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 [BJ_Datetime] [datetime] NULL ,
 [BJ_Clicks] [int] NULL CONSTRAINT [DF_tb_BlogJournal_BJ_Clicks] DEFAULT (0),
 [BJ_State] [tinyint] NULL ,
 [BJ_Pic] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [BJ_Intro] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 CONSTRAINT [PK_tb_BlogJournal] PRIMARY KEY  CLUSTERED
 (
  [BJ_Id]
 )  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [tb_Member] (
 [Mem_Id] [int] IDENTITY (1, 1) NOT NULL ,
 [Mem_LoginName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_PassWord] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Email] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_CardNumber] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Sex] [tinyint] NULL ,
 [Mem_Birthday] [datetime] NULL ,
 [Mem_Education] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Provide] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_City] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_HomeAddress] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Identity] [char] (18) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Telephone] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Mobilephone] [char] (11) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_MobilephoneSmall] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Unit] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Profession] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Code] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Introduction] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_IsCook] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Intrest] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_Use] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_State] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_AddTime] [datetime] NULL ,
 [Mem_Nickname] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [Mem_blogStar] [int] NULL CONSTRAINT [DF_tb_Member_Mem_blogStar] DEFAULT (0),
 [Mem_Pic] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 CONSTRAINT [PK_tb_Member] PRIMARY KEY  CLUSTERED
 (
  [Mem_Id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO