用于组合行的复杂 SQL 查询

发布于 2025-01-14 18:48:18 字数 450 浏览 1 评论 0原文

我首先有 2 个表，还有

Thread { code, itr_global,campaign, contact, start_time,duration}
segment {code,thread,start_time,duration,state}

多个其他连接，但这两个是主要连接。 2个表的关系为thread.code=segment.thread。

在段表中，单线程将有多行。我需要获取值

活动开始时间持续时间waititme talk_timehold_timewrap_time

其中wait_time我可以得到segment.state=7& talke_time=segment.state=6 &换行时间为segment.state=8 我无法在单行中获取所有这些值，因为它将为每条记录提供 3 个不同的行。如何按照上述格式获取单行中的所有值。

原文

I have 2 tables first is

Thread { code, itr_global,campaign, contact, start_time,duration}
segment {code,thread,start_time,duration,state}

There are multiple other joins but these 2 are major joins. 2 table are realted as thread.code=segment.thread.

In segment table there will be multiple rows for singl thread. I need to get values

campaign start_time duration waititme talk_time hold_time wrap_time

Where wait_time I can get as segment.state=7 & talke_time=segment.state=6 & wrap time as segment.state=8
I am not able to get all these values in single row as it will give me 3 diffrent rows for each record. How can I get all the values in single row as per above format.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浅笑依然 2025-01-21 18:48:18

您需要一些条件聚合 SUM(CASE...) 才能获得您想要的结果。

所有这些都是基于我对表格结构和含义的猜测，您从问题中省略了这些猜测。

SELECT thread.code, thread.campaign,
       SUM(segment.duration) duration,
       SUM(CASE WHEN segment.state = 7 THEN segment.duration END) waittime,
       SUM(CASE WHEN segment.state = 6 THEN segment.duration END) talk_time,
       SUM(CASE WHEN segment.state = 8 THEN segment.duration END) wrap_time,
       42 hold_time, -- you didn't say how to get hold_time
       SUM(CASE WHEN segment.state = 8 THEN segment.duration END) wrap_time
  FROM Thread
  LEFT JOIN segment ON Thread.code = segment.thread
 GROUP BY  thread.code, thread.campaign

You need some conditional aggregation SUM(CASE...) to get the result you want.

All this is based on my guesses about the structure and meaning of your tables, which you omitted from your question.

SELECT thread.code, thread.campaign,
       SUM(segment.duration) duration,
       SUM(CASE WHEN segment.state = 7 THEN segment.duration END) waittime,
       SUM(CASE WHEN segment.state = 6 THEN segment.duration END) talk_time,
       SUM(CASE WHEN segment.state = 8 THEN segment.duration END) wrap_time,
       42 hold_time, -- you didn't say how to get hold_time
       SUM(CASE WHEN segment.state = 8 THEN segment.duration END) wrap_time
  FROM Thread
  LEFT JOIN segment ON Thread.code = segment.thread
 GROUP BY  thread.code, thread.campaign

回复收藏 0 原文

~没有更多了~