mysql - 将具有不同值的行合并为一行

Question

我正在使用的查询

SELECT v.*,  if( up.upload_key = 'send', up.upload_value, 0 ) AS send,  
    if( up.upload_key = 'host', up.upload_value, 0 ) AS host, 
     if( up.upload_key = 'upload_id', up.upload_value, 0 ) AS upload_id 
FROM TableInit v JOIN TableName up ON up.video_id =  v.id_video;

查询的返回是这样的

| id_video | titulo | desc | send | host |    upload_id |
|----------|--------|------|------|------|--------------|
|        6 |  Title | Desc |    0 |  cnn |            0 |
|        6 |  Title | Desc |    0 |    0 |            0 |
|        6 |  Title | Desc |    0 |    0 | sHGN-tSNvJYs |

我需要的返回是这样的:

| id_video | titulo | desc | send | host |   upload_id |
|----------|--------|------|------|------|-------------|
|        6 |  Title | Desc |    0 |  cnn |sHGN-tSNvJYs |

如果我使用 GROUP BY，我得到的返回值只是第一行的值，而不是另一行的值

| id_video | titulo | desc | send | host | upload_id |
|----------|--------|------|------|------|-----------|
|        6 |  Title | Desc |    0 |  cnn |         0 |

SQLFiddle

Answer 1

这应该可以完成工作:

SELECT v.*,  if( up.upload_key = 'send', up.upload_value, 0 ) AS send,  
    MAX(if( up.upload_key = 'host', up.upload_value, null )) AS host, 
     MAX(if( up.upload_key = 'upload_id', up.upload_value, null )) AS upload_id 
FROM TableInit v JOIN TableName up ON up.video_id =  v.id_video GROUP BY id_video;

我刚刚添加了 MAX 函数以仅选择列 host 和 upload_id 的最大值。

返回结果:

+----------+--------+------+------+------+--------------+
| id_video | titulo | desc | send | host | upload_id    |
+----------+--------+------+------+------+--------------+
|        6 | Title  | Desc | 0    | cnn  | sHGN-tSNvJYs |
+----------+--------+------+------+------+--------------+