数据库记录如下：
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,第二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 行受影响)*/