SQL语句分组排序，多表关联排序

SQL语句分组排序，多表关联排序总结几种常见的方法：

 

案例一：

在查询结果中按人数降序排列,若人数相同,则按课程号升序排列？

分析：单个表内的多个字段排序，一般可以直接用逗号分割实现。

select * from tableA order by col1 desc,col2 asc;
-- 先按col1降序，相同则按col2 升序

 

案例二：

T-SQL查询班级信息：班级人数+班级信息，按人数多少排序？

-- 创建测试数据
create table stu(sid int primary key identity(1,1),sname varchar(50),sage int,scid int foreign key references class(cid))
create table class(cid int primary key identity(1,1),code varchar(50),cname varchar(50),cgrade varchar(50))
insert into stu values('张三',19,1)
insert into stu values('李四',20,2)
insert into stu values('王五',21,2)
insert into class values('c001','1','一年级')
insert into class values('c002','2','一年级')

-- 按人数分组排序查询
select 
  class.cgrade,
  class.code,
  class.cname,
  count(stu.sid) as 人数 
from 
  class JOIN stu
    ON (class.cid = stu.scid)
group by 
  class.cgrade,
  class.code,
  class.cname
order by 
  count(stu.sid) desc

 

案例三：

SQL 查询统计每个用户组发文章数量并按数量排序？

有三个表 文章表 记录文章标题、内容、发布人 发布时间，用户表 记录用户组id 用户名 密码，用户组表 记录id 用户组名称

问题是 现在要查询每个用户组发布文章的数量，并且按着数量排序。不知道怎么搞呀 大神们
还要查询 每个月中发文排行榜....

SELECT COUNT(1) AS 数量, c.用户组名称 
FROM 文件表 a INNER JOIN 用户表 b ON a.发布人 = b.用户名
 INNER JOIN 用户组表 c ON b.用户组id = c.id
 GROUP BY c.id,  c.用户组名称
 ORDER BY 数量

 

案例四：“圈子广场”页面需要展示圈子列表，查询某类型圈子，其中需要按圈子名称搜索，同时按照圈子人数排序。

数据库表结构大致如下：

　　圈子表“CMSocial”，字段包括：圈子主键 CMSocialID，圈子名称 SocialName，圈子状态 SocialState等；

　　圈子类型关系表“CMSocialCategoryRelation”，字段包括 关系主键 CMSocialCategoryRelationID，圈子类型外键 CMSocialCategoryID，圈子外键 CMSocialID；

　　圈子成员表“CMSocialMember”，字段包括：关系主键 CMSocialMemberID，成员外键 MemberID，圈子外键 CMSocialID，圈子成员状态 MemberState等；

USE [Community];

SELECT 
  S.CMSocialID,
  S.SocialName,
  S.SocialDescription,
  S.SocialLogo,
  S.SocialAuthority,
  S.SocialOwner,
  S.Integral,
  S.SocialState,
  S.IsAvailable,
  COUNT(DISTINCT SM.CMSocialMemberID) AS 'MemberNumber' /*圈子成员数*/
FROM CMSocial AS S

INNER JOIN (SELECT DISTINCT CMSocialID FROM CMSocialCategoryRelation WHERE CMSocialCategoryID IN('1','2')) AS SCR ON S.CMSocialID = SCR.CMSocialID /*内连接圈子类型*/

LEFT JOIN (SELECT * FROM CMSocialMember WHERE CMSocialMember.IsDelete<>1 AND CMSocialMember.IsAvailable=1) AS SM ON S.CMSocialID = SM.CMSocialID /*左连接圈子成员*/

WHERE S.IsAvailable=1 AND S.SocialState=0
  AND S.SocialName LIKE N'%创业圈%' /*圈子名称匹配查询*/

GROUP BY 
  S.CMSocialID,
  S.SocialName,
  S.SocialDescription,
  S.SocialLogo,
  S.SocialAuthority,
  S.SocialOwner,
  S.Integral,
  S.SocialState,
  S.IsAvailable
ORDER BY 
  COUNT(DISTINCT SM.CMSocialMemberID) DESC; /*圈子成员数排序*/

 

SQL 2005版本以后支持ROW_NUMBER()获取行号，于是进一步升级，使他支持分页，支持记录总数：

 参考：

/* 最内层输入分页参数之前靠count(*) over ()获得记录总数，然后把这个值作为记录的一列传出来。*/
SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT 
c1,
c2,
count(*) over () tot_cnt
FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

 

这里总结一个SQL分页的万能SQL语句分页壳子

 

USE [Community];

DECLARE @pageindex INT /*页码*/
DECLARE @pagesize INT /*页数据量*/
SELECT @pageindex = 3,@pagesize = 1

SELECT A.*
FROM 
    (
        SELECT ROW_NUMBER() OVER (ORDER BY ASub.ID DESC) AS RowNumber,* FROM 
        (
            …… /*这里写自己的查询语句*/
        ) AS ASub
    ) AS A
WHERE RowNumber BETWEEN ((@PageIndex-1)*(@PageSize) + 1) AND (@PageIndex)*(@PageSize)

 

整合上面的分页SQL语句壳子，实现分页效果：

 

USE [Community];

DECLARE @pageindex INT /*页码*/
DECLARE @pagesize INT /*页数据量*/
SELECT @pageindex = 3,@pagesize = 1

SELECT A.*
FROM 
    (
        SELECT ROW_NUMBER() OVER (ORDER BY ASub.MemberNumber DESC) AS RowNumber,* FROM 
        (
            SELECT 
              S.CMSocialID,
              S.SocialName,
              S.SocialDescription,
              S.SocialLogo,
              S.SocialAuthority,
              S.SocialOwner,
              S.Integral,
              S.SocialState,
              S.IsAvailable,
              COUNT(DISTINCT SM.CMSocialMemberID) AS 'MemberNumber', /*圈子成员数*/
              COUNT(*) OVER() 'RecordNumber' /*结果总数*/
            FROM CMSocial AS S

            INNER JOIN (SELECT DISTINCT CMSocialID FROM CMSocialCategoryRelation WHERE CMSocialCategoryID IN('1','2')) AS SCR ON S.CMSocialID = SCR.CMSocialID /*内连接圈子类型*/

            LEFT JOIN (SELECT * FROM CMSocialMember WHERE CMSocialMember.IsDelete<>1 AND CMSocialMember.IsAvailable=1) AS SM ON S.CMSocialID = SM.CMSocialID /*左连接圈子成员*/

            WHERE S.IsAvailable=1 AND S.SocialState=0
              AND S.SocialName LIKE N'%创业圈%' /*圈子名称匹配查询*/
    
        GROUP BY 
              S.CMSocialID,
              S.SocialName,
              S.SocialDescription,
              S.SocialLogo,
              S.SocialAuthority,
              S.SocialOwner,
              S.Integral,
              S.SocialState,
              S.IsAvailable
        ) AS ASub
    ) AS A
WHERE RowNumber BETWEEN ((@PageIndex-1)*(@PageSize) + 1) AND (@PageIndex)*(@PageSize)