我有两个表users和*activation_details*
Users表包含这些数据。uid是主键。
uid | user_name
______________________
1 | John Smith
2 | Mary Smith
3 | Nancy Smith
4 | Agent Smith
activation_details有这些数据
aid | is_activated | user_id
______________________________________
1 | 0 | 0
2 | 1 | 4
3 | 1 | 1
4 | 1 | 777
请注意，用户id 777不在users表中。
我需要这种格式的结果
aid | is_activated | user_name
______________________________________________
1 | 0 | 0
2 | 1 | Agent Smith
3 | 1 | John Smith
4 | 1 | 777
如果users表中存在user_id，则必须显示username，否则必须显示user_id本身。
我试过一些类似的东西
SELECT aid, is_activated, user_id, users.user_name from activation_details, users WHERE users.uid = user_id
它给出的输出是无用的。
aid | is_activated | user_name | user_id
________________________________________________________
2 | 1 | Agent Smith | 4
3 | 1 | John Smith | 1
有没有只使用mysql来获得这个输出，我可以用PHP中的多个查询来完成，但这不是我想要的。
aid | is_activated | user_name
______________________________________
1 | 0 | 0
2 | 1 | Agent Smith
3 | 1 | John Smith
4 | 1 | 777
您希望将外部联接与coalesce()函数（返回所选列中的第一个非null值）结合使用，如下所示：
SELECT
a.aid,
a.is_activated,
a.user_id,
coalesce(b.user_name, a.user_id, 0) as User
from
activation_details a
left outer join users b
on a.user_id=b.uid
编辑：我为coalesce()函数添加了一个检查，如果user_name和user_id字段都是空值，它将为用户返回一个0。
在mysql:中进行测试
mysql> select * from first
-> ;
+------+-------+
| id | title |
+------+-------+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
+------+-------+
3 rows in set (0.00 sec)
mysql> select coalesce('a', id) from first
-> ;
+-------------------+
| coalesce('a', id) |
+-------------------+
| a |
| a |
| a |
+-------------------+
3 rows in set (0.00 sec)
mysql> select coalesce('a', 1) from first;
+------------------+
| coalesce('a', 1) |
+------------------+
| a |
| a |
| a |
+------------------+
3 rows in set (0.00 sec)
mysql> select coalesce(null, 1) from first;
+-------------------+
| coalesce(null, 1) |
+-------------------+
| 1 |
| 1 |
| 1 |
+-------------------+
3 rows in set (0.00 sec)
SELECT t3.aid,t3.is_activated,
IF (t2.user_name IS NOT null, t2.user_name, t3.user_id)
FROM activation_details AS t3
LEFT JOIN users AS t2 ON t3.user_id = t2.uid;
我认为这个查询将给出