sql - 查询结果未按预期返回

问题描述

我正在努力解决几天前根据一位SO用户的建议尝试的查询，并使其与以下内容一起使用：

WITH your_table(ID,STOREDATE,VALUE,INFO)
AS
(
SELECT 1122,'1/1/2020',2,'DONE' UNION ALL
SELECT 1122,'1/2/2020',1,'DONE' UNION ALL
SELECT 1122,'1/3/2020',7,'DONE' UNION ALL
SELECT 1122,'1/4/2020',8,'DONE'
),

CTE AS
(
    SELECT ID,
    STOREDATE,
    VALUE,
    CASE 
        WHEN VALUE = 8 THEN 0
        WHEN VALUE + LAG(VALUE) OVER(ORDER BY STOREDATE) = 8 THEN 0
        WHEN VALUE + LEAD(VALUE) OVER(ORDER BY STOREDATE) = 8 THEN 0
        ELSE VALUE
    END VALUE2,
    INFO
    FROM your_table
)

SELECT *,
CASE 
    WHEN 
        (
            SELECT COUNT(*) FROM CTE A 
            WHERE A.VALUE2 = 0 AND A.STOREDATE < B.STOREDATE
        ) >= 1 AND B.VALUE = 8 THEN B.VALUE
    ELSE B.VALUE2
END VALUE3
FROM CTE B

注意：我的想法是在任何给定行上得到 8 的总和。所以对于上面的输入，我的预期输出是这样的。

输出：

ID      STOREDATE   VALUE   INFO
1122    1/1/2020    2       DONE
1122    1/2/2020    0           //1 + 7 = 8; it'll update both the rows with zero
1122    1/3/2020    0           //For this, it's just fine
1122    1/4/2020    8       DONE

现在我的问题是以下输入，我无法弄清楚如何获得预期的结果集。

输入：

ID      STOREDATE   VALUE   INFO
1122    1/1/2020    2       DONE
1122    1/2/2020    1       DONE
1122    1/3/2020    2       DONE
1122    1/4/2020    7       DONE

预期输出：

ID      STOREDATE   VALUE   INFO
1122    1/1/2020    2       DONE
1122    1/2/2020    0       
1122    1/3/2020    2       DONE
1122    1/4/2020    0

输入：

ID      STOREDATE   VALUE   INFO
1122    1/1/2020    2       DONE
1122    1/2/2020    2       DONE
1122    1/3/2020    2       DONE
1122    1/4/2020    2       DONE

预期输出：

ID      STOREDATE   VALUE   INFO
1122    1/1/2020    0       
1122    1/2/2020    0       
1122    1/3/2020    0       
1122    1/4/2020    0

我可能有几个 id 来获得如下的确切结果，但是对查询进行调整并不能得到我想要的结果。任何建议将不胜感激 - 谢谢。

WITH your_table(ID,STOREDATE,VALUE,INFO)
AS
(
SELECT 1122,'1/1/2020',2,'DONE' UNION ALL
SELECT 1122,'1/2/2020',7,'DONE' UNION ALL
SELECT 1122,'1/3/2020',1,'DONE' UNION ALL
SELECT 1122,'1/4/2020',8,'DONE' UNION ALL
SELECT 4466,'1/1/2020',2,'DONE' UNION ALL
SELECT 4466,'1/2/2020',7,'DONE' UNION ALL
SELECT 4466,'1/3/2020',1,'DONE' UNION ALL
SELECT 4466,'1/4/2020',8,'DONE'
),

CTE AS
(
    SELECT ID,
    STOREDATE,
    VALUE,
    CASE 
        WHEN VALUE = 8 THEN 0
        WHEN VALUE + LAG(VALUE) OVER(ORDER BY STOREDATE) = 8 THEN 0
        WHEN VALUE + LEAD(VALUE) OVER(ORDER BY STOREDATE) = 8 THEN 0
        ELSE VALUE
    END VALUE2,
    INFO
    FROM your_table
)

SELECT *,
CASE 
    WHEN 
        (
            SELECT COUNT(*) FROM CTE A 
            WHERE A.VALUE2 = 0 AND A.STOREDATE < B.STOREDATE
        ) >= 1 AND B.VALUE = 8 THEN B.VALUE
    ELSE B.VALUE2
END VALUE3
FROM CTE B

预期输出：

ID      STOREDATE   VALUE   INFO
1122    1/1/2020    2       DONE
1122    1/2/2020    0           
1122    1/3/2020    0           
1122    1/4/2020    8       DONE
4466    1/1/2020    2       DONE
4466    1/2/2020    0           
4466    1/3/2020    0           
4466    1/4/2020    8       DONE

更新1：请检查以下

输入：

ID      STOREDATE   VALUE   INFO
1122    1/1/2020    2       DONE
1122    1/2/2020    1       DONE
1122    1/3/2020    2       DONE
1122    1/4/2020    7       DONE

预期输出：

ID      STOREDATE   VALUE   INFO
1122    1/1/2020    2       DONE
1122    1/2/2020    0       
1122    1/3/2020    2       DONE
1122    1/4/2020    0

输入：

ID      STOREDATE   VALUE   INFO
1122    1/1/2020    2       DONE
1122    1/2/2020    2       DONE
1122    1/3/2020    2       DONE
1122    1/4/2020    2       DONE

预期输出：

ID      STOREDATE   VALUE   INFO
1122    1/1/2020    0       
1122    1/2/2020    0       
1122    1/3/2020    0       
1122    1/4/2020    0

标签： sqloraclesql-server-2008oracle10g

WITH your_table(ID,STOREDATE,VALUE,INFO) AS ( SELECT 1122,to_date('01/01/2020','dd/mm/yyyy'),2,'DONE' UNION ALL SELECT 1122,to_date('01/02/2020','dd/mm/yyyy'),1,'DONE' UNION ALL SELECT 1122,to_date('01/03/2020','dd/mm/yyyy'),7,'DONE' UNION ALL SELECT 1122,to_date('01/04/2020','dd/mm/yyyy'),8,'DONE' ),

sql - 查询结果未按预期返回

问题描述

解决方案

推荐阅读