我现在有一个问题是:
我表tb中的字段为:
service_code,response_success,user_success
我想让查出的结果是:
如果service_code=1或者service_code=3时,response_success,user_success为表tb中内容
如果service_code!=1或者service_code!=3时,response_success,user_success的内容为NA
这样的sql语句我该如何写呢?
请教高手了,谢谢!
我表tb中的字段为:
service_code,response_success,user_success
我想让查出的结果是:
如果service_code=1或者service_code=3时,response_success,user_success为表tb中内容
如果service_code!=1或者service_code!=3时,response_success,user_success的内容为NA
这样的sql语句我该如何写呢?
请教高手了,谢谢!
1、select case when service_code=1 or service_code=3 then response_success else 'NA' end response_success ,
case when service_code=1 or service_code=3 then user_success else 'NA' end user_success from tb;
2、select response_success,user_success from tb where service_code in(1,3)
union all
select 'NA','NA' from tb where service_code not in(1,3)
如果service_code!=1或者service_code!=3时,response_success,user_success的内容为NA
写到二个SQL条件矛盾吧,
第二个是不是
如果service_code!=1并且!!!!service_code!=3时???
select case when service_code in(1,3) then response_success else 'NA' end response_success,
case when service_code in(1,3) then user_success else 'NA' end user_success
from tb
--
with tb as(select '1' service_code, 'true' response_success, 'false' user_success union all
select '3' service_code, 'false' response_success, 'true' user_success union all
select '2' service_code, 'true' response_success, 'true' user_success union all)select service_code, decode(service_code, '1', response_success, '3', response_success, 'NA'),decode(service_code, '1', user_success, '3', user_success, 'NA') from tb
------------------------------------------
service_code response_success user_success
'1' 'true' 'false'
'3' 'false' 'true'
'2' 'NA' 'NA'--ps:你的第2个条件应该是并且的关系
如果service_code!=1或者service_code!=3时,response_success,user_success的内容为NA楼主自己理理逻辑,两个或,都矛盾了.....
case when
--service_code,response_success,user_success
select case service_code when not in (1,3) then 'NA' else response_success end,case service_code when not in (1,3) then 'NA' else user_success end from tb;
(case when service_code in (1,3) then user_success else 'NA' end) as user_success
from tb;
(case when service_code in (1,3) then response_success else 'NA' end)as response_success,
(case when service_code in (1,3) then user_success else 'NA' end) as user_success
,decode(service_code,1,response_success,3,response_success,'NA') AS response_success
,decode(service_code,1,user_success,3,user_success,'NA') AS user_success
from talbe