charindex运用别名

charindex使用别名?

SQL code

select     ...    [dbo].[KF_ImplodeColumn](b.exc_no,'get_fostergrad',default,default)    as [培养层次],--取出来的数据格式类似于:博士,研究生        ...from    KT_EX_TeachPlan as a     inner join KT_EX_Course as b on a.extc_no=b.exc_no    inner join KT_B_DataDict as c on b.exc_category=c.bdd_nowhere     1=1 and ...      and charindex((select bdd_name from KT_B_DataDict where bdd_no=11790),[培养层次])>0          --根据一个编号(11790)得到一个字符串(bdd_name),如:研究生,判断是否在[培养层次]这一列中,但是这样写会报"列明无效"的错误,最后我想到用exists查,虽然结果出来了,但是感觉绕了一个大弯,就用charindex应该怎么做????

SQL code

declare @a table([id] int,[c1] varchar(2),[c2] int)insert @aselect 1,'aa',1 union allselect 2,'ab',1 union allselect 3,'ac',1 union allselect 4,'ad',2 union allselect 5,'ae',2declare @b table([id] int,[c1] varchar(1))insert @bselect 1,'a'select * from @a a left join @b b on a.id=b.idwhere charindex((select c1 from @b where c2=1) ,a.c1)>0/*id          c1   c2          id          c1----------- ---- ----------- ----------- ----1           aa   1           1           a2           ab   1           NULL        NULL3           ac   1           NULL        NULL*/
[解决办法]
探讨

引用:

SQL code

declare @a table([id] int,[c1] varchar(2),[c2] int)
insert @a
select 1,'aa',1 union all
select 2,'ab',1 union all
select 3,'ac',1 union all
select 4,'ad',2 union all
s……