mysql - "Faking"SQL 中 3 个表的结果子表

Question

如果我有一个案例表:

CASE_NUMBER | CASE_ID | STATUS          | SUBJECT              | 
----------------------------------------------------------------
3108        | 123456  | Closed_Billable | Something Interesting
3109        | 325124  | Closed_Billable | Broken printer
3110        | 432432  | Open_Assigned   | Email not working

还有调用表:

PARENT_ID | STATUS    | DUR(H) | DUR(M) | SUBJECT
---------------------------------------------------------------
123456    | Held      | 1      | 30     | Initial discussion
123456    | Cancelled | 0      | 0      | Walk user through
123456    | Held      | 0      | 45     | Remote debug session
325124    | Held      | 1      | 0      | Consultation
325124    | Held      | 1      | 15     | Needs assessment
432432    | Held      | 1      | 30     | Support call

还有一张 session table :

PARENT_ID | STATUS    | DUR(H) | DUR(M) | SUBJECT
-------------------------------------------------------
123456    | Held      | 3      | 15     | On-site work
325124    | Held      | 2      | 0      | Un-jam printer
432432    | Held      | 1      | 0      | Reconnect network

我如何使用这些参数进行选择(显然这不是工作代码):

SELECT cases.case_number, cases.subject, calls.subject, meetings.subject
WHERE  cases.status="Closed_Billable" AND (calls.status="Held" OR meetings.status="Held)
LEFT JOIN cases
ON cases.case_id = calls.parent_id
LEFT JOIN cases
ON cases.case_id = meetings.parent_id

最后得到一个“伪造的”嵌套表格，例如:

CASE_NUMBER | CASE SUBJECT          | # CALLS | # MEETINGS | CALL SUBJECT         | MEETING SUBJECT | DURATION (H) | DURATION (M) | TOTAL
-----------------------------------------------------------------------------------------------------------------------------------------
3108        | Something Interesting | 2       | 1          |                      |                 |              |              | 5.5H
            |                       |         |            | Initial Discussion   |                 | 1            | 30           |
            |                       |         |            | Remote Debug Session |                 | 0            | 45           |
            |                       |         |            |                      | On-site work    | 3            | 15           |
3109        | Broken printer        | 2       | 1          |                      |                 |              |              | 4.25H
            |                       |         |            | Consultation         |                 | 1            | 0            | 
            |                       |         |            | Needs assessment     |                 | 1            | 15           |
            |                       |         |            |                      | Un-jam printer  | 2            | 0            |

我已经尽我所能尝试了连接和子查询，但我得到了重复的条目 - 例如，案例中的每个 session 将显示 3 次，在这种情况下每个调用一次。

我被难住了!显然还有其他领域我在这里拉，做电话和 session 的计数，以及它们持续时间的总和，但我很乐意只显示这样的表/子表。

有可能吗？

谢谢，大卫。

Answer 1

以您想要的确切格式组合查询结果是..有点痛苦。可以做到，但最好将类似的演示内容留给应用程序。

也就是说，这会做你想做的事:

select case when case_id > floor(case_id) then ''
            else case_number
       end case_number,
       coalesce(q1.c, '') calls,
       coalesce(q2.c, '') meetings,
       coalesce(calls.subject, '') `call subject`,
       coalesce(meetings.subject, '') `meeting subject`,
       case when calls.subject is not null then calls.dhour
            when meetings.subject is not null then meetings.dhour
            else ''
       end dhour,
       case when calls.subject is not null then calls.dmin
            when meetings.subject is not null then meetings.dmin
            else ''
       end dhour,
       coalesce(q3.total, '') total
from
(
 select case_number, case_id
   from cases where status = 'Closed_Billable'
 union select case_number, concat(case_id, '.1')
   from cases where status = 'Closed_Billable'
 union select case_number, concat(case_id, '.2')
   from cases where status = 'Closed_Billable'
) main
left join
(select parent_id, count(*) c
         from calls
         where status != 'Cancelled'
         group by parent_id ) q1
  on q1.parent_id = case_id
 left join
      (select parent_id, count(*) c
         from meetings
         group by parent_id) q2
      on q2.parent_id = case_id
    left join
      (select parent_id, sum(dhour + m) total
         from
           (select parent_id, dhour, dmin / 60 m
              from calls
              where status != 'Cancelled'
            union all
            select parent_id, dhour, dmin / 60 m
              from meetings
           ) qq
         group by parent_id
       ) q3
      on q3.parent_id = case_id
left join calls
  on concat(calls.parent_id, '.1') = main.case_id
left join meetings
  on concat(meetings.parent_id, '.2') = main.case_id
order by case_id asc

请注意，我已重命名您的持续时间字段，因为我不喜欢其中的括号。

我们必须在查询中对 case_id 进行一些改动，以便能够为您提供空白行/字段 - 这些都是使查询变得麻烦的原因

这里有一个演示:http://sqlfiddle.com/#!9/d59d4/21

编辑代码以在评论 fiddle 中使用不同的架构

select case when case_id > floor(case_id) then ''
            else case_number
       end case_number,
       coalesce(q1.c, '') calls,
       coalesce(q2.c, '') meetings,
       coalesce(calls.name, '') `call subject`,
       coalesce(meetings.name, '') `meeting subject`,
       case when calls.name is not null then calls.duration_hours
            when meetings.name is not null then meetings.duration_hours
            else ''
       end duration_hours,
       case when calls.name is not null then calls.duration_minutes
            when meetings.name is not null then meetings.duration_minutes
            else ''
       end duration_hours,
       coalesce(q3.total, '') total
from
(
 select case_number, id as case_id
   from cases where status = 'Closed_Billable'
 union select case_number, concat(id, '.1') as case_id
   from cases where status = 'Closed_Billable'
 union select case_number, concat(id, '.2') as case_id
   from cases where status = 'Closed_Billable'
) main
left join
(select parent_id, count(*) c
         from calls
         where status != 'Cancelled'
         group by parent_id ) q1
  on q1.parent_id = case_id
 left join
      (select parent_id, count(*) c
         from meetings
         group by parent_id) q2
      on q2.parent_id = case_id
    left join
      (select parent_id, sum(duration_hours + m) total
         from
           (select parent_id, duration_hours, duration_minutes / 60 m
              from calls
              where status != 'Cancelled'
            union all
            select parent_id, duration_hours, duration_minutes / 60 m
              from meetings
           ) qq
         group by parent_id
       ) q3
      on q3.parent_id = case_id
left join calls
  on concat(calls.parent_id, '.1') = main.case_id
left join meetings
  on concat(meetings.parent_id, '.2') = main.case_id
order by case_id asc