mysql - 合并具有相同值的sql行

Question

我已将一些数据添加到我的数据库中，但我刚刚发现我有很多重复项，当然具有不同的键，我想将它们合并为一条记录。

我想在 sql 数据库本身中执行此操作，我不想截断表并再次插入值，不要重复，因为脚本非常慢。

这是我的场景示例:

表格轨道:

key |   artist  | title
----|-----------|--------
k1  |  artist1  | title1
----|-----------|--------
k2  |  artist1  | title1
----|-----------|--------
k3  |  artist1  | title1

图表:

trackKey | otherKey |  anotherKey  |  value
---------|----------|--------------|---------
k1       |   ok1    |      ak4     |    v1
---------|----------|--------------|---------
k3       |   ok2    |      ak2     |    v2
---------|----------|--------------|---------
k1       |   ok3    |      ak9     |    v2
---------|----------|--------------|---------
k2       |   ok4    |      ak1     |    v6

其中 chart.trackKey 引用 track.key

我想达到的结果是:

表格轨道:

key |   artist  | title
----|-----------|--------
k1  |  artist1  | title1

图表:

trackKey | otherKey |  anotherKey  |  value
---------|----------|--------------|---------
k1       |   ok1    |      ak4     |    v1
---------|----------|--------------|---------
k1       |   ok2    |      ak2     |    v2
---------|----------|--------------|---------
k1       |   ok3    |      ak9     |    v2
---------|----------|--------------|---------
k1       |   ok4    |      ak1     |    v6

以便 track 中相同条目的每个副本都合并为一行，并且 chart 中的旧键更新为 中唯一保留的键>跟踪表。

有什么方法可以在 SQL 中做到这一点吗？

编辑:

解决方案#1 基于@popovitsj 的回答

UPDATE chart c SET trackUri =
(WITH track_unique AS
(
    SELECT MIN(uri) AS key, artist, title, album. artwork FROM track
    GROUP BY artist, title
)
SELECT tu.key FROM chart c1
INNER JOIN track t ON c1.trackUri = t.key
INNER JOIN track_unique tu ON t.artist = tu.artist AND t.title = tu.title
WHERE c1.trackUri = c.trackUri and c1.countryId = c.countryId and c1.date = c.date);

返回

#1064 - Syntax error near 
'track_unique AS (
SELECT MIN(uri) AS key, artist, title, album. artwork FR' line 2 

解决方案#2 基于@juergen d 的回答

update chart
join track t1 on t1.uri = chart.trackUri
left join 
(
   select min(uri) as key
   from track 
   group by artist, title
) tmp_track on tmp_track.key = chart.trackUri
set trackkey = tmp_tbl.key
where chart.trackUri not in 
(
  select min(uri)
  from track
  group by artist, title
  having count(*) > 1
);

返回

#1064 - Syntax error near
   'key
   from track
   group by artist, title
) tmp_track on tmp_track.key = c' line 5 

我不知道我做错了什么所以我添加了模式定义(取自phpMyAdmin)

Answer 1

第一个 with 子句获取您要保留的 ID，然后在下一个选择查询中将这些 ID 与图表 ID 匹配。

我根据您对我原来答案的修改编辑了这个答案。此答案假定 chart(countryid,date) 唯一标识一个图表，并且只有当 track(key,artist,title,album) 相等时才可以合并轨道。

UPDATE chart c SET trackUri =
(WITH track_unique AS
(
    SELECT MIN(uri) AS key, artist, title, album, artwork FROM track
    GROUP BY artist, title, album, artwork
)
SELECT tu.key FROM chart c1
INNER JOIN track t ON c1.trackUri = t.key
INNER JOIN track_unique tu
ON t.artist = tu.artist
AND t.title = tu.title
AND t.album = tu.album
AND t.artwork = tu.artwork
WHERE c1.trackUri = c.trackUri
AND c1.countryId = c.countryId
AND c1.date = c.date);

执行此更新后删除剩余的重复项:

DELETE FROM track
WHERE uri NOT IN
    (SELECT MIN(uri) AS key, artist, title, album, artwork
     FROM track
     GROUP BY artist, title, album, artwork);