SQL左联接给出重复的结果

您可以将GROUP_CONCAT与GROUP BY一起使用。
可以在带有LIMIT的子查询中完成第一个group_concat。
为了避免这两个一对多关系之间的笛卡尔联接效应。

例如：

SELECT 
 q.*,  
 group_concat(d.d_confidence) as d_confidence_list
FROM
(
    SELECT i.id, group_concat(c.c_confidence) as c_confidence_list
    FROM images i
    LEFT JOIN image_results_classification c ON (c.image_id = i.id)
    GROUP BY i.id
    LIMIT 10
) q
LEFT JOIN image_results_detection d ON (d.image_id = q.id)
GROUP BY q.id, q.c_confidence_list

或者您可以按值使用DISTINCT，而无需子查询

SELECT 
 i.id, 
 group_concat(distinct c.c_confidence) as c_confidence_list,
 group_concat(distinct d.d_confidence) as d_confidence_list
FROM images i
LEFT JOIN image_results_classification c ON (c.image_id = i.id)
LEFT JOIN image_results_detection d ON (d.image_id = i.id)
GROUP BY i.id
LIMIT 10

但是，如果对这些联接表有很多信心，第一种方法可能会更快。

额外

这里还有2个要尝试的查询。

第一个应该得到预期的结果。
使用CTE，LIMIT仅执行一次。

with TOPIMG as (
  select * from images LIMIT 10
)
select image_id, c_confidence, null as d_confidence
from TOPIMG i
join image_results_classification c on c.image_id = i.id
union all
select image_id, null as c_confidence, d_confidence
from TOPIMG i
join image_results_detection d on d.image_id = i.id
order by image_id;

并且此查询使用技巧来以回旋方式模仿PARTITION的ROW_NUMBER函数。（我不喜欢它，它会降低性能）

with TOPIMG as (
  select * from images LIMIT 10
)
select 
image_id, 
max(case when src = 'c' then conf end) as c_conf,
max(case when src = 'd' then conf end) as d_conf
from 
(
  select image_id, 'c' as src, c_confidence as conf,
  (
    select count(*) 
    from image_results_classification c2 
    where c.image_id = c2.image_id and c.c_confidence >= c2.c_confidence
  ) as RN
  from TOPIMG i
  join image_results_classification c on (c.image_id = i.id)

  union all

  select image_id, 'd', d_confidence,
  (
    select count(*) 
    from image_results_detection d2 
    where d.image_id = d2.image_id and d.d_confidence >= d2.d_confidence
  ) as RN
  from TOPIMG i
  join image_results_detection d on (d.image_id = i.id)
) cd
group by image_id, RN
order by image_id, RN;

更新

实施特殊调味料c_confidence > 0.5要求：

with IMG as (
  select i.id as image_id, 
  max(case when c.image_id is not null then 1 else 0 end) as show_all
  from images i
  left join image_results_classification c on (c.image_id = i.id and c.c_confidence > 0.5)
  group by i.id
  order by i.id
  LIMIT 100
)
select c.image_id, 'c' as result_type, c.c_confidence as confidence
from IMG i
join image_results_classification c on c.image_id = i.image_id
where i.show_all = 1

union all

select d.image_id, 'd' as result_type, d.d_confidence as confidence
from IMG i
join image_results_detection d on d.image_id = i.image_id
where i.show_all = 1

union all

select i.image_id, null, null
from IMG i
where i.show_all = 0

order by image_id;