mysql - 根据不同的列选择唯一的 GROUP_CONCAT-ed 行

Question

给定的表格可以有以下行。

即对于给定的 filename，可以有两个唯一的 version_id(s)。

file_id    version_id      filename
1          OS_v1           abc.update
1          App_v1          abc.update
2          OS_v2           xyz.update
2          App_v2          xyz.update
3          OS_v1           abc(1).update
3          App_v1          abc(1).update

PRIMARY KEY (`version_id`, `filename`)

如何检测没有两个不同的文件名具有相同的 OS_App(版本)组合？

在给定的示例中，file_id=3 的行集是 file_id=1 的副本。

注意:为操作系统和应用程序版本定义单独的列很容易，但这需要进行大量我们不想经历的代码更改。

问题:是否有一个 SELECT 查询只返回 file_id = 1 和 file_id = 2 而忽略 file_id = 3 ?

到目前为止，我提出了这个查询，它选择了按 filename 分组的 version_id 的组合，但是第 2 行是第 1 行的副本

SELECT   DISTINCT(GROUP_CONCAT(version_id SEPARATOR '-')) ,
         filename
FROM     schema_name.table_name
GROUP BY filename;

返回:

concat_version patch_filename
OS_V1-APP_V1   xyz.update
OS_V2-APP_V2   abc(1).update
OS_V1-APP_V1   abc.update

Answer 1

Question: Is there a SELECT query which would return just file_id = 1 and file_id = 2 and omit file_id = 3

如果您使用的是 MySQL 8.0，则可以利用窗口函数 ROW_NUMBER() :

SELECT x.file_id, x.version_id, x.filename
FROM (
    SELECT t.*, ROW_NUMBER() OVER(PARTITION BY version_id ORDER BY file_id) rn
    FROM master_logs.system_patches t    
) x
WHERE x.rn = 1

内部查询为 version_id 组中的每条记录分配一个行号，按 file_id 排序，外部查询过滤行号为 1< 的记录.

---

对于早期版本的 MySQL，一种典型的解决方案是使用带有 NOT EXISTS 条件的相关子查询来过滤掉不需要的记录:

SELECT t.file_id, t.version_id, t.filename
FROM master_logs.system_patches t 
WHERE NOT EXISTS (
    SELECT 1 
    FROM master_logs.system_patches t1
    WHERE t1.version_id = t.version_id AND t1.file_id < t.file_id
)