如何解决CASE WHEN的多条件查询问题。
数据库记录如下：
CateFolderCurrFolder
NULLpublic
/publicNULL
/publicNULL
……
我用下面的两个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
[解决办法]
设原数据为:
IDCateFolderCurrFolder
1NULLpublic
2/publicNULL
3/publicNULL
4/publiccss
5/public/cssNULL
你希望得到什么结果?
[解决办法]
--t_log表的自增加属性,我去掉了.分两步,第一update,第二insertCREATE 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')godeclare @stime as datetimeset @stime = '2007-12-27'--update update t_logset vm = m.vmfrom t_log t , (select id,name ,vm = case when px = 1 then 1200 when px = 2 then 1000 when px = 3 then 850 endfrom ( 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) twhere px <= 3) mwhere t.name = m.name--insertinsert into t_log select id,name ,vm = case when px = 1 then 1200 when px = 2 then 1000 when px = 3 then 850 endfrom ( 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) twhere 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 张三 12002 AA 8505 DD 10006 EE 8508 GG 120010 AA2 120011 BB3 100012 CC4 85014 EE6 100015 FF4 85016 GG5 1200(11 行受影响)*/
[解决办法]
declare @t table (CateFolder varchar(10), CurrFolder varchar(10))insert @t select NULL,'public'union all select null,'public'union all select '/public', NULLunion all select '/public', 'dsds'select * from @t where 1 = CASE WHEN CateFolder IS NULL THEN case when CurrFolder = 'public ' then 1 else 0 endWHEN CurrFolder IS NULL THEN case when CateFolder ='/public' then 1 else 0 end ELSE case when Catefolder + '/ ' + CurrFolder = '/public ' then 1 else 0 endEND
[解决办法]
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
--
没看懂