mysql - SQL 动态列

Question

我有以下 SQL 语句。列wrappupcode 最多可以有四个值:

销售、服务、 session 、其他

运行 SQL 时，列包装代码显示为单个列。我想要解决的是根据该值动态创建一个新的包装代码。我们可能会添加或删除代码，否则我会要求该值。

这是基于 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

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

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 ) <> ''

Answer 1

基本语法是:

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;