MySQL解决方案基于2个匹配的ID连接多行文本

Question

我正在使用 MySQL (5.5.65-MariaDB) 并有一个由表、generated_text_tbl. 以下是该表中的一些示例数据。

-----------------------------------------------

id | substance_id | display_id | generated_text   

-----------------------------------------------

1  | 54           | 139        | foo

-----------------------------------------------

2  | 54           | 139        | bar

----------------------------------------------

3  | 1933         | 139        | baz

-----------------------------------------------  

4  | 38           | 27         | xyz

-----------------------------------------------

id是一个AUTO_INCREMENT字段。和是外键，引用其他表中的记录（substance_id分别为和）。display_idsubstances.iddisplay.id

我想输出字段中的文本generated_text- 用 HTML 换行符 ( <br>) 分隔 - 如果存在匹配substance_id和display_id行。如果没有匹配项（即单独的行），则generated_text仍应输出，但不需要该<br>字符，因为没有任何内容可以分隔。

我有一个可以在 PHP 中运行的解决方案，但如果可能的话，我想要一个纯 SQL 解决方案。我已经写出了我的逻辑，因为这在技术上是可行的，尽管是在 PHP 中。

对于上面的示例数据，正确的输出如下：

• foo<br>bar：因为有 2 行substance_id和display_id匹配（分别是 54 和 139）。

• baz：只有 1 行WHERE substance_id = 1933 AND display_id = 139。

• xyz：与上面的逻辑相同，只有 1 行WHERE substance_id = 38 AND display_id = 27。

从逻辑上讲，我的 PHP 脚本是这样工作的：

1. 所有 uniquesubstance_id都被加载到一个数组中。相当于SQL SELECT DISTINCT(substance_id) FROM generated_text_tbl。

2. 所有 uniquedisplay_id都被加载到一个数组中。相当于SQL SELECT DISTINCT(display_id) FROM generated_text_tbl。

3. 循环 (1)，并在 (2) 上进行内循环。这有效地循环了每个substance_id和display_id组合，即：

• substance_id = 54 AND display_id = 139（2 行）

• substance_id = 54 AND display_id = 27（0 行）

• substance_id = 1933 AND display_id = 139（1 行）

• substance_id = 1933 AND display_id = 27（0 行）

• substance_id = 38 AND display_id = 139（0 行）

• substance_id = 38 AND display_id = 27（1 行）

4. generated_text然后它根据上述条件获得一个数组。如果数组大小大于 1 个元素，则<br>在每个元素之间添加一个字符。如果只有 1 个元素，则<br>不需要字符，因为只有 1 行，generated_text因此不需要分解任何字符。

   var_dump($output)上面脚本的结果是foo<br>bar，这就是我想要的。

尽管这在逻辑上可行，但效率很低（当表中有数千行时generated_text）。有没有一个纯 SQL 的解决方案？

Answer 1

你在追求这样的东西不是吗？查询1：SELECT   `substance_id` ,   `display_id`,   GROUP_CONCAT( `generated_text` SEPARATOR '<br>' ) as concact_textfrom generated_text_tbl group by   `substance_id` ,    `display_id`结果：| substance_id | display_id | concact_text ||--------------|------------|--------------||           38 |         27 |          xyz ||           54 |        139 |   foo<br>bar ||         1933 |        139 |          baz |查询2：SELECT   `substance_id` ,   `display_id`,   GROUP_CONCAT( `generated_text` ORDER BY id DESC SEPARATOR '<br>') as concact_reverse_textfrom generated_text_tbl group by   `substance_id` ,    `display_id`结果：| substance_id | display_id | concact_reverse_text ||--------------|------------|----------------------||           38 |         27 |                  xyz ||           54 |        139 |           bar<br>foo ||         1933 |        139 |                  baz |