像这样的方法可能有效：

select a.filename, a.value
from <table> a

union

select replace(a.filename, 'FileA', 'FileB'), a.value
from <table> a
    left join <table> b on a.filename = replace(b.filename, 'FileB', 'FileA')
where a.fileName like 'FileA'
and b.filename is null;

正确答案

select a.filename, a.value
from testtable a

union

select replace(a.filename, 'FileA', 'FileB'), a.value
from testtable a
    left join testtable b on replace(a.filename, 'FileA', 'FileB') = b.filename
where a.fileName like 'FileA%'
and b.filename is null;

用缺失的数据填充

insert into testtable (fileName, value)
select replace(a.filename, 'FileA', 'FileB'), a.value
from testtable a
    left join testtable b on replace(a.filename, 'FileA', 'FileB') = b.filename
where a.fileName like 'FileA%'
and b.filename is null;