MySQL-动态数据透视表分组问题 [英] MySQL - Dynamic Pivot Table Grouping Issue
问题描述
我正在尝试使用MySQL预处理语句创建动态数据透视表，该语句是我从有关MySQL数据透视表的众多其他问题中汇总而来的.但是我没有得到期望的输出.
I'm trying to create a dynamic pivot table using a MySQL Prepared Statement I've put together from the numerous other questions about MySQL pivot tables. But I'm not getting the output I'm expecting.
我的表格如下:
技能包含员工可以接受的培训的所有技能"
skills contains every "skill" that an employee could be trained on
| id | skill | expiry_date |
----+---------+-------------
| 1 | SkillA | 2016-07-01 |
| 2 | SkillB | 2016-06-01 |
| 3 | SkillC | 2016-04-01 |
employee_skills 包含员工接受技能培训的日期的日志
employee_skills contains a log of the date which an employee was trained on a skill
| id | employee_id | skill_id | date_trained |
----+-------------+----------+--------------
| 1 | 1000001 | 1 | 2016-05-01 |
| 2 | 1000002 | 1 | 2016-05-02 |
| 3 | 1000001 | 2 | 2016-05-03 |
| 4 | 1000002 | 3 | 2016-05-04 |
campaign_skills 包含已分配给广告系列"的技能.
campaign_skills contains skill which ahve been assigned to a "campaign".
| id | campaign_id | skill_id |
----+-------------+----------
| 1 | 1001 | 1 |
| 2 | 1001 | 3 |
| 3 | 1002 | 2 |
我还有一个表，其中包含员工详细信息(first_name，last_name等)，并以employee_id作为主键.
I also have a table containing employee details (first_name, last_name etc.) with employee_id as the Primary Key.
所需结果
这是我需要在视图中显示的内容:
This is what I need to display in my view:
| Employee | SkillA | SkillB | SkillC |
-------------+------------+-------------+-------------
| Person One | 2016-05-01 | 2016-05-03 | - |
| Person Two | 2016-05-02 | - | 2016-05-04 |
显示的日期应为最近 employee_skills.date_trained.
我还需要标记日期if ( employee_skills.date_trained < skills.expiry_date )的能力.但我可以稍后再弄清楚.
I will also need the ability to flag a date if ( employee_skills.date_trained < skills.expiry_date ). But I can figure that one out later.
我的查询
我已经编写了一个存储过程，该存储过程以campaign_id作为参数.我想我快到了，但没有得到我期望的结果...
I've written a stored procedure which takes campaign_id as a parameter. I think I'm nearly there but I'm not getting the result I'm expecting...
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'CASE WHEN skill = ''',
s.skill,
''' THEN es.date_trained ELSE 0 END AS ',
s.skill
)
) INTO @sql
FROM skills s
JOIN campaign_skills cs ON cs.skill_id = s.id
WHERE cs.campaign_id = campaignID;
SET @sql = CONCAT('
SELECT CONCAT(e.first_name, " ", e.last_name) AS employee, ', @sql, '
FROM employees e
LEFT JOIN employee_skills es ON es.employee_id = e.id
LEFT JOIN campaign_skills cs ON cs.skill_id = es.skill_id
LEFT JOIN skills s ON s.id = es.skill_id
WHERE e.id IN (SELECT id FROM employees WHERE campaign_id = campaignID)
AND e.active = 1
ORDER BY es.id DESC
GROUP BY e.id, s.id
');
PREPARE statement FROM @sql;
EXECUTE statement;
DEALLOCATE PREPARE statement;
这将输出如下内容:
| employee | SkillA | SkillB |
------------+------------+------------
| Person One | 2015-05-04 | - |
| Person One | - | 2016-05-01 |
| Person Two | - | 2016-03-25 |
| Person Two | 2016-04-04 | - |
即使我使用的是GROUP.我已经坚持了一段时间，所以也许有人可以告诉我我的错误?
Even though I'm using a GROUP. I've been stuck on this for a while so maybe someone can tell me my mistake?
SQL字段: http://sqlfiddle.com/#!9/7caa6c/1
推荐答案
以下SQL可以作为解决该问题的起点:
The following SQL can be a starting point for solving the problem:
SELECT
es.employee_id,
CONCAT(e.first_name, " ", e.last_name) AS employee,
MAX(IF (es.skill_id = 1, es.date_trained, null)) AS '1',
MAX(IF (es.skill_id = 2, es.date_trained, null)) AS '2',
MAX(IF (es.skill_id = 3, es.date_trained, null)) AS '3'
FROM
employee_skills es
LEFT JOIN employees e ON es.employee_id = e.id
GROUP BY
es.employee_id
结果是这样的数据透视表:
Result is a pivot table like this:
| employee_id | employee | 1 | 2 | 3 |
+-------------+------------+------------+------------+------------+
| 1001675 | Person Two | (null) | 2016-07-02 | 2016-07-04 |
| 1006111 | Person One | 2016-07-01 | 2016-07-11 | (null) |
如果动态创建SQL，则可以用技能名称替换技能ID.也可以在以后替换ID.
If SQL is created dynamically the skill IDs can be replaced by the skill name. Also it is possible to replace the IDs afterwards.
这篇关于MySQL-动态数据透视表分组问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
