麻烦那位大侠帮我看一下这条sql语句。
select distinct null,s.terminal_no,s.location,s.in_date,case when s.terminal_no in(select e.term_no from csyh.jian_cust) then e.connect_phone end connect_phone,case when s.terminal_no in(select e.term_no from csyh.jian_cust)then e.address end address,case when s.terminal_no in(select e.term_no from csyh.jian_cust)then e.implen_man end implen_man,case when s.terminal_no in(select e.term_no from csyh.jian_cust)then e.accept_date end accept_date from csyh.stock s,csyh.jian_cust e where 1=1 and s.status='2' order by s.in_date desc为什么在case条件满足时会查到两条数据?
select distinct null,s.terminal_no,s.location,s.in_date,case when s.terminal_no in(select e.term_no from csyh.jian_cust) then e.connect_phone end connect_phone,case when s.terminal_no in(select e.term_no from csyh.jian_cust)then e.address end address,case when s.terminal_no in(select e.term_no from csyh.jian_cust)then e.implen_man end implen_man,case when s.terminal_no in(select e.term_no from csyh.jian_cust)then e.accept_date end accept_date from csyh.stock s,csyh.jian_cust e where 1=1 and s.status='2' order by s.in_date desc为什么在case条件满足时会查到两条数据?
select distinct null,
s.terminal_no,
s.location,
s.in_date,
case when s.terminal_no in (select e.term_no from csyh.jian_cust) then e.connect_phone end connect_phone,
case when s.terminal_no in (select e.term_no from csyh.jian_cust) then e.address end address,
case when s.terminal_no in (select e.term_no from csyh.jian_cust) then e.implen_man end implen_man,
case when s.terminal_no in (select e.term_no from csyh.jian_cust) then e.accept_date end accept_date
from csyh.stock s,
csyh.jian_cust e
where 1=1 and
s.status='2'
order by s.in_date desc
--
/*
整理了一下你的sql语句,
发现case when写得有点问题,
应该case条件满足时会查到不只是两条数据,而是多条。
s.status='2'的数据行的s.terminal_no在e.term_no中对应对个值,
所以后面的then只好将所有的值返回咯!
*/
s.terminal_no,
s.location,
s.in_date,
case
when s.terminal_no in
(select e.term_no from csyh.jian_cust e) then
e.connect_phone
end connect_phone,
case
when s.terminal_no in
(select e.term_no from csyh.jian_cust e) then
e.address
end address,
case
when s.terminal_no in
(select e.term_no from csyh.jian_cust e) then
e.implen_man
end implen_man,
case
when s.terminal_no in
(select e.term_no from csyh.jian_cust e) then
e.accept_date
end accept_date
from csyh.stock s
where 1 = 1
and s.status = '2'
order by s.in_date desc另外,这样使用case when效率不好