mysql - 根据其他表中存在的文本值选择行

Question

我已尽力简化。给定两个表:

CREATE TABLE house ( `id` INTEGER  NOT NULL, `nb` INTEGER  NOT NULL );

CREATE TABLE room  ( `id` INTEGER  NOT NULL, `body` TEXT );

包含如下数据:

INSERT INTO house VALUES (1, 111);
INSERT INTO house VALUES (2, 222);
INSERT INTO house VALUES (3, 333);
INSERT INTO house VALUES (4, 444);

INSERT INTO room VALUES (1, 'lorem ipsum House NR: 111 dolor sit ames');
INSERT INTO room VALUES (2, 'lorem m House NR: 777 dolor sit ames');
INSERT INTO room VALUES (3, 'lopsu House NR: 32154 dolor sit ames');
INSERT INTO room VALUES (4, 'ipsa House NR: 222 dolor sit ames');
INSERT INTO room VALUES (5, 'ipsa House NR: 222222 dolor sit ames');

我需要是根据house nb获取house和room的所有数据> 也存在于 room body 中，作为 House NR: [NR] 中所有其余字符串/文本之后的字符串House NR: 之前的 house body 和 [NR] 之后可能会有所不同。

我试过以下方法:

SELECT * FROM room WHERE body LIKE '%House NR: 111%';

工作正常。然后我尝试使用 house nb 使 LIKE '%House NR: 111%' 动态化，如下所示:

SELECT *, concat('\'%House NR:', nb, '%\'') as value FROM house;

值正确。但如何将所有值(value)观整合在一起呢？我试过:

SELECT id, nb
FROM house h,
    (
        SELECT 
            body
        FROM room
    ) AS r
WHERE  r.body LIKE concat('\'%House NR:', h.nb, '%\'')

但不起作用。我做错了什么？

预期结果是两行

+----------+----------+---------+--------------------------------------------+
| house.id | house.nb | room.id | room.body                                  |
+----------+----------+---------+--------------------------------------------+
| 1        | 111      | 1       | 'lorem ipsum House NR: 111 dolor sit ames' |
+----------+----------+---------+--------------------------------------------+
| 2        | 222      | 4       | 'ipsa House NR: 222 dolor sit ames'        |
+----------+----------+---------+--------------------------------------------+

SQL Fiddle to play with here

Answer 1

我注意到的主要问题是您没有考虑 House NR: 和数字之间的空间。假设 House NR: 之后始终有一个空格，那么您可以像这样连接两个表:

SELECT house.*, room.*
from house 
inner join room on room.body like concat('%House NR: ', house.nb, ' %');