SQL合并重复的行和不同的联接值 [英] SQL merge duplicate rows and join values that are different

问题描述

我在MySQL

所以相关的列是...

So relevant columns are...

FilmID

FilmName

DateShown

日期存储为Unix timestamps.

我目前有多个在不同日期上映的电影实例，但所有其他信息都是相同的. 因此，我想合并rows，其中FilmName相同但DateShown不同，然后将DateShown字段合并到一个逗号分隔的列表中.

I currently have multiple instances of films that were shown on different dates yet all other information is the same. So I want to merge rows where the FilmName is the same but the DateShown is different, and join the DateShown fields into a comma separated list.

我已经搜索了，但是很难找到这个确切的查询.有可能吗?

I've searched but struggled to find this exact query. Is it possible?

在数据库设计方面，将时间戳存储为逗号分隔的列表也是不好的形式吗?

Also is it bad form in terms of database design to store the timestamps as a comma separated list?

谢谢

推荐答案

使用逗号分隔列表的错误做法.您可以阅读有关数据库的文档归一化.

Bad practice to use comma separated lists. You can read documentation about database normalization.

以逗号分隔的列表有很多实际问题:

• 不能确保每个值都是正确的数据类型:无法 防止1,2,3，banana，5
• 不能使用外键约束将值链接到查找表； 无法强制执行参照完整性.
• 无法强制唯一性:无法阻止1,2,3,3,3,5
• 不能在不获取整个列表的情况下从列表中删除值.
• 存储列表的时间不能超过字符串列的容纳时间.
• 很难在列表中搜索具有给定值的所有实体；你 必须使用效率低下的表格扫描.可能不得不求助于常规 表达式，例如在MySQL中:idlist REGEXP '[[:<:]]2[[:>:]]'
• 很难计算列表中的元素，或者执行其他汇总查询.
• 很难将值连接到它们引用的查找表中.
• 很难按排序顺序获取列表.
• 将整数存储为字符串大约需要两倍的空间 存储二进制整数.更不用说逗号占用的空间了 字符.

• Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
• Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
• Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5
• Can’t delete a value from the list without fetching the whole list.
• Can't store a list longer than what fits in the string column.
• Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan. May have to resort to regular expressions, for example in MySQL: idlist REGEXP '[[:<:]]2[[:>:]]'
• Hard to count elements in the list, or do other aggregate queries.
• Hard to join the values to the lookup table they reference.
• Hard to fetch the list in sorted order.
• Storing integers as strings takes about twice as much space as storing binary integers. Not to mention the space taken by the comma characters.

但是，如果在任何情况下都需要它，则可以使用类似的内容:

But If you need It for any case, you could use something like that:

SELECT  FilmID,
        FilmName, 
        Id = REPLACE(
                (
                SELECT DateShown AS [data()]
                FROM YourTable
                WHERE FilmID = a.FilmID
                ORDER BY FilmName FOR XML PATH('')), ' ', ','
                )
FROM    YourTable a
WHERE   FilmName IS NOT NULL
GROUP BY FilmID, FilmName

有关逗号分隔的详细信息列表

这篇关于SQL合并重复的行和不同的联接值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！