22,209
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-29 10:02:40
-- 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]([M_ID] int,[Money] int)
insert [tb]
select 1,100 union all
select 2,-50 union all
select 3,200 union all
select 4,-1000 union all
select 5,40 union all
select 6,90
--------------开始查询--------------------------
select
M_ID,
positive=(case when Money > 0 then convert(varchar,Money) else null end),
negative=(case when Money < 0 then convert(varchar,Money) else null end)
from tb
----------------结果----------------------------
/* M_ID positive negative
----------- ------------------------------ ------------------------------
1 100 NULL
2 NULL -50
3 200 NULL
4 NULL -1000
5 40 NULL
6 90 NULL
(6 行受影响)
*/
SELECT MID
,CASE WHEN [MONEY]>0 THEN MONEY ELSE NULL END 'positive'
,CASE WHEN [MONEY]<0 THEN MONEY ELSE NULL END 'negative'
FROM TB
SELECT MID
,CASE WHEN [MONEY]>=0 THEN [MONEY] ELSE NULL END 'positive'
,CASE WHEN [MONEY]<0 THEN [MONEY] ELSE NULL END 'negative'
FROM TB
create table #student( M_ID int,Money int)
insert #student select
1, 100 union all select
2, -50 union all select
3, 200 union all select
4, -1000 union all select
5, 40 union all select
6, 90
select M_ID,
positive=case when money>0 then money else null end,
negative=case when money>0 then null else money end
from #student
M_ID positive negative
----------- ----------- -----------
1 100 NULL
2 NULL -50
3 200 NULL
4 NULL -1000
5 40 NULL
6 90 NULL
(6 行受影响)
drop table #student
SELECT M_ID,
CASE WHEN Money>0 THEN Money END AS positive ,
CASE WHEN Money<=0 THEN Money END AS negative ,
FROM TB
select m_id,
case when money>0 then positive end,
case when money<0 then negative end
from tb
SELECT MID
,CASE WHEN [MONEY]>=0 THEN MONEY ELSE NULL END 'positive'
,CASE WHEN [MONEY]<0 THEN MONEY ELSE NULL END 'negative'
FROM TB
SELECT M_ID,
CASE WHEN Money>0 THEN Money END AS positive ,
CASE WHEN Money<0 THEN Money END AS negative ,
FROM TB
case when轻松搞定100分
select m_id,
case when money>0 then positive end,
case when money<=0 then positive end
from tb