当前位置: 代码迷 >> Sql Server >> 关于 compute 的有关问题
  详细解决方案

关于 compute 的有关问题

热度:82   发布时间:2016-04-27 19:09:22.0
关于 compute 的问题
关于   compute   的问题:

sql   查询:     select   customername   as   name,product,money   from   customer   compute   sum(money)    

问题是如何把   sum(money)   的值赋予某个变量(或者字段),以便在页面显示?


(或另有可代替的   sql   语句也行,要求是:   查询   customername,product,money,   并对   money   求和,   money   的和要能够调用)




------解决方案--------------------
select customername name,product,money from customer
union all
select '总计 ', ' ',sum(money) from customer

------解决方案--------------------
select a.customername as name ,a.product,a.money,b.money as sum_money
from customer a
left join (select sum(money) money from customer) b
on 1=1
------解决方案--------------------
declare @i as decimal(18,2)

select @i = money from customer compute sum(money)

select @i
------解决方案--------------------

select name,volume from tt union all
select '总计 ',sum(volume) from tt
--赋值变量
declare @i decimal
select @i=sum(volume) from tt
print @i
------解决方案--------------------
declare
@i int
select customername,product,money from customer
union
select @i=sum(money)from customer
------解决方案--------------------
如果想把兩個結果放到一個查詢裡,這麼寫

select customername as name,product,SUM(money) As money from customer Group By customername,product With Rollup Having customername Is Null Or product Is Not Null

------解决方案--------------------
Create Table customer
(customername Varchar(10),
product Varchar(10),
[money] Money)
Insert customer Select 'A ', 'A1 ', 200
Union All Select 'A ', 'A2 ', 300
Union All Select 'B ', 'B1 ', 200
GO
select customername as name,product,SUM(money) As money from customer Group By customername,product With Rollup Having customername Is Null Or product Is Not Null
GO
Drop Table customer
--Result
/*
name product money
A A1 200.0000
A A2 300.0000
B B1 200.0000
NULL NULL 700.0000
*/