27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-23 16:25:25
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[yzh] numeric(4,2),[thf] numeric(4,2),[qxf] numeric(3,2))
insert [tb]
select 1,23.00,4.00,8.00 union all
select 2,0.00,12.76,4.20 union all
select 3,5.00,45.44,4.44 union all
select 4,48.00,12.22,3.33
--------------开始查询--------------------------
select id,yzh,thf=(case when yzh in('0','5') then thf else thf+2 end),qxf from [tb]
----------------结果----------------------------
/* id yzh thf qxf
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1 23.00 6.00 8.00
2 0.00 12.76 4.20
3 5.00 45.44 4.44
4 48.00 14.22 3.33
(4 行受影响))
*/
create table [tb]([id] int,[yzh] numeric(4,2),[thf] numeric(4,2),[qxf] numeric(3,2))
insert [tb]
select 1,23.00,4.00,8.00 union all
select 2,0.00,12.76,4.20 union all
select 3,5.00,45.44,4.44 union all
select 4,48.00,12.22,3.33
go
--search
select id,yzh ,
case when yzh <> 0 and yzh <> 5 then thf+2 else thf end thf,
qxf
from tb
select id,yzh ,
case when yzh not in(0,5) then thf+2 else thf end thf,
qxf
from tb
/*
id yzh thf qxf
----------- ------ ------- -----
1 23.00 6.00 8.00
2 .00 12.76 4.20
3 5.00 45.44 4.44
4 48.00 14.22 3.33
(所影响的行数为 4 行)
*/
go
create view my_view as
select id,yzh ,
case when yzh <> 0 and yzh <> 5 then thf+2 else thf end thf,
qxf
from tb
go
select * from my_view
/*
id yzh thf qxf
----------- ------ ------- -----
1 23.00 6.00 8.00
2 .00 12.76 4.20
3 5.00 45.44 4.44
4 48.00 14.22 3.33
(所影响的行数为 4 行)
*/
drop table tb
drop view my_view
select id,yzh,thf=(case when (yzh=!0 or yzh=!5) then thf+2 else thf end),qxf from
tb
create view view_1 as
select id,yzh,thf=thf+(case when yzh!=0.00 and yzh!=5.00 then 2 else 0 end),qxf from 表
go
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-23 16:25:25
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[yzh] numeric(4,2),[thf] numeric(4,2),[qxf] numeric(3,2))
insert [tb]
select 1,23.00,4.00,8.00 union all
select 2,0.00,12.76,4.20 union all
select 3,5.00,45.44,4.44 union all
select 4,48.00,12.22,3.33
--------------开始查询--------------------------
select id,yzh,thf=(case when yzh in('1','5') then thf else yzh+2 end),qxf from [tb]
----------------结果----------------------------
/* id yzh thf qxf
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1 23.00 25.00 8.00
2 0.00 2.00 4.20
3 5.00 45.44 4.44
4 48.00 50.00 3.33
(4 行受影响)
*/
--search
select id,yzh ,
case when yzh <> 0 and yzh <> 5 then thf+2 else thf end thf,
gxf
from tb
--view
create view my_view as
select id,yzh ,
case when yzh <> 0 and yzh <> 5 then thf+2 else thf end thf,
gxf
from tb
SELECT
id ,
CASE WHEN yzh NOT IN(0,5) THEN yzh+2 ELSE yzh END AS yzh
, thf
, qxf
FROM TB