试试这个。我在mssql中测试它。我希望它在mysql中也能工作

SELECT id,child FROM
(SELECT t1.Related id,t1.id child,row_number() over (partition by t1.Related ORDER BY t1.Id) sl FROM Table1 t1
INNER JOIN Table1 t2 ON t1.Related=t2.Id) t
where t.sl<=2

或

SET @row_number:=0;
SET @Related:=0;
SET @Child:=0;
SELECT t.id,t.Child FROM 
(SELECT @row_number:=CASE WHEN @Related=t1.Related THEN @row_number+1 ELSE 1 END AS row_nu, @Related:=t1.Related AS id,@Child:=t1.id AS Child
FROM Table1 t1
INNER JOIN Table1 t2 ON t1.Related=t2.Id) t
WHERE t.row_nu<=2;

提琴

您想选择所有记录 Related = 0 . 然后您要选择最多两个随机的子对象。一种解决方案是取最小子id和最大子id。您可以在子查询中查找父项。

select
  related as parent_id, 
  min(id) as child1_id,
  max(id) as child2_id
from mytable
where related in (select id from mytable where related = 0)
group by related;

如果您不想两次显示同一个孩子的id（如果只有一个孩子，即。 min(id) = max(id) )，将此更改为：

select
  related as parent_id, 
  min(id) as child1_id,
  nullif(max(id), min(id)) as child2_id
...

这些查询不显示 related = 0 没有孩子的记录，但是，没有孩子，他们也不是父母