MySql，如果没有返回行，则返回默认值

本文介绍了MySql，如果没有返回行，则返回默认值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个查询如下

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

这会根据 where 条件返回多行，我正在寻找的是不返回任何行的条件，我应该有一个默认值，例如0".到目前为止，我没有得到任何行.

This returns multiple rows based on where condition, what i am looking for is like for a condition which do not return any rows, i should have a default value say '0'. As of now i do not get any row for it.

例如，如果第三个条件 (cap_type = 'Type3' AND cap_desc = 'CapDesc3') 不匹配，我期望输出如下:

For e.g if 3rd condition (cap_type = 'Type3' AND cap_desc = 'CapDesc3') do not match, i am expecting an output as below:

23 34 0 45

我检查了给出的解决方案，例如

I checked solutions given, like

如果没有行匹配则返回一个值

如果没有找到行则返回默认值 -mysql

但似乎它们不适用于返回的多行.任何指针将不胜感激.

But seems they don't work on multiple rows getting returned. Any pointers will be greatly appreciated.

这里有一个 Fiddle 可以玩.

Here's a Fiddle to play with.

推荐答案

你想要一个 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(这不是原始问题的一部分)，你可以这样做:

If you need to support NULL cap_desc (which is not part of the original question), you can do:

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 Fiddle.

Here is a SQL Fiddle.