怎么解决CASE WHEN的多条件查询有关问题

如何解决CASE WHEN的多条件查询问题。
数据库记录如下：
CateFolder CurrFolder
NULL public
/public NULL
/public NULL
……

我用下面的两个CASE才能完成：
CASE
WHEN CateFolder IS NULL THEN CurrFolder
WHEN CurrFolder IS NULL THEN CateFolder
ELSE Catefolder + '/ ' + CurrFolder
END
=
CASE
WHEN CateFolder IS NULL THEN 'public '
WHEN CurrFolder IS NULL THEN '/public '
ELSE '/public '
END

如果我只使用一个CASE怎么完成？
谢谢。

------解决方案--------------------

CASE
WHEN CateFolder IS NULL and CurrFolder IS not NULL THEN CurrFolder= 'public '
WHEN CurrFolder IS NULL and CateFolder IS not NULL THEN CateFolder= '/public '
ELSE Catefolder + '/ ' + CurrFolder= '/public '
END

------解决方案--------------------
设原数据为:
ID CateFolder CurrFolder
1 NULL public
2 /public NULL
3 /public NULL
4 /public css
5 /public/css NULL

你希望得到什么结果?
------解决方案--------------------

SQL code

--t_log表的自增加属性,我去掉了.分两步,第一update,第二insert
CREATE TABLE [dbo].[T_info] ( [id] [int] IDENTITY (1, 1) NOT NULL , [classid] [int] NULL , [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [results] [float] NULL , [stime] [datetime] NULL ) ON [PRIMARY] 
GO 
CREATE TABLE [dbo].[T_log] ( [id] [int] , [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [vm] [numeric](18, 0) NULL ) ON [PRIMARY] 
GO 
insert into T_info(classid,name,results,stime) values('1','张三','90','2007-12-27') 
insert into T_info(classid,name,results,stime) values('1','AA','80','2007-12-27') 
insert into T_info(classid,name,results,stime) values('1','BB','70','2007-12-27') 
insert into T_info(classid,name,results,stime) values('1','CC','60','2007-12-27') 
insert into T_info(classid,name,results,stime) values('2','DD','50','2007-12-27') 
insert into T_info(classid,name,results,stime) values('2','EE','40','2007-12-27') 
insert into T_info(classid,name,results,stime) values('2','FF','30','2007-12-27') 
insert into T_info(classid,name,results,stime) values('2','GG','65','2007-12-27') 
insert into T_info(classid,name,results,stime) values('1','张三','90','2007-12-27') 
insert into T_info(classid,name,results,stime) values('3','AA2','80','2007-12-27') 
insert into T_info(classid,name,results,stime) values('3','BB3','70','2007-12-27') 
insert into T_info(classid,name,results,stime) values('3','CC4','60','2007-12-27') 
insert into T_info(classid,name,results,stime) values('1','DD5','50','2007-12-27') 
insert into T_info(classid,name,results,stime) values('4','EE6','40','2007-12-27') 
insert into T_info(classid,name,results,stime) values('4','FF4','30','2007-12-27') 
insert into T_info(classid,name,results,stime) values('4','GG5','65','2007-12-27') 
insert into T_log(id , name,vm) values(1 , '张三','109')
go

declare @stime as datetime
set @stime = '2007-12-27'

--update 
update t_log
set vm = m.vm
from t_log t , (select id,name ,vm = case when px = 1 then 1200
                 when px = 2 then 1000
                 when px = 3 then 850
            end
from 
(
  select px = (select count(1) from t_info where classid = t.classid and stime = @stime and results > t.results) + 1,* from t_info t where stime = @stime
) t
where px <= 3) m
where t.name = m.name

--insert
insert into t_log 
select id,name ,vm = case when px = 1 then 1200
                 when px = 2 then 1000
                 when px = 3 then 850
            end
from 
(
  select px = (select count(1) from t_info where classid = t.classid and stime = @stime and results > t.results) + 1,* from t_info t where stime = @stime
) t
where px <= 3 and name not in (select name from t_log)

select * from t_log

--drop table t_info,t_log

/*
id          name                                               vm
----------- -------------------------------------------------- ---------------------------------------
1           张三                                                 1200
2           AA                                                 850
5           DD                                                 1000
6           EE                                                 850
8           GG                                                 1200
10          AA2                                                1200
11          BB3                                                1000
12          CC4                                                850
14          EE6                                                1000
15          FF4                                                850
16          GG5                                                1200

(11 行受影响)
*/

怎么解决CASE WHEN的多条件查询有关问题

相关推荐