34,576
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE(
A INT,
B INT
)
INSERT @t (
A,
B
)
SELECT 1, 20
UNION ALL SELECT 2, -30
UNION ALL SELECT 3, 40
UNION ALL SELECT 4, 1
UNION ALL SELECT 5, -2
select SA=sum(case when A>0 then A else 0 end),SB=sum(case when B<0 then A else 0 end)
from @t
select sum(case when b>0 then b else 0 end),sum(case when b<0 then b else 0 end) from 表
select sum(case when b>0 then b else 0 end),sum(case when b<0 then b else 0 end) from 表
select sum(case when b>0 then b else 0 end),sum(case when b<0 then b else 0 end) from 表
--> --> (Andy)生成测试数据 2008-12-05
Set Nocount On
declare @1 table([A] int,[B] int)
Insert @1
select 1,20 union all
select 2,-30 union all
select 3,40 union all
select 4,1 union all
select 5,-2
Select Sum(Sign(1+Sign(B))*B),Sum(Sign(1-Sign(B))*B) from @1
/*
----------- -----------
61 -32
*/
DECLARE @t TABLE(
A INT,
B INT
)
INSERT @t (
A,
B
)
SELECT 1, 20
UNION ALL SELECT 2, -30
UNION ALL SELECT 3, 40
UNION ALL SELECT 4, 1
UNION ALL SELECT 5, -2
SELECT SUM(CASE WHEN B>0 THEN B ELSE 0 end) [大于0], SUM(CASE WHEN B<0 THEN B ELSE 0 END) [小于0]
FROM @t
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int)
insert [tb]
select 1,20 union all
select 2,-30 union all
select 3,40 union all
select 4,1 union all
select 5,-2
select * from [tb]
select sum(case when b>0 then b else 0 end),sum(case when b<0 then b else 0 end)
from tb
--测试结果:
/*
61 -32
*/