SELECT *,CASE WHEN B=0 THEN A*C ELSE B*C END 'M' FROM TB
select m=(case when b is null then a*c else b.*c end) from tb
update tb set m=(case when len(b)=0 then a*c else b*c end)
select m=case when b=0 then a*c else b*c end from tab
select val=(case when isnull(b,0)=0 then a else b end)*c from tb
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-07 13:31:24 -- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) -- Mar 29 2009 10:27:29 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([a] INT,[b] INT,[c] INT) INSERT [tb] SELECT 2,0,3 UNION ALL SELECT 2,3,4 GO --SELECT * FROM [tb]-->SQL查询如下: select isnull(nullif(b,0),a)*c as val from tb /* val ----------- 6 12(2 行受影响) */
m=(case when b is null then a*c else b.*c end)
from
tb
select m=case when b=0 then a*c
else b*c
end
from tab
-- Author : htl258(Tony)
-- Date : 2010-04-07 13:31:24
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] INT,[b] INT,[c] INT)
INSERT [tb]
SELECT 2,0,3 UNION ALL
SELECT 2,3,4
GO
--SELECT * FROM [tb]-->SQL查询如下:
select isnull(nullif(b,0),a)*c as val from tb
/*
val
-----------
6
12(2 行受影响)
*/