mysql - 不同并不能消除空结果

Question

我的应用程序使用带有关系表的表，我使用 SQL 中的 DISTINCT 将视频与类别、 Actor 和标签 GROUP_CONCAT 连接起来。

我面临的问题是我也会输出 Actor 的拇指，但有时 Actor 还没有缩略图。在这种情况下，我在 group_concat 行中生成的值与 Actor 字段不一致，因为 3 个 Actor 是唯一的，但如果我有 2 个 Actor 没有图像(空)，我就会丢失他们的 anchor 。

例如

SELECT
  videos.id AS id, 
  videos.video_title AS video_title,
  videos.video_views AS video_views, 
  videos.video_likes AS video_likes, 
  videos.video_dislikes AS video_dislikes, 
  videos.video_duration AS video_duration,
  GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ';') AS actor_names,
  GROUP_CONCAT(DISTINCT t.tag_name SEPARATOR ';') AS tag_names,
  GROUP_CONCAT(DISTINCT c.category_name SEPARATOR ';') AS category_names,
  GROUP_CONCAT(DISTINCT a.actor_thumb SEPARATOR ';') AS actor_thumbs

FROM videos 

LEFT OUTER JOIN video_actors  AS va ON va.video_id = videos.id
LEFT OUTER JOIN actor AS a ON a.actor_id = va.actor_id

LEFT OUTER JOIN video_tags AS vt ON vt.video_id = videos.id
LEFT OUTER JOIN tags AS t ON t.tag_id = vt.tag_id

LEFT OUTER JOIN video_categories AS vc ON vc.video_id = videos.id
LEFT OUTER JOIN categories AS c ON c.category_id = vc.category_id

WHERE videos.id = '23'

Actor 拇指字段结果:

more fields.. |0;http://site.com/actor/59.jpg

但应该是(3 个 Actor ，2 个没有缩略图):

more fields.. |0;0;http://site.com/actor/59.jpg

使值与 Actor 姓名保持一致。

希望这有点清楚。

提前致谢!尼克

Answer 1

最好立即获取 Actor 姓名和拇指......

GROUP_CONCAT(DISTINCT CONCAT(a.actor_name,'^',a.actor_thumb) SEPARATOR ';') AS actor_names