mysql - 连接记录 [SQL]

Question

我有一个表“Table”

id_document , merge , merge_order ,name

相同的记录合并，需要按下merge_order 是特定顺序(在一次合并中)

等等

ID123456 , 400 , 1 , Coral
ID654321 , 200 , 2 , Deril
ID654322 , 400 , 2 , Rick
ID654323 , 200 , 1 , Maggie

select 中的输出将是，例如:

ID654323 , 200 , 1 , Maggie
ID654321 , 200 , 2 , Deril
ID123456 , 400 , 1 , Coral
ID654322 , 400 , 2 , Rick

这是我的选择

SELECT
   MERGE_ORDER,ID_DOCUMENT,NAME,MERGE 
FROM(select * from Table order by name)-trying to order table before main select  
WHERE MERGE IS NOT NULL                                           
CONNECT BY NOCYCLE PRIOR MERGE = MERGE 
order SIBLINGS by MERGE_ORDER,NAME)

选择有效，但现在我需要按名称对结果进行排序，但保存记录连接(合并)。

我需要对 merge_order = 1 的行进行排序。但在有序行下，放置具有相同合并的第二条记录，其中 merge_order = 2。

ID654323 , 200 , 1 , Maggie
ID123456 , 400 , 1 , Coral

Answer 1

我会通过一个简单的自连接来做到这一点:

select t1.*
from table t1
inner join table t2 on t1.merge=t2.merge  --self join
where t2.merge_order=1   --relate the 1st of each merge to each record in the merge
order by t2.name ASC, t1.merge_order ASC   --the name in t2 will be the name from the 1st record, see where clause

您也许也可以使用 connect by 来做到这一点，但是，您还没有更新您的产品标签，并且 mysql 没有 connect by 子句。因此，我不认为 connect by 子句是有效的解决方案。