try case函数：）您可以指定应该显示的内容和位置
https://www.w3schools.com/sql/func_mysql_case.asp

你想要一个 left join :

select coalesce(cm.cap_id, 0) as cap_id
from (select 'Type1' as cap_type, 'CapDesc1' as cap_desc union all
      select 'Type2' as cap_type, 'CapDesc2' as cap_desc union all
      select 'Type3' as cap_type, 'CapDesc3' as cap_desc union all
      select 'Type4' as cap_type, 'CapDesc4' as cap_desc
     ) c left join
     cap_master cm
     on cm.cap_type = c.cap_type and cm.cap_desc = c.cap_desc
order by c.cap_type, c.cap_desc;

如果你需要支持
NULL cap_desc （这不是原始问题的一部分），您可以：

select coalesce(cm.cap_id, 0) as cap_id
from (
      select 'Type5' as cap_type, null as cap_desc
     ) c left join
     cap_master cm
     on cm.cap_type = c.cap_type and 
        (cm.cap_desc = c.cap_desc or cm.cap_desc is null and c.cap_desc is null)
order by c.cap_type, c.cap_desc;

这是一个sql小提琴。

我能想出的解决办法。不确定这是否会对效率造成很大的伤害，我想我最多有20个条件。如果有其他选择，请告诉我。

SELECT ifnull((SELECT cap_id FROM cap_master WHERE cap_type = 'Type4' AND 
cap_desc = 'CapDesc4' ),0) as cap_id
union all
SELECT ifnull((SELECT cap_id FROM cap_master WHERE cap_type = 'Type7' AND 
cap_desc = 'CapDesc7' ),0) as cap_id
union all
SELECT ifnull((SELECT cap_id FROM cap_master WHERE cap_type = 'Type3' AND 
cap_desc = 'CapDesc3' ),0) as cap_id
union all
SELECT ifnull((SELECT cap_id FROM cap_master WHERE cap_type = 'Type6' AND 
cap_desc = 'CapDesc6' ),0) as cap_id

我会用ifnull来做你想做的事。由于您没有提供运行查询所需的最小数据集，因此未对其进行测试：

SELECT IFNULL( (SELECT cap_id FROM cap_master WHERE   
        (cap_type = 'Type1' AND cap_desc = 'CapDesc1`) 
     OR (cap_type = 'Type2' AND cap_desc = 'CapDesc2')
     OR (cap_type = 'Type3' AND cap_desc = 'CapDesc3')
     OR (cap_type = 'Type4' AND cap_desc = 'CapDesc4')
order by cap_type,cap_desc) ,'0');

在这里您可以找到有关ifnull的更多详细信息