不太确定,试一下
select emp.employee_id,
emp.employee_name,
nvl(case when rule.rule_flag=1 then a.a_name
when rule.rule_flag=2 then b.b_name
when rule.rule_flag=3 then c.c_name
end,' ') name,
nvl(rule.rule_flag,' ')
from emp,
rule,
a,
b,
c
where emp.employee_id=rule.employee_id(+) and
rule.rule_id=a.a_id and,
rule.rule_id=b.b_id and,
rule.rule_id=c.c_id
select emp.employee_id,
emp.employee_name,
nvl(case when rule.rule_flag=1 then a.a_name
when rule.rule_flag=2 then b.b_name
when rule.rule_flag=3 then c.c_name
end,' ') name,
nvl(rule.rule_flag,' ')
from emp,
rule,
a,
b,
c
where emp.employee_id=rule.employee_id(+) and
rule.rule_id=a.a_id and,
rule.rule_id=b.b_id and,
rule.rule_id=c.c_id
select emp.employee_id,
emp.employee_name,
nvl(case when rule.rule_flag=1 then a.a_name
when rule.rule_flag=2 then b.b_name
when rule.rule_flag=3 then c.c_name
end,' ') name,
nvl(rule.rule_flag,' ')
from emp,
rule,
a,
b,
c
where emp.employee_id=rule.employee_id(+) and
(rule.rule_id=a.a_id or
rule.rule_id=b.b_id or
rule.rule_id=c.c_id)在where的地方改了下,把and改成or就能查出一模一样的三条记录(实际应该只能找出1条记录)。
应该怎么改呢?
emp.employee_name,
nvl(case when rule.rule_flag=1 then a.a_name
when rule.rule_flag=2 then b.b_name
when rule.rule_flag=3 then c.c_name
end,' ') name,
nvl(rule.rule_flag,' ')
from emp,
rule,
a,
b,
c
where emp.employee_id=rule.employee_id(+) and
rule.rule_id=a.a_id(+) and,
rule.rule_id=b.b_id(+) and,
rule.rule_id=c.c_id(+)这样呢?
select distinct
emp.employee_id,
emp.employee_name,
nvl(case when rule.rule_flag=1 then a.a_name
when rule.rule_flag=2 then b.b_name
when rule.rule_flag=3 then c.c_name
end,' ') name,
nvl(rule.rule_flag,' ')
from emp,
rule,
a,
b,
c
where emp.employee_id=rule.employee_id(+) and
rule.rule_id=a.a_id(+) and
rule.rule_id=b.b_id(+) and
rule.rule_id=c.c_id(+)
emp.*, decode(rule_flag,'1',a_name,'2',b_name,'3',c_name), rule_flag
from emp, rule, a, b, c
where rule.employee_id(+)=emp.employee_id
and a.a_id(+)=rule.rule_id
and b.b_id(+)=rule.rule_id
and c.c_id(+)=rule.rule_id
select emp.employee_id,
emp.employee_name,
nvl(case when rule.rule_flag=1 then a.a_name
when rule.rule_flag=2 then b.b_name
when rule.rule_flag=3 then c.c_name
end,' ') name,
nvl(rule.rule_flag,' ')
from emp,
rule,
a,
b,
c
where emp.employee_id=rule.employee_id(+) and
rule.rule_id=a.a_id(+) and,
rule.rule_id=b.b_id(+) and,
rule.rule_id=c.c_id(+)这样就可以了!!!感谢!给分!