sql case when 语句

bl_song 2009-10-23 04:24:09
我有一个表:
id yzh thf qxf
1 23.00 4.00 8.00
2 0.00 12.76 4.20
3 5.00 45.44 4.44
4 48.00 12.22 3.33
我现在需要创建一个视图
当 yzh=!0 or yzh=!5 时,thf=thf+2 否则thf不变
视图结果应该为
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

也就是当yzh不等于0或5时,thf加上2.
谢谢

...全文
175 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
bl_song 2009-10-23
  • 打赏
  • 举报
回复
谢谢大家!
--小F-- 2009-10-23
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- 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 行受影响))
*/
dawugui 2009-10-23
  • 打赏
  • 举报
回复
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
bancxc 2009-10-23
  • 打赏
  • 举报
回复
晕 and
bancxc 2009-10-23
  • 打赏
  • 举报
回复
select id,yzh,thf=(case when (yzh=!0 or yzh=!5) then thf+2 else thf end),qxf from 
tb
子陌红尘 2009-10-23
  • 打赏
  • 举报
回复
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
--小F-- 2009-10-23
  • 打赏
  • 举报
回复
晕 是'0','5'
jwdream2008 2009-10-23
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 sql77 的回复:]
SQL codeSELECT
id ,CASEWHEN yzhNOTIN(0,5)THEN yzh+2ELSE yzhENDAS yzh
, thf
, qxfFROM TB
[/Quote]
正解!
--小F-- 2009-10-23
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- 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 行受影响)
*/
dawugui 2009-10-23
  • 打赏
  • 举报
回复

--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
SQL77 2009-10-23
  • 打赏
  • 举报
回复
SELECT
id ,
CASE WHEN yzh NOT IN(0,5) THEN yzh+2 ELSE yzh END AS yzh
, thf
, qxf
FROM TB

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧