时间戳

首页 > 解决方案 > 使用日期将 SQL 查询转换为 Pivot (Oracle)

sql - 使用日期将 SQL 查询转换为 Pivot (Oracle)

问题描述

每个人。

我正在尝试将此 Oracle SQL 查询转换为 Pivot，但我无法弄清楚如何。

SELECT TO_CHAR (STARTTIME, 'YYYY/MM/DD')                AS DATETIME,
         COUNT (*)                                      AS QTY,
         DECODE (STATE,'P','PASS','F','FAIL',  STATE)   AS STATUS
    FROM R_TEST_RECORD
   WHERE SN IN (SELECT SN
                  FROM R_SN
                 WHERE SKUNO = '750-062572')
     AND MESSTATION = 'SI_V1'
     AND STARTTIME BETWEEN TO_DATE ('2020/06/02 01:00:49',
                                    'YYYY/MM/DD HH24:MI:SS')
                       AND TO_DATE ('2021/10/02 01:53:49',
                                    'YYYY/MM/DD  HH24:MI:SS')
GROUP BY TO_CHAR (STARTTIME, 'YYYY/MM/DD'), STATE
ORDER BY DATETIME;

查询给了我下一张表：

约会时间 数量 地位
2021/03/16 106 经过
2021/03/17 731 经过
2021/03/19 102 经过
2021/04/05 102 经过
2021/06/08 100 经过
2021/06/15 99 经过
2021/06/20 712 经过
2021/06/22 149 经过
2021/06/29 33 经过
2021/06/30 6 经过
2021/07/02 2 经过
2021/07/04 2 经过

我想要的是：

地位 2021/03/16 2021/03/17 2021/03/19 2021/04/05
经过 106 731 102 102

此外，我希望它是动态的，因此我不必在 Pivot 语句中硬编码日期。

我的工作是下一步：

SELECT *
FROM (
    SELECT TO_CHAR (starttime, 'YYYY/MM/DD') starttime,
    STATE
    FROM r_test_record A
   WHERE     sn IN (SELECT sn
                      FROM r_sn
                     WHERE skuno = '750-062572')
         AND messtation = 'SI_V1'
         AND starttime BETWEEN TO_DATE ('2021/06/02 01:00:49',
                                        'YYYY/MM/DD HH24:MI:SS')
                           AND TO_DATE ('2021/10/02 01:53:49',
                                        'YYYY/MM/DD  HH24:MI:SS')
) 
PIVOT
(   
    COUNT(STATE)
    FOR STARTTIME IN (  
      SELECT DISTINCT TO_CHAR (starttime, 'YYYY/MM/DD') STARTTIME
   FROM r_test_record
  WHERE     sn IN (SELECT sn
                     FROM r_sn
                    WHERE skuno = '750-062572')
        AND messtation = 'SI_V1'
        AND starttime BETWEEN TO_DATE ('2021/06/02 01:00:49',
                                       'YYYY/MM/DD HH24:MI:SS')
                          AND TO_DATE ('2021/10/02 01:53:49',
                                       'YYYY/MM/DD  HH24:MI:SS')
    )
)

谢谢你的帮助。

标签： sqloraclepivot

解决方案

如果要将其限制为七列，一种选择是按 WEEK_OF 隐式分组，这样您就可以保留多行。像这样的东西：

SELECT *
FROM (SELECT TRUNC(r.starttime, 'DAY') AS WEEK_OF, 
             TO_CHAR(r.starttime, 'D') AS DAY_OF_WEEK, 
             status
      FROM r_test_record r)
PIVOT (COUNT(*) FOR DAY_OF_WEEK IN (1 AS SUNDAY, 2 AS MONDAY, 3 AS TUESDAY,
                                               4 AS WEDNESDAY, 5 AS THURSDAY,
                                               6 AS FRIDAY, 7 AS SATURDAY))
ORDER BY WEEK_OF, STATUS;

您将丢失列名中的实际日期，但您可以运行它以获得完整的报告。或者您可以添加 where 子句来限制日期。

推荐阅读