sql查询两列之差

[p05128]

Factor Simple1 Simple2
Length 200 200
Type DWT DWT
High 100 90

有上面这样一个表怎么样求两列之差（Simple1-Simple2）

[jwdream2008]

select Factor,(Simple1-Simple2) as '差额' From Table1

????

[永生天地]

select Simple1-Simple2 where isnumeric(Simple1)=1 and isnumeric(Simple2)=1

[rucypli]

字符怎么做差

[Mr_Nice]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([Factor] varchar(6),[Simple1] varchar(3),[Simple2] varchar(3))
insert [TB]
select 'Length','200','200' union all
select 'Type','DWT','DWT' union all
select 'High','100','90'

select * from [TB]

SELECT factor,ve = col1-col2
FROM (
SELECT factor, col1 = CASE WHEN ISNUMERIC([Simple1])>0 THEN CONVERT(INT,simple1) ELSE 0 END,
 col2 = CASE WHEN ISNUMERIC([Simple2])>0 THEN CONVERT(INT,simple1) ELSE 0 END
FROM dbo.TB )T

/*
factor ve
------ -----------
Length 0
Type   0
High   0

(3 行受影响)
*/

瞎猜了一个。

[Mr_Nice]

[Quote=引用 3 楼 rucypli 的回复:]

字符怎么做差
[/Quote]

同问

[美女她妈]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Factor] varchar(6),[Simple1] varchar(3),[Simple2] varchar(3))
insert [tb]
select 'Length','200','200' union all
select 'Type','DWT','DWT' union all
select 'High','100','90'
go

select *,
差=case when isnumeric(Simple1)=0 or isnumeric(Simple2)=0 then null else cast(Simple1 as int)-cast(Simple2 as int) end
from tb

/**
Factor Simple1 Simple2 差
------ ------- ------- -----------
Length 200     200     0
Type   DWT     DWT     NULL
High   100     90      10

(3 行受影响)
**/