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)