sql 统计排名

webcode

浏览: 5956402 次
性别:
来自: 上海

最近访客更多访客>>

ap950

u012363178

wangyy

ybfjavaeye

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (7533)

社区版块

存档分类

2013-02 ( 78)
2013-01 ( 83)
2012-12 ( 121)
更多存档...

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

--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