Mysql获取在条件中描述的所有记录，即使它们在表中不存在。

19 浏览
0 Comments

Mysql获取在条件中描述的所有记录，即使它们在表中不存在。

考虑我有像下面这样的表格：

my_table

+---------------+
| id  |  name   |
+---------------+
| 1   |  ABC    |
+---------------+
| 2   |  XYZ    |
+---------------+
| 3   |  PQR    |
+---------------+
| 4   |  LMN    |
+---------------+

假设我有以下查询:

select * from my_table where id in (1,2,3,4,5)

通过更改查询，是否可能获得以下输出?

+---------------+
| id  |  name   |
+---------------+
| 1   |  ABC    |
+---------------+
| 2   |  XYZ    |
+---------------+
| 3   |  PQR    |
+---------------+
| 4   |  LMN    |
+---------------+
| 5   |  NULL   |
+---------------+

我尝试使用自身JOIN和其他条件，并且搜索了很多，但没有找到解决方法。

有人能建议一个解决方案吗？

admin 更改状态以发布 2023年5月24日
0

2 答案

0 Comments

你可以在不需要创建额外的表或不需要为每个需要搜索的值编写条件的情况下创建一个数字系列。 你可以使用一个变量rownum，初始化为0并将其增加1，以便通过使用“limit”轻松创建系列。 我使用INFORMATION_SCHEMA.COLUMNS表，因此您可以创建一个大的系列（您可以使用数据库中的任何更大的表或任何足够大的表来满足您的需求）。

SQL Fiddle

MySQL 5.6.6 m9 Schema Setup：

CREATE TABLE my_table
    (`id` int, `name` varchar(3))
;
INSERT INTO my_table
    (`id`, `name`)
VALUES
    (1, 'ABC'),
    (2, 'XYZ'),
    (3, 'PQR'),
    (4, 'LMN')
;

Query 1：

select rownum id, name 
from (
select @rownum:=@rownum+1 as rownum
from INFORMATION_SCHEMA.COLUMNS,(SELECT @rownum:=0) r limit 5) as num
left outer join my_table on id = rownum

结果：

| ID |   NAME |
|----|--------|
|  1 |    ABC |
|  2 |    XYZ |
|  3 |    PQR |
|  4 |    LMN |
|  5 | (null) |

0
0 Comments

不幸的是，mysql没有内置函数来生成一系列数值（像其他数据库一样）。有（至少）两种方法可以实现：

将所需数值硬编码为子查询，然后左连接到您的表中：

select x.id, t.name
from (select 1 id
  union select 2
  union select 3
  union select 4
  union select 5) x
left join my_table t on t.id = x.id

但是这很繁琐，很难编写和维护。

或者（正如我以前所做的那样）创建一个表（一次）并填充自然数（一次），用作代理系列生成器：

create table numbers (num int);
insert into numbers values (1), (2), (3), ... etc

然后：

select n.num id, t.name
from numbers n
left join my_table t on t.id = n.num
where n.num in (1,2,3,4,5)

一旦设置并填充了大量数字，这种方法非常方便。

您可以创建一个类似的表，填充日期并使用类似的方法，这在生成某个日期范围内每个日期的图表时非常有用，即使不是每个日期都有数据。

0