问题连接重复行 [英] Issue concatenating rows with duplicates

问题描述

我在尝试组合可以找到重复项的一行变量时遇到了一些问题.

I have run into some issues trying to combine a row of variables where dublicates can be found.

带有 ID 的计算机保存在计算机表中:

Computers with Ids are saved in the Computer table:

| Computer.Id |
|-------------|
| 1           |
| 2           |
| 3           |
| 4           |
| 5           |
| 6           |
| 7           |
| 8           |
| 9           |

Harddrive 保存在 HardDisk 表中，HardDisk Id 是硬盘专用的，ComputerId 链接到 Computer 表中的 Id

Harddrive are saved in a HardDisk table with a HardDisk Id exclusive to the harddrive and a ComputerId linked to the Id in the Computer table

| Harddisk.ComputerId | Harddisk.Id |
|---------------------|-------------|
| 1                   | 1           |
| 2                   | 2           |
| 3                   | 3           |
| 4                   | 4           |
| 5                   | 5           |
| 6                   | 6           |
| 6                   | 7           |
| 7                   | 8           |
| 8                   | 9           |
| 9                   | 10          |

我希望实现的输出是:

| Harddisk.ComputerId | Harddisk.Id |
|---------------------|-------------|
| 1                   | 1           |
| 2                   | 2           |
| 3                   | 3           |
| 4                   | 4           |
| 5                   | 5           |
| 6                   | 6,7         |
| 7                   | 8           |
| 8                   | 9           |
| 9                   | 10          |

我目前得到的输出是:

| Harddisk.ComputerId | Harddisk.Id |
|---------------------|-------------|
| 1                   | 1           |
| 2                   | 2           |
| 3                   | 3           |
| 4                   | 4           |
| 5                   | 5           |
| 6                   | 6           |
| 7                   | 8           |
| 8                   | 9           |
| 9                   | 10          |

注意共享计算机 6 的硬盘 7 是如何消失的.

Notice how Harddisk 7 which is the disk that shares Computer 6 is gone.

我当前的查询如下所示，由 scaisEdge 提供:

My current query looks like the following, courtesy of scaisEdge:

SELECT *, group_concat(HardDisk.Id)
from Computer
inner join HardDisk on Computer.Id = HardDisk.ComputerId
group by Computer.Id

我希望有人能够帮助我！

I hope someone is able to help me out!

推荐答案

你不能使用 * 因为这会在 mysql 中产生一个错误的聚合版本 <5.7

You can't use * because this produce an a wrong aggregation in mysql for version < 5.7

尝试在选择中使用显式列的名称

try use explicit column's name in select

SELECT computer.ID, group_concat(HardDisk.Id) my_disk
from Computer
inner join HardDisk on Computer.Id = HardDisk.ComputerId
group by Computer.Id 

如果您需要更多与相同聚合级别无关的列，则需要加入

if you need more column's not related to the same aggreagtion level you need a join

在mysql版本中<5.7 如果 select 子句中提到的某些列在 group 中没有被聚合函数正确提到，则返回 select 的第一次出现，而不是正确的聚合结果

In mysql version < 5.7 if some columns mentioned in select clause are not mentioned properly in group by the aggregation function return the first occurrence of the select and not the correct aggreagted result

尝试添加

 echo  $row['my_disk];

这篇关于问题连接重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！