--> --> (Andy)生成测试数据 2008-10-27 Set Nocount On declare @1 table([名称] nvarchar(1),[应用量] int,[已经领量] int,[库存量] int) Insert @1 select N'A',1000,0,2280 union all select N'B',200,0,2125 union all select N'C',100,null,10 union all select N'D',200,null,100 union all select N'E',35,null,135 union all select N'F',100,40,445 union all select N'G',100,30,17
Select * from @1 Where [库存量]>Coalesce([应用量]-[已经领量],[应用量],-[已经领量],0)/* 名称 应用量 已经领量 库存量 ---- ----------- ----------- ----------- A 1000 0 2280 B 200 0 2125 E 35 NULL 135 F 100 40 445 */
decode('应用量','NULL','0','应用量')
Select * from @1 Where [库存量]>Coalesce([应用量]-[已经领量],[应用量],-[已经领量],0)
where 库存量>应用量-isnull(已领量,0)
INSERT @T SELECT 'A' ,1000, 0, 2280
INSERT @T SELECT 'B' ,200, 0 ,2125
INSERT @T SELECT 'C' ,100, NULL, 10
INSERT @T SELECT 'D', 200, NULL, 100
INSERT @T SELECT 'E' ,35, NULL, 135
INSERT @T SELECT 'F' ,100, 40, 445
INSERT @T SELECT 'G', 100, 30, 17
SELECT * FROM @T WHERE num3>=num1-ISNULL(num2,0)
/*name num1 num2 num3
---------- ----------- ----------- -----------
A 1000 0 2280
B 200 0 2125
E 35 NULL 135
F 100 40 445
*/
Set Nocount On
declare @1 table([名称] nvarchar(1),[应用量] int,[已经领量] int,[库存量] int)
Insert @1
select N'A',1000,0,2280 union all
select N'B',200,0,2125 union all
select N'C',100,null,10 union all
select N'D',200,null,100 union all
select N'E',35,null,135 union all
select N'F',100,40,445 union all
select N'G',100,30,17
Select * from @1 Where [库存量]>Coalesce([应用量]-[已经领量],[应用量],-[已经领量],0)/*
名称 应用量 已经领量 库存量
---- ----------- ----------- -----------
A 1000 0 2280
B 200 0 2125
E 35 NULL 135
F 100 40 445
*/
DECLARE @T TABLE(name VARCHAR(10), num1 INT, num2 INT, num3 INT)
INSERT @T SELECT 'A' ,1000, 0, 2280
INSERT @T SELECT 'B' ,200, 0 ,2125
INSERT @T SELECT 'C' ,100, NULL, 10
INSERT @T SELECT 'D', 200, NULL, 100
INSERT @T SELECT 'E' ,35, NULL, 135
INSERT @T SELECT 'F' ,100, 40, 445
INSERT @T SELECT 'G', 100, 30, 17
SELECT * FROM @T WHERE num3>=num1-ISNULL(num2,0)
/*name num1 num2 num3
---------- ----------- ----------- -----------
A 1000 0 2280
B 200 0 2125
E 35 NULL 135
F 100 40 445
*/