有个A表,里面有三列A1,A2,A3
我想让对查询结果增加两列C1,C2,为固定值,通常这么写:Select 'testc1' as C1,'testC2' as C2,A1,A2,A3 from A where A3=99;
则查询结果为:
testc1,testC2,1,1,99现在的问题是,我想实现当A表中没有匹配结果时,仍返回一个A1,A2,A3为空值的行:
testc1,testC2,,,比如查A3=110,但A表中没有A3=110的记录,就返回一个有testc1,testC2其它为空值的行,
这个有没什么办法能写出这样的SQL语句?
我想让对查询结果增加两列C1,C2,为固定值,通常这么写:Select 'testc1' as C1,'testC2' as C2,A1,A2,A3 from A where A3=99;
则查询结果为:
testc1,testC2,1,1,99现在的问题是,我想实现当A表中没有匹配结果时,仍返回一个A1,A2,A3为空值的行:
testc1,testC2,,,比如查A3=110,但A表中没有A3=110的记录,就返回一个有testc1,testC2其它为空值的行,
这个有没什么办法能写出这样的SQL语句?
我的理解是testc1有值就用A1,A2,没有值就塞空的吧,不知道是不是这个意思
select tt.*,row_number()over(order by 3,4,5 desc)rn from(
Select 'testc1' as C1,'testC2' as C2,A1,A2,A3 from A where A3=99
union select 'testc1','testC2',null,null,null from dual)tt)
where rn=1 or (A1 is not null or A2 is not null or A3 is not null)
select tt.*,row_number()over(order by 3,4,5 desc)rn from(
Select 'testc1' as C1,'testC2' as C2,A1,A2,A3 from A where A3=99
union select 'testc1','testC2',null,null,null from dual)tt)
where rn=1 or (A1 is not null or A2 is not null or A3 is not null)