mysql - 返回空值 IN 子句 mysql

Question

当数据库中没有数据时打印not found。例如在我的数据库中我没有 56443 因此它应该打印 'not found'

SELECT uid, (CASE WHEN (u.uid = null) THEN 'not found' ELSE 'found' END) as result
FROM (SELECT uid
            FROM users
            WHERE uid IN (1,2,56443,3)) as u;

得到结果如下

+--------+--------+
| uid    | result|
+--------+--------+
| 1      | found | 
| 2      | found |
| 3      | found |
+--------+--------+

我还期待 not found 行 56443

Answer 1

您需要使用不同的方法。您将需要使用 UNION ALL 创建一个包含所有值的内联 View ，然后将其与用户表左连接:

SQL Fiddle

查询 1:

SELECT a.uid, (CASE WHEN (u.uid is null) THEN 'not found' ELSE 'found' END) as     result
FROM (select 1 as UID FROM dual
      UNION ALL
      select 2 as UID FROM dual
      UNION ALL
      select 56443 as UID FROM dual
      UNION ALL
      select 3 as UID FROM dual) as a
LEFT JOIN users u on a.uid = u.uid

[结果]:

|   UID |    result |
|-------|-----------|
|     1 |     found |
|     2 |     found |
|     3 |     found |
| 56443 | not found |