登录
当前位置： 首页 > 面试题库 >

在MySQL中取消枢轴化多列

张坚白
2023-03-14
问题内容：

我有一些非规范化的数据，我试图将其移开，希望所有的人都能帮助我找出执行此操作的最佳方法。我已经使用多个并集语句完成了此操作，但是我希望做的是创建一个动态查询，随着向表中添加更多列，该查询可以一遍又一遍地执行此操作。我的数据看起来像这样：（数字列一直到50）

| Code  | Desc  | Code_0 | Desc_0| Period|    1    |    2   |    3    |    4     |  
|-------|-------|--------|-------|-------|---------|--------|---------|----------|
| NULL  | NULL  |  NULL  |  NULL |  Date |29-Nov-13|6-Dec-13|13-Dec-13| 20-Dec-13|  
|CTR07  |Risk   |  P1    | Phase1|  P    |   0.2   |  0.4   |   0.6   |    1.1   |         
|CTR07  |Risk   |  P1    | Phase1|  F    |   0.2   |  0.4   |   0.6   |    1.1   |          
|CTR07  |Risk   |  P1    | Phase1|  A    |   0.2   |  0.4   |   0.6   |    1.1   |
|CTR08  |Oper   |  P1    | Phase1|  P    |   0.6   |  0.6   |   0.9   |    2.7   |
|CTR08  |Oper   |  P1    | Phase1|  F    |   0.6   |  0.6   |   0.9   |    2.7   |
|CTR08  |Oper   |  P1    | Phase1|  A    |   0.6   |  0.6   |   0.9   |    2.7   |

列标题是最上面的行。如您所见，查看数据时，需要解决一些奇怪的问题。

日期字段开始前的前四个NULL列是一个问题。具有数字标题（1-50）的每一列代表一个星期。问题在于，每个星期在同一列中不仅具有日期字段，而且具有该周的百分比值。我想将其向下旋转，使其看起来像这样：

| Code  | Desc  |Code_0 |Desc_0 | Period| Date    |Percent|  
|-------|-------|-------|-------|-------|---------|-------|
|CTR07  | Risk  |  P1   | Phase |   P   | 11/29/13| 0.2   |   
|CTR07  | Risk  |  P1   | Phase1|   F   | 11/29/13| 0.2   |
|CTR07  | Risk  |  P1   | Phase1|   A   | 11/29/13| 0.2   |
|CTR08  | Oper. |  P1   | Phase1|   P   | 11/29/13| 0.6   |

每个星期的日期都在其自己的列中，并将百分比按其各自的日期分组。

由不同的代码，Desc，CODE_0，期间和日期作为键。我想将日期与数字列中的百分比分开，然后将数字列带入按日期连接的自己的列中。如前所述，我已经使用UNION语句静态完成了此操作，但是我想编写某种查询，该查询可以在表扩展时动态地执行。任何帮助将不胜感激。让我知道是否需要任何其他信息，这是我关于StackOverflow的第一个问题，我有两个漂亮的屏幕截图向您展示，但是在这次交流中我还不到10个。仅适用于科幻和幻想。我知道，对吧？

我在联合中用于静态创建底表的代码：

select `Code`, `Desc`, `Code_0`, `Desc_0`, `Period`, (select STR_TO_DATE(`1`, '%d%b%y') from combined_complete where `1` = '29Nov13') as `Date`, `1` as `Percent`
from combined_complete
where period <> 'Date'
union
select `Code`, `Desc`, `Code_0`, `Desc_0`, `Period`, (select STR_TO_DATE(`2`, '%d%b%y') from combined_complete where `2` = '06Dec13') as `Date`, `2`
from combined_complete
where period <> 'Date'
union
select `Code`, `Desc`, `Code_0`, `Desc_0`, `Period`, (select STR_TO_DATE(`3`, '%d%b%y') from combined_complete where `3` = '13Dec13') as `Date`, `3`
from combined_complete
where period <> 'Date'
union
select `Code`, `Desc`, `Code_0`, `Desc_0`, `Period`, (select STR_TO_DATE(`4`, '%d%b%y') from combined_complete where `4` = '20Dec13') as `Date`, `4`
from combined_complete
where period <> 'Date'

问题答案：

对于这个建议，我创建了一个简单的50行表，称为TransPoser，在MySQL或您的数据库中可能已经有一个整数表，但是您想要类似的东西，将那些编号的列的数字从1转换为N。

然后，使用该表交叉连接到您的非规范化表（我称其为BadTable），但将其限制在第一行。然后使用一组case表达式将pivot这些日期字符串放入一列中。如果需要的话，可以将其转换为适当的日期（我建议这样做，但未包括在内）。

然后，将这种较小的换位用作主查询中的派生表。

主查询将忽略第一行，但也使用交叉联接将所有原始行强制为50行（在本示例中为4行）。然后将这种笛卡尔积乘回到上面讨论的派生表中以提供日期。然后是另一组case表达式，它们将百分比转换为与日期和各种代码对齐的列。

结果html" target="_blank">示例（来自示例数据），手动添加了空行：

| N |  CODE | DESC | CODE_0 | DESC_0 |   THEDATE | PERCENTAGE |
|---|-------|------|--------|--------|-----------|------------|
| 1 | CTR07 | Risk |     P1 | Phase1 | 29-Nov-13 |        0.2 |
| 1 | CTR07 | Risk |     P1 | Phase1 | 29-Nov-13 |        0.2 |
| 1 | CTR07 | Risk |     P1 | Phase1 | 29-Nov-13 |        0.2 |
| 1 | CTR08 | Oper |     P1 | Phase1 | 29-Nov-13 |        0.6 |
| 1 | CTR08 | Oper |     P1 | Phase1 | 29-Nov-13 |        0.6 |
| 1 | CTR08 | Oper |     P1 | Phase1 | 29-Nov-13 |        0.6 |

| 2 | CTR07 | Risk |     P1 | Phase1 |  6-Dec-13 |        0.4 |
| 2 | CTR07 | Risk |     P1 | Phase1 |  6-Dec-13 |        0.4 |
| 2 | CTR07 | Risk |     P1 | Phase1 |  6-Dec-13 |        0.4 |
| 2 | CTR08 | Oper |     P1 | Phase1 |  6-Dec-13 |        0.6 |
| 2 | CTR08 | Oper |     P1 | Phase1 |  6-Dec-13 |        0.6 |
| 2 | CTR08 | Oper |     P1 | Phase1 |  6-Dec-13 |        0.6 |

| 3 | CTR07 | Risk |     P1 | Phase1 | 13-Dec-13 |        0.6 |
| 3 | CTR07 | Risk |     P1 | Phase1 | 13-Dec-13 |        0.6 |
| 3 | CTR07 | Risk |     P1 | Phase1 | 13-Dec-13 |        0.6 |
| 3 | CTR08 | Oper |     P1 | Phase1 | 13-Dec-13 |        0.9 |
| 3 | CTR08 | Oper |     P1 | Phase1 | 13-Dec-13 |        0.9 |
| 3 | CTR08 | Oper |     P1 | Phase1 | 13-Dec-13 |        0.9 |

| 4 | CTR07 | Risk |     P1 | Phase1 | 20-Dec-13 |        1.1 |
| 4 | CTR07 | Risk |     P1 | Phase1 | 20-Dec-13 |        1.1 |
| 4 | CTR07 | Risk |     P1 | Phase1 | 20-Dec-13 |        1.1 |
| 4 | CTR08 | Oper |     P1 | Phase1 | 20-Dec-13 |        2.7 |
| 4 | CTR08 | Oper |     P1 | Phase1 | 20-Dec-13 |        2.7 |
| 4 | CTR08 | Oper |     P1 | Phase1 | 20-Dec-13 |        2.7 |

查询：

select
       n.n
     , b.Code
     , b.Desc
     , b.Code_0
     , b.Desc_0
     , T.theDate
     , case
            when n.n =  1 then `1`
            when n.n =  2 then `2`
            when n.n =  3 then `3`
            when n.n =  4 then `4`
         /* when n.n =  5 then `5` */
         /* when n.n = 50 then `50`*/
       end as Percentage
from BadTable as B
cross join (select N from TransPoser where N < 5) as N
inner join (
            /* transpose just the date row */
            /* join back vis the number given to each row */
            select
                    n.n
                  , case
                        when n.n =  1 then `1`
                        when n.n =  2 then `2`
                        when n.n =  3 then `3`
                        when n.n =  4 then `4`
                     /* when n.n =  5 then `5` */
                     /* when n.n = 50 then `50`*/
                   end as theDate
            from BadTable as B
            cross join (select N from TransPoser where N < 5) as N
            where b.code is null
            and b.Period = 'Date'
           ) as T on N.N = T.N
where b.code is NOT null
and b.Period <> 'Date'
order by
       n.n
     , b.code
;

对于以上内容，请参见此SQLFIDDLE

期望恕我直言，结果期望一个完全准备好的可执行文件交付确实不公平-
这是“拉伸友谊”。但是要将上面的查询转换为动态查询并不难。这有点“乏味”，因为语法有点棘手。我对MySQL并没有那么的经验，但这就是我的做法：

set @numcols := 4;
set @casevar := '';

set @casevar := (
                  select 
                  group_concat(@casevar
                                       ,'when n.n =  '
                                       , n.n
                                       ,' then `'
                                       , n.n
                                       ,'`'
                                      SEPARATOR ' ')
                  from TransPoser as n
                  where n.n <= @numcols
                 )
;


set @sqlvar := concat(
          'SELECT n.n , b.Code , b.Desc , b.Code_0 , b.Desc_0 , T.theDate , CASE '
        , @casevar
        , ' END AS Percentage FROM BadTable AS B CROSS JOIN (SELECT N FROM  TransPoser WHERE N <='
        , @numcols
        , ') AS N INNER JOIN ( SELECT n.n , CASE '
        , @casevar                                                                                                       
        , ' END AS theDate FROM BadTable AS B CROSS JOIN (SELECT N FROM  TransPoser WHERE N <='
        , @numcols
        , ') AS N WHERE b.code IS NULL '
        , ' AND b.Period = ''Date'' ) AS T ON N.N = T.N WHERE b.code IS NOT NULL AND b.Period <> ''Date'' ORDER BY n.n , b.code ' 
        );

PREPARE stmt FROM @sqlvar;
EXECUTE stmt;

[Demo of the dynamic approach](http://sqlfiddle.com/#!2/d11f7d/2)



 类似资料：
  • MySQL动态枢轴

    问题内容： 我有一张这样的产品零件表： 部分 我想要一个查询，将返回这样的表： 在其实际实施中，将有数百万个产品零件 问题答案： 不幸的是，MySQL没有函数，但是您可以使用聚合函数和语句对其进行建模。对于动态版本，您将需要使用准备好的语句： 请参见带有演示的SQL Fiddle 如果只有几列，则可以使用静态版本：

  • Oracle枢轴中的列

    问题内容： 示例选择： 输出： 是否可以按时间顺序对记录进行排序，排除列“月”和“年”？没有列出所有列。 UPD 需要： 就像是： 问题答案： 要对记录进行排序，可以将以下内容添加到sql的末尾 注意：枢轴列名称区分大小写，因此需要用引号引起来 这是完整的查询，仅选择您需要的列：

  • 雪花枢轴

  • TSQL动态枢轴

    我在使用PIVOT SQL脚本时遇到了一个“小”问题。我目前的解决方案基于类似的问题 动态枢轴 . 我已经写好了PIVOT脚本，基本上没问题。然而，我的情况是这样的，脚本输出类似这样的内容，而不是将唯一的条目放在单行上 而我所寻找的结果应该是这样的 这是脚本 我将不胜感激一些可以帮助我解决这个问题的指示。我在这里做了一个小提琴，由于某种奇怪的原因，它没有输出任何东西，但生成架构的所有代码都在那里

  • 非规范化数据（也许是枢轴？）

    问题内容： 我有一个简单的表，其中包含学生编号和相应的教师编号，并且需要对它进行规范化，以输入到旧版系统中。 例如，下面是数据现在的样子： 我希望它看起来像这样，将每个Teacher分成一个单独的列，从左到右填充各列。一个业务规则是，每个学生最多只能有六位老师： 原始表中有10,000多行，因此我需要以编程方式执行此操作。谢谢！ 问题答案： 您可以使用数据透视。您还需要“排名”您的老师1-6。请参

  • T-SQL动态枢轴

    问题内容： 好吧，我有一张看起来像这样的表 现在，我需要将其转换为： 我一直在看动态透视图示例，但是我似乎无法使其适合我的情况。 有人可以帮忙吗？ 问题答案： 看下面的例子

相关阅读

SQL Server 2008中的枢轴功能枢轴-SQL-子查询中的值Row_Number（）需要动态枢轴产生摘要（“枢轴”？）表SQL Server枢轴是否为空？

相关文章

Java锁粗化和锁消除Java锁粗化和锁消除PHP MySQL 读取数据DefaultMQPushConsumer消费者发起拉取消息请求MySQL查询优化与调优

相关问答

在已缩放节点中的枢轴点缩放JavaFx将鼠标缩放为枢轴如何在熊猫数据框枢轴？[重复]如何在Laravel中与额外的字段同步枢轴？Laravel口才在枢轴关系中执行聚合功能

相关工具

Movable Type 中文化多线程页面抓取服务器Polr 中文优化版esoTalk 中文优化版WordPress中文优化版

相关文档

MySQL 中文手册MySQL 中文参考手册Java 国际化 中文教程Python 取证中文教程Windows MSMQ 消息队列［初步文档］
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.