mysql - 简单 SQL SELECT 查询中的 IF..ELSE 语句

Question

我有 2 个表 tmp1 和 tmp2。它们如下所示:

tmp1:

+----+------+---------+---------+
| id | name | add1    | add2    |
+----+------+---------+---------+
| 1  | NULL | NULL    | NULL    |
| 2  | NULL | NULL    | NULL    |
| 3  | mum1 | rajpur1 | rajpur2 |
| 4  | mum3 | rajpur3 | rajpur4 |
+----+------+---------+---------+

tmp2:

+----+------+---------+---------+
| id | name | add1    | add2    |
+----+------+---------+---------+
| 1  | NULL | NULL    | NULL    |
| 2  | mum  | rajpur  | rajpur1 |
| 3  | NULL | NULL    | NULL    |
| 4  | mum2 | rajpur2 | rajpur3 |
+----+------+---------+---------+

我想在此处使用 if..else 语句来检索一些数据，例如:

Case 1: if id=1 then (select * from tmp1 where id=1)

Case 2: if id=2 then (select * from tmp2 where id=2)

Case 3: if id=3 then (select * from tmp1 where id=3)

Case 4: if id=4 then (select * from tmp1 where id=4)  

我最近两天正在尝试解决这个问题。请帮忙，这非常重要。

Answer 1

我认为以下查询是您真正想要实现的:

select 
 tmp1.id id, 
 coalesce(tmp1.name,tmp2.name) name,
 coalesce(tmp1.add1,tmp2.add1) add1,
 coalesce(tmp1.add2,tmp2.add2) add2 
from 
 tmp1 inner join tmp2 on tmp1.id=tmp2.id;

结果:

+------+------+---------+---------+
| id   | name | add1    | add2    |
+------+------+---------+---------+
|    1 | NULL | NULL    | NULL    |
|    2 | mum  | rapjur  | rapjur1 |
|    3 | mum1 | rajpur1 | rajpur2 |
|    4 | mum3 | rajpur3 | rajpur4 |
+------+------+---------+---------+

info on coalesce