sql - Oracle 查询返回单行

问题描述

我需要编写一个查询，返回第五行作为输出加上另外两列，分别从第一行和第三行捕获时间作为一天中的时间和发货时间。区分这些行的是最后一个和下一个状态列。

以下是我的查询和输出。

 SELECT DISTINCT
         SLMCU AS "BUSINESSUNIT",
         SLDOCO AS "ORDERNO",
         SLDCTO AS "ORDERTYPE",
         SLAN8 AS "CUSTOMERNO",
         ABALPH AS "CUSTOMERNAME",
         SLLITM AS "ITEMNO",
         SLDSC1 AS "DESCRIPTION",
         SLDSC2 AS "DESCRIPTION2",
         SLLTTR AS "LAST STATUS",
         SLNXTR AS "NEXT STATUS",
         CASE SLTRDJ
            WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
            ELSE TO_DATE (TO_CHAR (SLTRDJ + 1900000), 'YYYYDDD')
         END
            AS "ORDER DATE",
         CASE SLADDJ
            WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
            ELSE TO_DATE (TO_CHAR (SLADDJ + 1900000), 'YYYYDDD')
         END
            AS "SHIPPED DATE",
         SLTDAY AS "TIME",
         SLUORG / 10000 AS "ORDER QUANTITY",
         SLSOQS / 10000 AS "SHIPPED QUANTITY"
     FROM PRODDTA.F42199 INNER JOIN PRODDTA.F0101 ON SLAN8 = ABAN8 WHERE SLDOCO = 19437443
  -- WHERE ((SLLTTR = 520 AND SLNXTR = 540) OR (SLLTTR = 620 AND SLNXTR = 582)) AND SLDOCO = 19437443
ORDER BY "LAST STATUS" ASC;

删除了一些列以获得更好的可见性。

期望的输出

标签： sqloracle

根据您的排序标准 ( NEXT_STATUS) 为行指定递增的行号，然后使用分析函数查找第一行和第三行的值，然后过滤以仅返回第五行：

WITH your_query AS (
  -- paste your query here
)
SELECT *
FROM   (
  SELECT t.*,
         MAX( CASE WHEN rn = 1 THEN time END ) OVER () AS time_of_day,
         MAX( CASE WHEN rn = 3 THEN time END ) OVER () AS shipped_time
  FROM   (
    SELECT q.*,
           ROW_NUMBER() OVER ( ORDER BY next_status ) AS rn
    FROM   your_query q
  ) t
  WHERE rn IN ( 1, 3, 5 )
)
WHERE  rn = 5;

替代版本：

WITH your_query AS (
  -- paste your query here
)
SELECT *
FROM   (
  SELECT t.*,
         MAX( CASE WHEN ROWNUM = 1 THEN time END ) OVER () AS time_of_day,
         MAX( CASE WHEN ROWNUM = 3 THEN time END ) OVER () AS shipped_time,
         ROWNUM AS rn
  FROM   (
    SELECT *,
    FROM   your_query
    ORDER BY next_status
  ) t
  WHERE ROWNUM <= 5
)
WHERE  rn = 5;

更新多个ORDERNO：

WITH your_query AS (
  -- paste your query here
)
SELECT *
FROM   (
  SELECT t.*,
         MAX( CASE WHEN rn = 1 THEN time END ) OVER ( PARTITION BY orderno ) AS time_of_day,
         MAX( CASE WHEN rn = 3 THEN time END ) OVER ( PARTITION BY orderno ) AS shipped_time
  FROM   (
    SELECT q.*,
           ROW_NUMBER() OVER ( PARTITION BY orderno ORDER BY next_status ) AS rn
    FROM   your_query q
  ) t
  WHERE rn IN ( 1, 3, 5 )
)
WHERE  rn = 5;

sql - Oracle 查询返回单行

问题描述

解决方案

推荐阅读