前端之家

  1. 首页
  2. 问答

如何连接SQL中乱序的字符串碎片消息

• 问答

我有一个包含三列的表，第一列表示一条消息ID（message_id），第二列代表一个序数特征，该顺序特征表明了消息的顺序（message_order），最后第三列是消息的片段（message_fragment ）：

+------------+---------------+------------------------------+
| message_id | message_order |           message            |
+------------+---------------+------------------------------+
| Message 1  |             2 | Best,Jose                   |
| Message 1  |             1 | Thanks for your advice       |
| Message 2  |             1 | I only have one line of text |
+------------+---------------+------------------------------+

SQL中是否有一种方法可以按消息顺序将消息行按消息连接起来？最终获得以下信息：

+------------+-----------------------------------+
| message_id |              message              |
+------------+-----------------------------------+
| Message 1  | Thanks for your advice Best,Jose |
| Message 2  | I only have one line of text      |
+------------+-----------------------------------+
chenhui1646 回答：如何连接SQL中乱序的字符串碎片消息

对于Oracle：

with msg(msg_id,msg_order,msg_text) as (
  select 1,2,'Best,Jose' from dual union all
  select 1,1,'Thanks for your advice' from dual union all
  select 2,'I only have one line of text' from dual
)
select msg_id,listagg(msg_text,' ') within group (order by msg_id,msg_order) message
from msg
group by msg_id;
,

对于Hive：

select message_id,concat_ws(' ',collect_list(message)) as message
  from
      (select message_id,message_order,message 
         from table
       distribute by message_id sort by message_order 
      )s  
 group by message_id
 order by message_id;
hivehiveqlsqlstring-aggregation
本文链接：https://www.f2er.com/3136859.html

大家都在问