SQL分组排序

来源：互联网发布：java 并发编程实战编辑：程序博客网时间：2024/05/02 02:01

分组排序在实际应用中常会用到.就拿零售系统中常出现的一个功能,找出每个类别(西裤,牛仔裤,鞋,衬衫...)中畅销的前10个款式.

假设表 SalesSum : Style,Category,SalesQty

在SQL SERVER 2000下:

SELECT *
FROM SalesSum AS S
WHERE Style IN(SELECT TOP 10 Style
            FROM SalesSum
            WHERE S.Category=Category
            ORDER BY SalesQty Desc)
Order By Category,SalesQty Desc

方法1:(利用相关子查询找出销售件数TOP 10)

SELECT *
FROM SalesSum As S
WHERE 10>(SELECT COUNT(*)
          FROM SalesSum
          WHERE Category=S.Category AND SalesQty>S.SalesQty)
ORDER BY Category,SalesQty DESC

方法2:(用count嵌套模拟,记数器COUNT返回同类别中销售件数比自己大的汇总记录数,如不存在相同销售数的情况下,可以理解为所在排位.)

再介绍2种 SQL 2005下的做法,比较方便

SELECT *
FROM
(
   SELECT rn=ROW_NUMBER() OVER(PARTITION BY Category ORDER BY SalesQty DESC),*
   FROM SalesSum) AS S
WHERE rn<=10
ORDER BY Category,SalesQty Desc

方法3:(利用SQL05的新特性,ROW_NUMBER()用于分组排列)

SELECT DISTINCT b.*
FROM SalesSum AS S
CROSS APPLY
(
   SELECT TOP (10) *
   FROM SalesSum
   WHERE Category=S.Category
   ORDER BY SalesQty DESC
) AS b
ORDER BY Category,SalesQty Desc

方法4:(利用APPLY表运算符)

APPLY涉及下面两个步骤的子集(取决于APPLY的类型):

1.A1:把右表表达式应用到左表输入的行.

2.A2:添加外部行.

APPLY运算符把右表表达式应用到左输入中的每一行.可以认为它类似于联接,只不过它的右表表达式可以引用左输入中的列.就好像在联接中的两个输入之间没有优先级.而使用APPLY时,就像是先计算左输入,然后为左输入中的每一行计算一次右输入.

CROSS APPLY和OUTER APPLY总是包含步骤A1,只有OUTER APPLY包含步骤A2.如果为CROSS APPLY的外部(左)行应用内部(右)表表达式时返回空集,则不返回该行.而OUTER APPLY将返回该行,并且该行的内表表达式的属性为NULL.