返回空值IN子句mysql

admin

返回空值IN子句mysql

sql

当数据库中没有数据时，找不到打印。例如，在我的数据库中，我没有，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

阅读 151

2021-06-07

共1个答案

admin

您需要使用其他方法。您将需要使用UNION ALL创建具有所有值的内联视图，然后将其与users表保持连接：

sqlfiddle

查询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 |

2021-06-07