SQL2008避免出错（聚合函数+Over用法）-白红宇

SQL2008避免出错（聚合函数+Over用法）

阅读量：6263 次

发布时间：2019-06-22

本文共 1822 字，大约阅读时间需要 6 分钟。

--返回出错with CTEOrders as	(select cast(1 as int) as OrderID, cast('3/1/2012' as date) as OrderDate, cast(10.00 as money) as OrderAmt, 'Joe' as CustomerName	union select 2, '3/1/2012', 11.00, 'Sam'	union select 3, '3/2/2012', 10.00, 'Beth'	union select 4, '3/2/2012', 15.00, 'Joe'	union select 5, '3/2/2012', 17.00, 'Sam'	union select 6, '3/3/2012', 12.00, 'Joe'	union select 7, '3/4/2012', 10.00, 'Beth'	union select 8, '3/4/2012', 18.00, 'Sam'	union select 9, '3/4/2012', 12.00, 'Joe'	union select 10, '3/4/2012', 11.00, 'Beth'	union select 11, '3/5/2012', 14.00, 'Sam'	union select 12, '3/6/2012', 17.00, 'Beth'	union select 13, '3/6/2012', 19.00, 'Joe'	union select 14, '3/7/2012', 13.00, 'Beth'	union select 15, '3/7/2012', 16.00, 'Sam'	)select sum(OrderAmt), sum(OrderAmt) over()   from CTEOrders   group by CustomerName /* 消息 155，级别 15，状态 1，第 24 行'CTEOrders' 不是可以识别的 GROUP BY 选项。*/  --这样用避免出错;with CTEOrders as	(select cast(1 as int) as OrderID, cast('3/1/2012' as date) as OrderDate, cast(10.00 as money) as OrderAmt, 'Joe' as CustomerName	union select 2, '3/1/2012', 11.00, 'Sam'	union select 3, '3/2/2012', 10.00, 'Beth'	union select 4, '3/2/2012', 15.00, 'Joe'	union select 5, '3/2/2012', 17.00, 'Sam'	union select 6, '3/3/2012', 12.00, 'Joe'	union select 7, '3/4/2012', 10.00, 'Beth'	union select 8, '3/4/2012', 18.00, 'Sam'	union select 9, '3/4/2012', 12.00, 'Joe'	union select 10, '3/4/2012', 11.00, 'Beth'	union select 11, '3/5/2012', 14.00, 'Sam'	union select 12, '3/6/2012', 17.00, 'Beth'	union select 13, '3/6/2012', 19.00, 'Joe'	union select 14, '3/7/2012', 13.00, 'Beth'	union select 15, '3/7/2012', 16.00, 'Sam'	)select sum(OrderAmt), sum(sum(OrderAmt)) over()   from CTEOrders   group by CustomerName;/*61.0000	205.000068.0000	205.000076.0000	205.0000*/

转载于:https://www.cnblogs.com/wuxi88/p/5466743.html

你可能感兴趣的文章

putty提供的两个文件传输工具PSCP、PSFTP详细介绍

查看>>