有个配置表
t_conf
(
funccode varchar2(30) not null, --属性代码
funcvalue number(10) default 0 not null, --属性值
)其中 ('user_name_case', 1)或者('user_name_case', 0)
分表代表funccode(user_name_case)对应的funcvalue为1则用户名大小写敏感,如果为0则用户名大小写不敏感用户表
user_name_case
(
userindex int,
username varchar2(36)
statue int
)现在给出一个用户名v_username,查询该用户的userindex,但是需要先通过user_name_case来判断用户名username是否大小写敏感,
如果敏感的话则查询userindex的条件为username=v_username,如果不敏感则lower(username) == lower(v_username)。现在要求写一个复杂的sql语句能一次性的查询出该用户的userindex
t_conf
(
funccode varchar2(30) not null, --属性代码
funcvalue number(10) default 0 not null, --属性值
)其中 ('user_name_case', 1)或者('user_name_case', 0)
分表代表funccode(user_name_case)对应的funcvalue为1则用户名大小写敏感,如果为0则用户名大小写不敏感用户表
user_name_case
(
userindex int,
username varchar2(36)
statue int
)现在给出一个用户名v_username,查询该用户的userindex,但是需要先通过user_name_case来判断用户名username是否大小写敏感,
如果敏感的话则查询userindex的条件为username=v_username,如果不敏感则lower(username) == lower(v_username)。现在要求写一个复杂的sql语句能一次性的查询出该用户的userindex
with t_conf as
(select 'user_name_case' funccode ,0 funcvalue from dual) ,
user_name_case as
(select 1 userindex, 'A' username ,0 statue from dual
union all
select 2 ,'a',1 from dual)
select userindex
from user_name_case
where (case
when (select funcvalue from t_conf where funccode = 'user_name_case') = 1 then
replace(username, 'a')
else
replace(lower(username), lower('a'))
end) is null
用户表(表名写错了):
t_user
(
userindex int,
username varchar2(36)
statue int
)
回复一下1、2楼的:
v_username 是一个真实的用户名 eg:
v_username := 'li_san';user_name_case就只是指表t_user中username是否大小敏感
select userindex from (
select t1.userindex
from user_name_case t1, t_conf t2
where t2.funccode = 'user_name_case' and t2.funcvalue = 0
and t1.username = 'AAA'
union all
select t1.userindex
from user_name_case t1, t_conf t2
where t2.funccode = 'user_name_case' and t2.funcvalue = 1
and lower(t1.username) = lower('AAA')
) t where userindex is not null;
还有,你是在过程中执行的吧?你想要‘一次性的查询出该用户的userindex’,是不是指用一条sql语句就能查出来结果?这个当然也能实现,但是既然是在过程中执行了,几条sql语句就没关系了,并不一定要一条sql才行,按照你说的思路用两条sql实现就很自然了。
这个不行。如果t_conf没有'user_name_case',按用户名敏感处理
with t_conf as
(select 'user_name_case' funccode ,0 funcvalue from dual) ,
t_user as
(select 1 userindex, 'A' username ,0 statue from dual
union all
select 2 ,'a',1 from dual)
select userindex
from t_user
where (case
when (select funcvalue from t_conf where funccode = 'user_name_case') = 1 then
replace(username, 'a')
else
replace(lower(username), lower('a'))
end) is null
这个不行么?
t_conf表(funccode|funcvalue): 这个表就是一个配置表每条记录作用不一样
user_name_case|1
auto_delete|1
用户表(userindex|username|statue)其中userindex、username分别为唯一索引:
168|wjj2|1
159|008|1
165|jsbc2|1
164|jsbc3|1
from t_user
where ((select funcvalue from t_conf where funccode = 'user_name_case') = 1 and
lower(username) = lower('dD'))
or ((select funcvalue from t_conf where funccode = 'user_name_case') = 0 and
username = 'dD')
select userindex
from t_user
where ((select funcvalue from t_conf where funccode = 'user_name_case') = 1 and
lower(username) = lower('dD'))
or (nvl((select funcvalue from t_conf where funccode = 'user_name_case'),0) = 0 and
username = 'dD')
select 'user_name_case' funccode ,1 funcvalue from dual
union all
select 'auto_delete' funccode ,1 funcvalue from dual
)
,
t_user as(
select 168 userindex, 'wjj2' username ,1 statue from dual
union all
select 159 ,'008',1 from dual
union all
select 165 ,'jsbc2',1 from dual
union all
select 164 ,'jsbc3',1 from dual
)
select userindex
from t_user
where
(case
when (select funcvalue from t_conf where funccode = 'user_name_case') = 1
then
replace(username,v_username)
else
replace(lower(username),lower(v_username))
end) is null
select userindex
from t_user
where (case
when (select funcvalue from t_conf where funccode = 'user_name_case') = 1 then
replace(username, 'a')
else
replace(lower(username), lower('a'))
end) is null把里面的‘a'替换成你传入的参数,应该没问题吧大小写敏感的话,这两个就不是一个人了
li_san
li_San
你的sql语句也是Ok的。tks。