SQL Server求解最近多少销售记录的销售额占比总销售额的指定比例

IF OBJECT_ID(N‘dbo.SalesData‘, N‘U‘) IS NOT NULL 2 BEGIN 3 DROP TABLE dbo.SalesData; 4 END 5 GO 6 7 CREATE TABLE dbo.SalesData 8 ( 　　 9 Product_Name CHAR(32) NOT NULL, --商品名称 　　 10 Sale_Amount FLOAT NOT NULL　　 --销售金额 11 ); 12 GO 13 14 INSERT INTO dbo.SalesData (Product_Name, Sale_Amount) 15 SELECT ‘ProductNameA‘, 13000 UNION ALL 16 SELECT ‘ProductNameA‘, 12000 UNION ALL 17 SELECT ‘ProductNameA‘, 9000 UNION ALL 18 SELECT ‘ProductNameB‘, 167000 UNION ALL 19 SELECT ‘ProductNameB‘, 137000 UNION ALL 20 SELECT ‘ProductNameB‘, 107000 UNION ALL 21 SELECT ‘ProductNameC‘, 78000 UNION ALL 22 SELECT ‘ProductNameC‘, 12000; 23 GO

执行以下的T-SQL代码：

1 SELECT Product_Name, Sale_Amount
2 FROM dbo.SalesData;
3 GO

得到的结果如下：

  注意：以上测试数据也是来自潇潇隐者的博文，我再次基础上做了调整。   基于SQL Server 2005和以上的版本的解决方案的T-SQL代码如下：

 1 -- SQL Server 2005 和以上版本 使用子查询的解决方案
 2 SELECT *
 3 FROM (
 4     SELECT T.Product_Name, T.Sale_Amount, T.rownum, T.Sale_TotalAmount, T.Sale_AccumulateAmount
 5         ,100.0 * T.Sale_AccumulateAmount / T.Sale_TotalAmount AS Sale_AccumulatePercent
 6     FROM (
 7         SELECT T.Product_Name, T.Sale_Amount, ROW_NUMBER() OVER (ORDER BY T.Sale_Amount DESC) AS rownum, T2.Sale_TotalAmount
 8             ,(SELECT SUM(Sale_Amount) FROM dbo.SalesData WHERE Sale_Amount >= T.Sale_Amount) AS Sale_AccumulateAmount
 9         FROM dbo.SalesData AS T
10         CROSS APPLY (SELECT SUM(Sale_Amount) AS Sale_TotalAmount FROM dbo.SalesData) AS T2
11     ) AS T
12 ) AS T
13 WHERE T.Sale_AccumulatePercent <= 80;
14 GO
15  

执行后的查询结果如下：

  基于SQL Server 2012和以上的版本的解决方案的T-SQL代码如下：

 1 -- SQL Server 2012 和以上 使用增强的窗口函数的解决方案
 2 SELECT T.Product_Name, T.Sale_Amount, T.rownum, T.Sale_TotalAmount, T.Sale_AccumulateAmount, T.Sale_AccumulatePercent
 3 FROM (
 4     SELECT T.Product_Name, T.Sale_Amount, T.rownum, T.Sale_TotalAmount, T.Sale_AccumulateAmount
 5     ,100.0 * T.Sale_AccumulateAmount / T.Sale_TotalAmount AS Sale_AccumulatePercent /*累计销售额在总销售中的百分比*/
 6     FROM (
 7         SELECT Product_Name, Sale_Amount, ROW_NUMBER() OVER (ORDER BY Sale_Amount DESC) AS rownum /*行号行数，具有不确定性，窗口排序字句列不唯一*/
 8         ,SUM(Sale_Amount) OVER () AS Sale_TotalAmount /*总销售额*/
 9         ,SUM(Sale_Amount) OVER (ORDER BY Sale_Amount DESC ROWS UNBOUNDED PRECEDING) AS Sale_AccumulateAmount /* 累计销售额，使用了SQL Server 2012窗口函数新增的窗口框架字句，也等同于 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW*/
10         FROM dbo.SalesData
11     ) AS T
12 ) AS T
13 WHERE T.Sale_AccumulatePercent <= 90;
14 GO

执行后的查询结果如下：

  博友有其他的解决方案，也请不吝赐教，万分感谢。

SQL Server求解最近多少销售记录的销售额占比总销售额的指定比例

标签：