我的应用程序使用带有关系表的表,我用SQL将具有类别,演员和标签GROUP_CONCAT的视频与DISTINCT连接起来。
我面临的问题是我也将为演员输出大拇指,但是有时演员还没有缩略图。在这种情况下,我在group_concat的行中生成的值与actor字段不一致,因为3个actor是唯一的,但是如果我有2个actor没有图像(空),则会丢失锚点。
例如
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'
演员拇指字段结果:
more fields.. |0;http://site.com/actor/59.jpg
但是应该是(3个演员,2个没有缩略图):
more fields.. |0;0;http://site.com/actor/59.jpg
使值与演员名称保持一致。
希望这有点清楚。
提前致谢!
缺口
最佳答案
最好一次获得演员姓名和拇指...
GROUP_CONCAT(DISTINCT CONCAT(a.actor_name,'^',a.actor_thumb) SEPARATOR ';') AS actor_names
关于mysql - 不同不会消除无效结果,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18960792/