如表所示:
tb1
Account sum1
510101 20
510202 -10
520103 100
540105 -8
55050101 10
要求:定义一个变量@sum,初始值为0
若Account以51或者52或者53开头的 @sum+sum1
若Account以54或者55或者56或者57或者58开头的 @sum-sum1
最后输出@sum
tb1
Account sum1
510101 20
510202 -10
520103 100
540105 -8
55050101 10
要求:定义一个变量@sum,初始值为0
若Account以51或者52或者53开头的 @sum+sum1
若Account以54或者55或者56或者57或者58开头的 @sum-sum1
最后输出@sum
select Account,(case when convert(int,left(Account,2))>=51 and convert(int,left(Account,2))<=53 then @sum+sum1 else
(case when convert(int,left(Account,2))>=54 and convert(int,left(Account,2))<=58 then @sum-sum1 end)end) as sum1 from tb1
when convert(int,left(Account,2))>=51 and convert(int,left(Account,2))<=58
select Account,(case when convert(int,left(Account,2))>=51 and convert(int,left(Account,2)) <=53 then @sum+sum1 else @sum-sum1 end) as sum1 from tb1
when convert(int,left(Account,2))>=51 and convert(int,left(Account,2)) <=58
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([Account] int,[sum1] int)
insert [tb1]
select 510101,20 union all
select 510202,-10 union all
select 520103,100 union all
select 540105,-8 union all
select 55050101,10
---查询---
declare @sum int;
set @sum=0;
select
@sum=sum(case when left(Account,2) in('51','52','53') then sum1 when left(Account,2) in('54','55','56','57','58') then -1*sum1 end)
from [tb1]
select @sum as [@sum]
---结果---
@sum
-----------
108
这样?
declare @tb1 table(account varchar(50),sum1 money)
insert into @tb1
select '510101',20 union all
select '510102',-10 union all
select '520103',100 union all
select '540105',-8 union all
select '55050101',20
select sum(case when left(account,2) in(54,55,56,57,58) then sum1*-1 when left(account,2) in(51,52,53) then sum1 end)
from @tb1
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([Account] int,[sum1] int)
insert [tb1]
select 510101,20 union all
select 510202,-10 union all
select 520103,100 union all
select 540105,-8 union all
select 55050101,10
---查询---
declare @sum int;
set @sum=0;
select
@sum=sum(case when left(Account,2) in('51','52','53') then sum1 when left(Account,2) in('54','55','56','57','58') then -1*sum1 end)
from [tb1]
where case when left(Account,2) in('51','52','53','54','55','56','57','58')
select @sum as [@sum]
(
account nvarchar(50),
sum1 int
)
insert into #T
select '510101', 20 union all
select '510202', -10 union all
select '520103', 100 union all
select '540105', -8 union all
select '55050101', 10
declare @sum int
set @sum=0
select @sum=sum(case when SUBSTRING(account,1,2) in ('51','52','53') then sum1
when SUBSTRING(account,1,2) in ('54','55','56','57','58') then 0-sum1 end) from #T
select @sum