sql 查询一张表中相同orderid的两条记录 显示为一条内容为全部内容

Question

帮忙写条sql
2张表 根据commentid从comment表中查找orderid相同的数据，并显示关联user表的name显示为一条记录

结果集

Answer 1

select c.commentid,c.orderid,c.fromuserid,u1.name,u1.contet,u2.name,u2.content

from common c join user u
on c.fromuserid=u.userid
join user u1
on c.fromuserid=u1.userid
where exists (select 1 from comment m
where c.commentid<>m.commentid
and c.orderid=m.orderid)