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

提问者：小点点

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

我使用的是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_id是引用其他表中记录的外键（分别为substances.id和display.id)。

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

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

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

foo bar:因为有两行substance_id和display_id匹配（两者分别为54和139）。
baz:只有1行，其中substance_id=1933且display_id=139。
xyz:逻辑与上面相同，只有1行，其中substance_id=38，display_id=27。

逻辑上，我的PHP脚本是这样工作的:

所有唯一的substance_id将加载到数组中。相当于SQL从generated_text_tbl中选择DISTINCT(substance_id)。

所有唯一的display_id都加载到数组中。相当于SQL从generated_text_tbl中选择DISTINCT(display_id)。

通过（1）循环，内部循环在（2）上。这有效地循环了每个substance_id和display_id组合，即:

substance_id=54和display_id=139(2行）
substance_id=54和display_id=27(0行）
substance_id=1933和display_id=139(1行）
substance_id=1933和display_id=27(0行）
substance_id=38和display_id=139(0行）
substance_id=38和display_id=27(1行）

然后，它根据上面的条件获得一个generated_text数组。如果数组大小大于1个元素，则在每个元素之间添加字符。如果只有1个元素，则不需要字符，因为只有1行generated_text，所以不需要分解任何内容。

 // Store the output text
 $output = '';

 // e.g. $result contains MySQL rows WHERE substance_id = 54 AND display_id = 139
 if (sizeof($result) == 1) {
     $output = $result['generated_text'];
 } else {
 foreach ($result as $key => $value) {
     $output .= $value['generated_text'] . "<br>";
 }
     $output = (substr($output), 0, -4); // Remove trailing <br>
 }

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

虽然这在逻辑上是可行的，但效率很低（当generated_text表中有数千行时）。对此有纯SQL解决方案吗？

共1个答案

匿名用户

你想要那样的东西不是吗？

SQL小提琴

查询1:

SELECT 
  `substance_id` , 
  `display_id`, 
  GROUP_CONCAT( `generated_text` SEPARATOR '<br>' ) as concact_text
from 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_text
from 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 |

正式文档:https://dev.mysql.com/doc/refman/8.0/en/aggrege-functions.html#function_group-concat