如有表
SaleMaster
ID Price Status
1 100 1
2 110 0
3 120 1
4 130 0
--------------------------------
现在要求将Status =0的记录更新为Status =1,关返回他的Price的总和
前面的为
Update SaleMaster Set Status = 1 Where Status =0
那加上后面的功能的话这条语句要怎么写谢谢
SaleMaster
ID Price Status
1 100 1
2 110 0
3 120 1
4 130 0
--------------------------------
现在要求将Status =0的记录更新为Status =1,关返回他的Price的总和
前面的为
Update SaleMaster Set Status = 1 Where Status =0
那加上后面的功能的话这条语句要怎么写谢谢
这样就好办了。
set nocount on
select sum(price) from SaleMaster where Status =0
Update SaleMaster Set Status = 1 Where Status =0
2 Update SaleMaster Set Status = 1 Where Status =0
3 select * from SaleMaster
2 Update SaleMaster Set Status = 1 Where Status =0
3 select * from SaleMaster
--05
DECLARE @t TABLE(TotalPrice INT)Update SaleMaster
Set Status = 1
OUTPUT SUM(inserted.price)
INTO @t(TotalPrice)
Where Status =0
Update SaleMaster Set Status = 1 Where Status =0 SELECT SUM(Price )Price FROM SaleMaster ??
if object_id('[SaleMaster]') is not null drop table [SaleMaster]
go
create table [SaleMaster]([ID] int,[Price] int,[Status] int)
insert [SaleMaster]
select 1,100,1 union all
select 2,110,0 union all
select 3,120,1 union all
select 4,130,0
---更新&查询---
declare @sumPrice int
update SaleMaster
set Status=1,@sumPrice=isnull((select sum(price) from SaleMaster where status=0),0)
where status=0
select @sumPriceselect * from [SaleMaster]
/**
-----------
240(所影响的行数为 1 行)
**//**
ID Price Status
----------- ----------- -----------
1 100 1
2 110 1
3 120 1
4 130 1(所影响的行数为 4 行)
**/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-21 19:01:49
----------------------------------------------------------------
--> 测试数据:[SaleMaster]
if object_id('[SaleMaster]') is not null drop table [SaleMaster]
create table [SaleMaster]([ID] int,[Price] int,[Status] int)
insert [SaleMaster]
select 1,100,1 union all
select 2,110,0 union all
select 3,120,1 union all
select 4,130,0
--------------开始查询--------------------------
select sum(Price )Price from SaleMaster where Status =0
Update SaleMaster set Status = 1 where Status =0
select sum(Price )Price from SaleMaster
----------------结果----------------------------
/*
--更改前的
price
240--更改后的
price
460*/