• 主页
• 基础类
• 应用实例
• 新技术前沿

# compute by 出现"计算依据列表与排序依据列表不匹配"？？

cash8486 2008-01-04 10:03:38
Select TOP 30 Percent
MV.VendorName AS 厂商名称,
MB.BrandName AS 厂商经营品牌,
sum(TR.SaleAmt+TR.DiscAmt) As 销售额
From TRsum AS TR,
mVendor AS MV,
mBrand AS MB
Where TR.BrandCd=MB.BrandCd
And
MB.VendorCd=MV.Vendorcd
Group By MV.VendorName,MB.BrandName
order By 销售额 DESC
Compute sum(sum(TR.SaleAmt+TR.DiscAmt))By MV.VendorName

...全文
188 点赞 收藏 2

2 条回复

dawugui 2008-01-04
``````
4、CUBE
select item,color,sum(qty) qtysum from tb group by item,color with cube
/*
item       color      qtysum
---------- ---------- -----------
Chair      Blue       100
Chair      Red        400
Chair      NULL       500
Table      Blue       300
Table      Red        100
Table      NULL       400
NULL       NULL       900
NULL       Blue       400
NULL       Red        500
*/
/*

Chair                (null)               500

Table                (null)               400

(null)               (null)               900

(null)               Blue                 400
(null)               Red                  500

*/

5、使用 GROUPING 区分空值
CUBE 操作所生成的空值带来一个问题：如何区分 CUBE 操作所生成的 NULL 值和从实际数据中返回的 NULL 值？

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(qty) AS QtySum
FROM tb
GROUP BY Item, Color WITH CUBE
/*
Item       Color      QtySum
---------- ---------- -----------
Chair      Blue       100
Chair      Red        400
Chair      ALL        500
Table      Blue       300
Table      Red        100
Table      ALL        400
ALL        ALL        900
ALL        Blue       400
ALL        Red        500
*/

6、COMPUTE
select item,color,qty from tb order by item,color compute sum(qty)
/*
item       color      qty
---------- ---------- -----------
Chair      Blue       100
Chair      Red        200
Chair      Red        200
Table      Blue       100
Table      Blue       200
Table      Red        100

sum
===========
900
*/
--上面的结果也可以用union(all)来实现
select item,color,qty from tb
union all
select item='',color='',sum(qty) from tb
/*
item       color      qty
---------- ---------- -----------
Table      Blue       100
Table      Blue       200
Table      Red        100
Chair      Blue       100
Chair      Red        200
Chair      Red        200
900
*/

7、COMPUTE BY
select item,color,qty from tb order by item,color compute sum(qty) by item,color
/*
item       color      qty
---------- ---------- -----------
Chair      Blue       100
sum
===========
100
item       color      qty
---------- ---------- -----------
Chair      Red        200
Chair      Red        200
sum
===========
400
item       color      qty
---------- ---------- -----------
Table      Blue       100
Table      Blue       200
sum
===========
300
item       color      qty
---------- ---------- -----------
Table      Red        100
sum
===========
100
*/
/*

*/

8、同时使用avg,sum
select item,color,qty from tb order by item,color compute avg(qty),sum(qty) by item,color
/*
item       color      qty
---------- ---------- -----------
Chair      Blue       100
avg
===========
100
sum
===========
100
item       color      qty
---------- ---------- -----------
Chair      Red        200
Chair      Red        200
avg
===========
200
sum
===========
400
item       color      qty
---------- ---------- -----------
Table      Blue       100
Table      Blue       200
avg
===========
150
sum
===========
300
item       color      qty
---------- ---------- -----------
Table      Red        100
avg
===========
100
sum
===========
100
*/

``````

dawugui 2008-01-04

``````有关ROLLUP,CUBE,GROUPING,COMPUTE,COMPUTE BY的用法
(爱新觉罗.毓华 2007-10-23于浙江杭州)

ROLLUP
ROLLUP运算符生成的结果集显示了所选列中值的某一层次结构的聚合。返回单个结果集。
在生成包含小计和合计的报表时，ROLLUP运算符很有用。
ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。

CUBE
CUBE运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展，事实数据即记录个别事件的数据。
扩展建立在用户打算分析的列上。这些列被称为维。多维数据集是一个结果集，其中包含了各维度的所有可能组合的交叉表格。

GROUPING
是一个聚合函数,它产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，附加的列输出值为1，
当所添加的行不是由CUBE或ROLLUP产生时，附加列值为0。
仅在与包含CUBE或ROLLUP运算符的GROUP BY子句相联系的选择列表中才允许分组。
注释：分组用于区分由CUBE和ROLLUP返回的空值和标准的空值。作为CUBE或ROLLUP操作结果返回的NULL是NULL的特殊应用。
它在结果集内作为列的占位符，意思是"全体"。

COMPUTE
COMPUTE子句生成合计作为附加的汇总列出现在结果集的最后。当与BY一起使用时，COMPUTE子句在结果集内生成控制中断和分类汇总。
可在同一查询内指定COMPUTE BY和COMPUTE。
COMPUTE子句需要下列信息：
1、可选的 BY 关键字，该关键字可按对一列计算指定的行聚合。
2、行聚合函数名称；例如：AVG    --数字表达式中所有值的平均值，
COUNT  --选定的行数，
MAX    --表达式中的最(大)高值，
MIN    --表达式中的最(小)低值，
STDEV  --表达式中所有值的统计标准偏差，
STDEVP --表达式中所有值的填充统计标准偏差，
VAR    --表达式中所有值的统计方差，
VARP   --表达式中所有值的填充统计方差，
SUM    --数字表达式中所有值的和。
3、要对其执行行聚合函数的列。
说明：在带 COMPUTE 子句的 SELECT 语句中，选择列表中的列顺序替代 COMPUTE 子句中的聚合函数顺序。
ODBC 和 DB-library 程序员必须知道这种顺序要求，以将聚合函数结果放在正确的位置。
由于包含 COMPUTE 的语句生成表并且这些表的汇总结果不存储在数据库中，因此在 SELECT INTO 语句中不能使用 COMPUTE。
因而，任何由 COMPUTE 生成的计算结果不出现在用 SELECT INTO 语句创建的新表内。
当 SELECT 语句是 DECLARE CURSOR 语句的一部分时，不能使用 COMPUTE 子句。

COMPUTE BY
COMPUTE BY子句使您得以用同一SELECT语句既查看明细行，又查看汇总行。可以计算子组的汇总值，也可以计算整个结果集的汇总值。
如果使用 COMPUTE BY，则必须也使用 ORDER BY 子句。表达式必须与在 QRDER BY 后列出的子句相同或是其子集，
并且必须按相同的序列。例如，如果 ORDER BY 子句是：
ORDER BY a, b, c
则 COMPUTE子句可以是下面的任意一个（或全部）：
COMPUTE BY a, b, c
COMPUTE BY a, b
COMPUTE BY a

COMPUTE生成的结果集
COMPUTE所生成的汇总值在查询结果中显示为分离的结果集。包括COMPUTE子句的查询的结果类似于控制中断报表，
即汇总值由指定的组(或称中断)控制的报表。可以为各组生成汇总值，也可以对同一组计算多个聚合函数。
当COMPUTE 带有可选的 BY 子句时，符合 SELECT 条件的每个组都有两个结果集：
每个组的第一个结果集是明细行集，其中包含该组的选择列表信息。
每个组的第二个结果集有一行，其中包含该组的 COMPUTE 子句中所指定的聚合函数的小计。
当COMPUTE 不带可选的 BY 子句时，SELECT 语句有两个结果集：
每个组的第一个结果集是包含选择列表信息的所有明细行。
第二个结果集有一行，其中包含 COMPUTE 子句中所指定的聚合函数的合计。

说明：在COMPUTE或COMPUTE BY子句中，不能包含ntext、text或image数据类型。

COMPUTE和GROUP BY之间的区别汇总如下：
1、GROUP BY生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。
选择列表只能包含分组依据列和聚合函数。
2、COMPUTE生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。
另一类结果集包含组的子聚合，或 SELECT 语句的总聚合。
选择列表可包含除分组依据列或聚合函数之外的其它表达式。聚合函数在 COMPUTE 子句中指定，而不是在选择列表中。

---------------------------------------------------------------------------
--原始数据
Item       Color      Qty
---------- ---------- -----------
Table      Blue       100
Table      Blue       200
Table      Red        100
Chair      Blue       100
Chair      Red        200
Chair      Red        200
--创建表并插入数据
create table tb(Item varchar(10),Color varchar(10),Qty int)
insert into tb values('Table','Blue',100)
insert into tb values('Table','Blue',200)
insert into tb values('Table','Red ',100)
insert into tb values('Chair','Blue',100)
insert into tb values('Chair','Red ',200)
insert into tb values('Chair','Red ',200)
go
----------------------------------------------------------------------------
1、ROLLUP
select item,color,sum(qty) qtysum from tb group by item,color with rollup
/*
item       color      qtysum
---------- ---------- -----------
Chair      Blue       100
Chair      Red        400
Chair      NULL       500
Table      Blue       300
Table      Red        100
Table      NULL       400
NULL       NULL       900
*/

2、GROUPING
select item,color,Sum(qty) qtysum,grouping(item) grouping_item,grouping(color) grouping_color
from tb group by item,color with rollup
/*
item       color      qtysum      grouping_item grouping_color
---------- ---------- ----------- ------------- --------------
Chair      Blue       100         0             0
Chair      Red        400         0             0
Chair      NULL       500         0             1
Table      Blue       300         0             0
Table      Red        100         0             0
Table      NULL       400         0             1
NULL       NULL       900         1             1
*/

3、利用grouping将上面的空(null)转换为合计，小计
SELECT CASE WHEN (GROUPING(Item) = 1) THEN '合计' else item END AS Item,
CASE WHEN (GROUPING(Item) = 1) and (GROUPING(Color) = 1) THEN '合计'
WHEN (GROUPING(Item) = 0) and (GROUPING(Color) = 1) THEN '小计'
ELSE color END AS Color,
SUM(Qty) AS QtySum
FROM tb
GROUP BY Item, Color
WITH ROLLUP
/*
Item       Color      QtySum
---------- ---------- -----------
Chair      Blue       100
Chair      Red        400
Chair      小计       500
Table      Blue       300
Table      Red        100
Table      小计       400

*/``````

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区