个人中心
gpt4 book ai didi

MySQL 将列转为行并转回列

转载 作者：可可西里 更新时间：2023-11-01 07:08:41 25 4
gpt4 key购买 nike

我已经查看了很多关于旋转的搜索，并且我进行了旋转的第一部分，但我很难弄清楚如何进行第二和第三旋转。我将在当前状态和一些解释之后显示最终目标表

我的数据是运行 HTML 片段时输出的内容。

<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=UTF-8">
</head>
<body bgcolor="#FFFFFF">
<table border="1">
<tr>
  <td>ID</td>
  <td>Title</td>
  <td>5ba.&nbsp;Initiation&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Planned</td>
  <td>5bb.&nbsp;Initiation&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Actual</td>
  <td>5bc.&nbsp;Initiation&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Planned</td>
  <td>5bd.&nbsp;Initiation&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Actual</td>
  <td>5ca.&nbsp;Preliminary&nbsp;Design&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Planned</td>
  <td>5cb.&nbsp;Preliminary&nbsp;Design&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Actual</td>
  <td>5cc.&nbsp;Preliminary&nbsp;Design&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Planned</td>
  <td>5cd.&nbsp;Preliminary&nbsp;Design&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Actual</td>
  <td>5da.&nbsp;Design&nbsp;and&nbsp;Development&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Planned</td>
  <td>5db.&nbsp;Design&nbsp;and&nbsp;Development&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Actual</td>
  <td>5dc.&nbsp;Design&nbsp;and&nbsp;Development&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Planned</td>
  <td>5dd.&nbsp;Design&nbsp;and&nbsp;Development&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Actual</td>
  <td>5ea.&nbsp;Qualification&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Planned</td>
  <td>5eb.&nbsp;Qualification&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Actual</td>
  <td>5ec.&nbsp;Qualification&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Planned</td>
  <td>5ed.&nbsp;Qualification&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Actual</td>
</tr>
<tr>
  <td>109581</td>
  <td>Proj 1</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>113075</td>
  <td>Proj 2</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>132510</td>
  <td>Proj 3</td>
  <td>2017-10-2</td>
  <td>2017-10-2</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
  <td>2017-10-16</td>
  <td>2017-12-29</td>
  <td>0000-0-0</td>
  <td>2018-3-1</td>
  <td>0000-0-0</td>
  <td>2018-8-31</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>132511</td>
  <td>Proj 4</td>
  <td>2017-10-2</td>
  <td>2017-10-12</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
  <td>2017-10-16</td>
  <td>2017-12-29</td>
  <td>2017-12-29</td>
  <td>2018-1-2</td>
  <td>2018-1-2</td>
  <td>2018-5-11</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>114171</td>
  <td>Proj 5</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>114200</td>
  <td>Proj 6</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
</table>
</body>
</html>

所以一开始我尝试只旋转列名，但这行不通。

表格的结束状态需要如下所示

<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=UTF-8">
</head>
<body bgcolor="#FFFFFF">
<table border="1">
<tr>
  <td>ID</td>
  <td>Title</td>
  <td>Phase</td>
  <td>Start&nbsp;Date&nbsp;Planned</td>
  <td>Start&nbsp;Date&nbsp;Actual</td>
  <td>End&nbsp;Date&nbsp;Planned</td>
  <td>End&nbsp;Date&nbsp;Actual</td>
</tr>
<tr>
  <td>109581</td>
  <td>Proj 1</td>
  <td>Initiation</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>109581</td>
  <td>Proj 1</td>
  <td>Prelimenary Design</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>109581</td>
  <td>Proj 1</td>
  <td>Design and Development</td>
  <td>2017-10-2</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
</tr>
<tr>
  <td>109581</td>
  <td>Proj 1</td>
  <td>Qualification Phase</td>
  <td>2017-10-12</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
</tr>
<tr>
  <td>123456</td>
  <td>Proj 2</td>
  <td>Initiation</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>123456</td>
  <td>Proj 2</td>
  <td>Prelimenary Design</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>123456</td>
  <td>Proj 2</td>
  <td>Design and Development</td>
  <td>2017-10-2</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
</tr>
<tr>
  <td>123456</td>
  <td>Proj 2</td>
  <td>Qualification Phase</td>
  <td>2017-10-12</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
</tr>
</table>
</body>
</html>

因此，当我执行第一个数据透视表时，我意识到我的格式仍然不是很有用，因为它只是使列成为行，而日期仍然不是所需的列。

这是第一个开始旋转的查询

    SELECT
    ID
    ,Title
    ,`5ba. Initiation Phase Start Date Planned` Date
    , 'Initiation Phase Start Date Planned' Phase
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5bb. Initiation Phase Start Date Actual`
    ,'Initiation Phase Start Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5bc. Initiation Phase End Date Planned`
    ,'Initiation Phase End Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5bd. Initiation Phase End Date Actual`
    ,'Initiation Phase End Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5ca. Preliminary Design Phase Start Date Planned`
    ,'Preliminary Design Phase Start Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5cb. Preliminary Design Phase Start Date Actual`
    ,'Preliminary Design Phase Start Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5cc. Preliminary Design Phase End Date Planned`
    ,'Preliminary Design Phase End Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5cd. Preliminary Design Phase End Date Actual`
    ,'Preliminary Design Phase End Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5da. Design and Development Phase Start Date Planned`
    ,'Design and Development Phase Start Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5db. Design and Development Phase Start Date Actual`
    ,'Design and Development Phase Start Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5dc. Design and Development Phase End Date Planned`
    ,'Design and Development Phase End Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5dd. Design and Development Phase End Date Actual`
    ,'Design and Development Phase End Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5ea. Qualification Phase Start Date Planned`
    ,'Qualification Phase Start Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5eb. Qualification Phase Start Date Actual`
    ,'Qualification Phase Start Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5ec. Qualification Phase End Date Planned`
    ,'Qualification Phase End Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5ed. Qualification Phase End Date Actual`
    ,'Qualification Phase End Date Actual' 
FROM RandD_Playbook

我希望得到一些关于这方面的指导，以及按照我被问到的方式整合表格的最佳方式。

最佳答案

您需要选择 Phase 作为常量和四个相应的列。每个阶段一个子查询，由 UNION ALL 组合:

SELECT ID, Title,
    'Initiation' as Phase,
    `5ba. Initiation Phase Start Date Planned` as `Start Date Planned`,
    `5bb. Initiation Phase Start Date Actual`  as `Start Date Actual`,
    `5bc. Initiation Phase End Date Planned`   as `End Date Planned`,
    `5bd. Initiation Phase End Date Actual`    as `End Date Actual`
FROM RandD_Playbook
UNION ALL
SELECT ID, Title,
   'Preliminary Design' as Phase,
    `5ca. Preliminary Design Phase Start Date Planned` as `Start Date Planned`,
    `5cb. Preliminary Design Phase Start Date Actual`  as `Start Date Actual`, 
    `5cc. Preliminary Design Phase End Date Planned`   as `End Date Planned`,  
    `5cd. Preliminary Design Phase End Date Actual`    as `End Date Actual`    
FROM RandD_Playbook
UNION ALL
SELECT ID, Title,
    'Design and Development' as Phase,
    `5da. Design and Development Phase Start Date Planned` as `Start Date Planned`,
    `5db. Design and Development Phase Start Date Actual`  as `Start Date Actual`,
    `5dc. Design and Development Phase End Date Planned`   as `End Date Planned`,
    `5dd. Design and Development Phase End Date Actual`    as `End Date Actual`
FROM RandD_Playbook
UNION ALL
SELECT ID, Title,
    'Qualification' as Phase,
    `5ea. Qualification Phase Start Date Planned` as `Start Date Planned`,
    `5eb. Qualification Phase Start Date Actual`  as `Start Date Actual`,
    `5ec. Qualification Phase End Date Planned`   as `End Date Planned`,
    `5ed. Qualification Phase End Date Actual`    as `End Date Actual`
FROM RandD_Playbook
ORDER BY Title

样本数据的结果:

|     ID |  Title |                  Phase | Start Date Planned | Start Date Actual | End Date Planned | End Date Actual |
|--------|--------|------------------------|--------------------|-------------------|------------------|-----------------|
| 109581 | Proj 1 |             Initiation |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 109581 | Proj 1 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 109581 | Proj 1 | Design and Development |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 109581 | Proj 1 |     Preliminary Design |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 113075 | Proj 2 | Design and Development |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 113075 | Proj 2 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 113075 | Proj 2 |     Preliminary Design |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 113075 | Proj 2 |             Initiation |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 132510 | Proj 3 | Design and Development |         2018-03-01 |        0000-00-00 |       2018-08-31 |      0000-00-00 |
| 132510 | Proj 3 |     Preliminary Design |         2017-10-16 |        2017-10-16 |       2017-12-29 |      0000-00-00 |
| 132510 | Proj 3 |             Initiation |         2017-10-02 |        2017-10-02 |       2017-10-13 |      2017-10-13 |
| 132510 | Proj 3 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 132511 | Proj 4 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 132511 | Proj 4 | Design and Development |         2018-01-02 |        2018-01-02 |       2018-05-11 |      0000-00-00 |
| 132511 | Proj 4 |             Initiation |         2017-10-02 |        2017-10-12 |       2017-10-13 |      2017-10-13 |
| 132511 | Proj 4 |     Preliminary Design |         2017-10-16 |        2017-10-16 |       2017-12-29 |      2017-12-29 |
| 114171 | Proj 5 |             Initiation |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114171 | Proj 5 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114171 | Proj 5 | Design and Development |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114171 | Proj 5 |     Preliminary Design |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114200 | Proj 6 |     Preliminary Design |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114200 | Proj 6 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114200 | Proj 6 | Design and Development |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114200 | Proj 6 |             Initiation |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |

演示:http://sqlfiddle.com/#!9/e14ce6/6

关于MySQL 将列转为行并转回列，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/48837256/

25 4 0
文章推荐： php - 使用 PHP 和 MySQL...如何释放内存？
文章推荐： ios - 模拟寿司火车 - Swift SpriteKit
文章推荐： java - JPA 与同一实体的两个单向@OneToMany 关系导致重复条目
文章推荐： ios - NavigationItem 中的 titleView 不考虑 iOS 11 中的框架高度
可可西里
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com