where后面如何用case..when

[rgshare]

declare @t table(bu_no int,bs_no int,uc_no int,us_no int)
insert into @t 
select d.bu_no,c.bs_no,b.uc_no,a.us_no 
from KT_U_Student as a 
inner join KT_U_Class as b on a.uc_no=b.uc_no
inner join KT_B_Special as c on b.bs_no=c.bs_no
inner join KT_B_Unit as d on c.bu_no=d.bu_no
where a.us_no=1128

select * from @t

select b.ola_no,c.olar_no,
    convert(varchar(10),b.ola_date,120) as period_date,
    convert(varchar(5),c.olar_starttime,114)+'—'+convert(varchar(5),c.olar_endtime,114) as period_time,
    case ola_type when 1 then '学生自拟' when 2 then '指定课题' when 3 then '指定项目' end as ola_itemtype,
    case ola_isappseat when 1 then '是' when 0 then '否' end as ola_appseat,
    case ola_isexam when 1 then '是' when 0 then '否' end as ola_exam,
    ola_maxperson,(select count(*) from KT_EXO_LabAppPerson where olar_no=c.olar_no) as yyy_num
from KT_EXO_LabApplication as a 
inner join KT_EXO_LabTimePeriod as b on a.ol_no=b.ol_no 
inner join KT_EXO_LabArrangement as c on b.ola_no=c.ola_no
where 1=1 and a.ol_no=1 and ol_islevel=2 
--我想让只有当b.ola_openobj=2的时候才进行下面判断,应该怎么做?
and 
(
	(select count(*) from KT_EXO_LabOpenObj where olobj_type=1 and ola_no=b.ola_no and olobj_id=(select bu_no from @t))>0	--学院
	or (select count(*) from KT_EXO_LabOpenObj where olobj_type=2 and ola_no=b.ola_no and olobj_id=(select bs_no from @t))>0	--专业
	or (select count(*) from KT_EXO_LabOpenObj where olobj_type=3 and ola_no=b.ola_no and olobj_id=(select uc_no from @t))>0	--班级
	or (select count(*) from KT_EXO_LabOpenObj where olobj_type=4 and ola_no=b.ola_no and olobj_id=(select us_no from @t))>0	--学生
)

[發糞塗牆]

你这个可能要用到动态SQL 了，看看这里有没有帮助：
http://blog.csdn.net/dba_huangzj/article/details/7684520

[rgshare]

不知道这样对不对

...
--我想让只有当b.ola_openobj=2的时候才进行下面判断,应该怎么做?
and 
(
	case when b.ola_openobj=2 then (select count(*) from KT_EXO_LabOpenObj where olobj_type=1 and ola_no=b.ola_no and olobj_id=(select bu_no from @t)) end>0	--学院
	or 
	case when b.ola_openobj=2 then (select count(*) from KT_EXO_LabOpenObj where olobj_type=2 and ola_no=b.ola_no and olobj_id=(select bs_no from @t)) end>0	--专业
	or 
	case when b.ola_openobj=2 then (select count(*) from KT_EXO_LabOpenObj where olobj_type=3 and ola_no=b.ola_no and olobj_id=(select uc_no from @t)) end>0	--班级
	or 
	case when b.ola_openobj=2 then (select count(*) from KT_EXO_LabOpenObj where olobj_type=4 and ola_no=b.ola_no and olobj_id=(select us_no from @t)) end>0	--学生
)

[以学习为目的]

--没有数据，不能测试，写法是可行的
and 
(
    case when b.ola_openobj=2 then (select count(*) from KT_EXO_LabOpenObj where olobj_type=1 and ola_no=b.ola_no and olobj_id=(select bu_no from @t)) end>0    --学院
    or 
    case when b.ola_openobj=2 then (select count(*) from KT_EXO_LabOpenObj where olobj_type=2 and ola_no=b.ola_no and olobj_id=(select bs_no from @t)) end>0    --专业
    or 
    case when b.ola_openobj=2 then (select count(*) from KT_EXO_LabOpenObj where olobj_type=3 and ola_no=b.ola_no and olobj_id=(select uc_no from @t)) end>0    --班级
    or 
    case when b.ola_openobj=2 then (select count(*) from KT_EXO_LabOpenObj where olobj_type=4 and ola_no=b.ola_no and olobj_id=(select us_no from @t)) end>0    --学生
)

--你试试下面这种写法
declare @t table(bu_no int,bs_no int,uc_no int,us_no int)
insert into @t 
select d.bu_no,c.bs_no,b.uc_no,a.us_no 
from KT_U_Student as a 
inner join KT_U_Class as b on a.uc_no=b.uc_no
inner join KT_B_Special as c on b.bs_no=c.bs_no
inner join KT_B_Unit as d on c.bu_no=d.bu_no
where a.us_no=1128

select * from @t

if  ol_islevel=2 
begin
select b.ola_no,c.olar_no,
    convert(varchar(10),b.ola_date,120) as period_date,
    convert(varchar(5),c.olar_starttime,114)+'—'+convert(varchar(5),c.olar_endtime,114) as period_time,
    case ola_type when 1 then '学生自拟' when 2 then '指定课题' when 3 then '指定项目' end as ola_itemtype,
    case ola_isappseat when 1 then '是' when 0 then '否' end as ola_appseat,
    case ola_isexam when 1 then '是' when 0 then '否' end as ola_exam,
    ola_maxperson,(select count(*) from KT_EXO_LabAppPerson where olar_no=c.olar_no) as yyy_num
from KT_EXO_LabApplication as a 
inner join KT_EXO_LabTimePeriod as b on a.ol_no=b.ol_no 
inner join KT_EXO_LabArrangement as c on b.ola_no=c.ola_no
where 1=1 and a.ol_no=1 
and 
(
    (select count(*) from KT_EXO_LabOpenObj where olobj_type=1 and ola_no=b.ola_no and olobj_id=(select bu_no from @t))>0    --学院
    or (select count(*) from KT_EXO_LabOpenObj where olobj_type=2 and ola_no=b.ola_no and olobj_id=(select bs_no from @t))>0    --专业
    or (select count(*) from KT_EXO_LabOpenObj where olobj_type=3 and ola_no=b.ola_no and olobj_id=(select uc_no from @t))>0    --班级
    or (select count(*) from KT_EXO_LabOpenObj where olobj_type=4 and ola_no=b.ola_no and olobj_id=(select us_no from @t))>0    --学生
)
end

[rgshare]

[Quote=引用 3 楼 的回复:]

SQL code

--没有数据，不能测试，写法是可行的
and
(
case when b.ola_openobj=2 then (select count(*) from KT_EXO_LabOpenObj where olobj_type=1 and ola_no=b.ola_no and olobj_id=(select bu_no from @t)) end>0 --……
[/Quote]
可以这样,但是我想偷一下懒,你这样那么又需要复制一大块数据