mysql - SQL动态列

我有下面的SQL语句。列wrapupcode最多可以包含四个值：

销售，服务，会议，其他

运行SQL时，列wrapupcode显示为单列。我要解决的是基于该值动态创建新的wrapupcode。我们可能会添加或删除代码，否则我会考虑该值。

这是基于MySQL数据库的。



当前数据如下：

user  wrapupcode  intialtime  endofwrapup  timediff
joe   Service     11:38       11:39        1
jenny Sales       11:35       11:36        1
joe   Service     11:41       11:42        1

但是想看看是否有可能通过MySQL数据库上的SQL：

user  Service  Sales     timediff
joe   2                  2
jenny          1         1

我可以对时间和总数进行求和/平均，只需在每个不同的wrapupcode上添加一个新列即可。

SELECT
     ( SELECT loginid FROM `axpuser` WHERE age.userid = axpuser.pkey ) as user,
     ( SELECT name FROM `breakcode` WHERE age.wrapupcode = pkey ) as wrapupcode,
     time(age.`instime`) as initialtime,
     age.`ENDOFWRAPUPTIME` AS endofwrapup,
     timediff(age.`ENDOFWRAPUPTIME`, time(age.`instime`)) as timediff
  FROM
     agentcallinformation age
  WHERE
     age.endofwrapuptime IS NOT null and ( SELECT name FROM `breakcode` WHERE age.wrapupcode =   pkey ) <> ''

最佳答案

基本语法为：

select user,
    sum(case when wrapupcode = 'Service' then 1 else 0 end) Service,
    sum(case when wrapupcode = 'Sales' then 1 else 0 end) Sales,
    sum(case when wrapupcode = 'Meeting' then 1 else 0 end) Meeting,
    sum(case when wrapupcode = 'Other' then 1 else 0 end) Other,
    count(timediff) timediff
from
(
    <yourquery>
) src
group by user

硬编码的静态版本将类似于以下内容：

select user,
    sum(case when wrapupcode = 'Service' then 1 else 0 end) Service,
    sum(case when wrapupcode = 'Sales' then 1 else 0 end) Sales,
    sum(case when wrapupcode = 'Meeting' then 1 else 0 end) Meeting,
    sum(case when wrapupcode = 'Other' then 1 else 0 end) Other,
    count(timediff) timediff
from
(
    select u.loginid as user,
        b.name wrapupcode,
        time(age.`instime`) as initialtime,
        age.`ENDOFWRAPUPTIME` AS endofwrapup,
        count(timediff(age.`ENDOFWRAPUPTIME`,   time(age.`instime`))) as timediff
    from agentcallinformation age
    left join `axpuser` u
        on age.userid = u.pkey
    left join `breakcode` b
        on age.wrapupcode = b.pkey
        and age.wrapupcode <> ''
    WHERE age.endofwrapuptime IS NOT null
) src
group by user

我将查询更改为使用JOIN语法而不是相关的子查询。

如果需要动态版本，则可以使用准备好的语句：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when wrapupcode = ''',
      name,
      ''' then 1 else 0 end) AS ',
      name
    )
  ) INTO @sql
FROM breakcode;

SET @sql = CONCAT('SELECT user, ', @sql, '
                    , count(timediff) timediff
                  from
                  (
                    select u.loginid as user,
                        b.name wrapupcode,
                        time(age.`instime`) as initialtime,
                        age.`ENDOFWRAPUPTIME` AS endofwrapup,
                        count(timediff(age.`ENDOFWRAPUPTIME`,   time(age.`instime`))) as timediff
                    from agentcallinformation age
                    left join `axpuser` u
                        on age.userid = u.pkey
                    left join `breakcode` b
                        on age.wrapupcode = b.pkey
                        and age.wrapupcode <> ''
                    WHERE age.endofwrapuptime IS NOT null
                ) src
                GROUP BY user');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;