时间戳

首页 > 解决方案 > 在某些行上重复一个值

sql - 在某些行上重复一个值

问题描述

我有一个值列表，我必须按CHILDVALUE以下方式显示母值。MOTHERVALUE应该与 at 相同，CHILDVALUE否则RNO = 1对MOTHERVALUE后续行重复 ，直到遇到从 RNO = 1 开始的新序列。以下是我希望实现的

RNO ChildValue  MotherValue SKIPNO  CREATEDDATE
1   345dg       345dg       4   19/9/2018 2:49
2   342sds      345dg       4   19/9/2018 11:53
.
.
.               
19  343dfd      345dg       4   6/11/2018 12:40
20  234dfs      345dg       4   6/11/2018 14:56
1   545ert      545ert      4   6/11/2018 15:17
2   543tye      545ert      4   7/11/2018 11:29
.
.
.               
9   345cxv      545ert      4   16/11/2018 14:16
1   563mnj      563mnj      5   19/11/2018 2:12

行号是根据SKIPNO(not distinct) 和动态生成的CREATEDDATE。但是我在显示MOTHERVALUE某些行的正确性时遇到了问题

我尝试了以下查询，它最能满足至少 50% 的需求。

 select RNO
        , CHILDVALUE 
       , case RNO when 1 then CHILDVALUE 
           else lag(MOTHERVALUE) over (order by SKIPNO, CreatedDate ASC) end as MOTHERVALUE
       , SKIPNO, CreatedDate 
from( SELECT (ROW_NUMBER() OVER (PARTITION BY A.SKIPNO
              ORDER BY A.SKIPNO,  A.CreatedDate ASC) RNO
          , A.* 
      from  (select distinct CHILDVALUE
                    , CHILDVALUE as MOTHERVALUE
                    , SKIPNO
                    , CreatedDate 
              from values ) A
          )
      )

此查询部分给出了预期的输出，但仍与实际结果相去甚远，因为它显示了MOTHERVALUE only for the first two rows. The query does not help me to show the correct超过第二行的正确 MOTHERVALUE`。

这是我的桌子：

   CREATE TABLE VALUES (
      CHILDVALUE VARCHAR2(36), 
      SKIPNO VARCHAR2(36), 
      CREATEDDATE DATE)

请帮忙。

标签： sqloracle

解决方案

注意我假设您的样本数据在第二组和第三组中的skipnos 不正确。我的答案在其样本数据中使用了更新的值。

您可以使用first_value分析功能，而不是lag实现您的目标，例如：

WITH vals AS (SELECT '345dg' childvalue, 4 skipno, to_date('19/09/2018 02:49', 'dd/mm/yyyy hh24:mi') createddate FROM dual UNION ALL
              SELECT '342sds' childvalue, 4 skipno, to_date('19/09/2018 11:53', 'dd/mm/yyyy hh24:mi') createddate FROM dual UNION ALL
              SELECT '343dfd' childvalue, 4 skipno, to_date('06/11/2018 12:40', 'dd/mm/yyyy hh24:mi') createddate FROM dual UNION ALL
              SELECT '234dfs' childvalue, 4 skipno, to_date('06/11/2018 14:56', 'dd/mm/yyyy hh24:mi') createddate FROM dual UNION ALL
              SELECT '545ert' childvalue, 5 skipno, to_date('06/11/2018 15:17', 'dd/mm/yyyy hh24:mi') createddate FROM dual UNION ALL
              SELECT '543tye' childvalue, 5 skipno, to_date('07/11/2018 11:29', 'dd/mm/yyyy hh24:mi') createddate FROM dual UNION ALL
              SELECT '345cxv' childvalue, 5 skipno, to_date('16/11/2018 14:16', 'dd/mm/yyyy hh24:mi') createddate FROM dual UNION ALL
              SELECT '563mnj' childvalue, 6 skipno, to_date('19/11/2018 02:12', 'dd/mm/yyyy hh24:mi') createddate FROM dual)
SELECT row_number() OVER (PARTITION BY skipno ORDER BY createddate) rno,
       childvalue,
       first_value(childvalue) OVER (PARTITION BY skipno ORDER BY createddate) mothervlue,
       skipno,
       createddate
FROM   vals;

       RNO CHILDVALUE MOTHERVLUE     SKIPNO CREATEDDATE
---------- ---------- ---------- ---------- -----------
         1 345dg      345dg               4 19/09/2018
         2 342sds     345dg               4 19/09/2018
         3 343dfd     345dg               4 06/11/2018
         4 234dfs     345dg               4 06/11/2018
         1 545ert     545ert              5 06/11/2018
         2 543tye     545ert              5 07/11/2018
         3 345cxv     545ert              5 16/11/2018
         1 563mnj     563mnj              6 19/11/2018

推荐阅读