你想要这个吗?---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-01-09 22:09:16 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) -- Jun 17 2011 00:57:23 -- Copyright (c) Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([A] int,[B] int) insert [huang] select 2,3 union all select 5,7 union all select 3,4 --------------开始查询--------------------------select SUM(a*b) from [huang] ----------------结果---------------------------- /* ----------- 53(1 行受影响)*/
一时太快,算错了。 没有group by 会报错
因为该列没有包含在聚合函数或 GROUP BY 子句中
楼主,如果是全部记录的话,不用group也可以做聚合create table #a(a int,b int)insert into #a select 2,3 union select 4,5select * from #a/*输出结果 2,3 4,5 */select sum(a*b) from #a--输出结果为26
--创建数据 if object_id('Test') is not null drop table Test create table Test(a int,b int) go insert into Test select 2,3 union all select 5,7 union all select 3,4 --查询 ;with T as( select ROW_NUMBER() over(order by getdate()) id,* from Test ) select t1.a,t1.b,isnull(t1.a,0)*isnull(t1.b,0)+isnull(t2.a,0)*isnull(t2.b,0)as'相邻行的AB积之和' from T t1 left join T t2 on t1.id=t2.id-1 order by t1.id--结果 /*(3 行受影响) a b 相邻行的AB积之和 ----------- ----------- ----------- 2 3 41 5 7 47 3 4 12(3 行受影响) */
你比我好,我数学是语文老师教的 不好意思,因为这是老师提出的作业,不知怎样导出来,求原谅。我以为用 select sum(A*B) from table group by A,B 是可以得到这两列多行的乘积和的,但还是不行,表不止两列。
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-09 22:09:16
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([A] int,[B] int)
insert [huang]
select 2,3 union all
select 5,7 union all
select 3,4
--------------开始查询--------------------------select SUM(a*b)
from [huang]
----------------结果----------------------------
/* -----------
53(1 行受影响)*/
没有group by 会报错
select 2,3
union
select 4,5select * from #a/*输出结果
2,3
4,5
*/select sum(a*b) from #a--输出结果为26
--创建数据
if object_id('Test') is not null drop table Test
create table Test(a int,b int)
go
insert into Test
select 2,3 union all
select 5,7 union all
select 3,4 --查询
;with T as(
select ROW_NUMBER() over(order by getdate()) id,* from Test
)
select t1.a,t1.b,isnull(t1.a,0)*isnull(t1.b,0)+isnull(t2.a,0)*isnull(t2.b,0)as'相邻行的AB积之和' from T t1 left join
T t2 on t1.id=t2.id-1 order by t1.id--结果
/*(3 行受影响)
a b 相邻行的AB积之和
----------- ----------- -----------
2 3 41
5 7 47
3 4 12(3 行受影响)
*/
不好意思,因为这是老师提出的作业,不知怎样导出来,求原谅。我以为用 select sum(A*B) from table group by A,B 是可以得到这两列多行的乘积和的,但还是不行,表不止两列。