查询结果未按预期返回 [英] Query Result Not Returned As Expected
问题描述
我正在努力尝试几天前提出的 SO 用户之一的建议，并使其与以下各项配合使用:
I am struggling with a query that tried few days back with a suggestion of one of SO user and got it work with the following:
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
NB :我的想法是在任意给定的行中获得8的总和.因此，对于上面的输入，我的预期输出是这样.
N.B: My idea was to get the summation of 8 at any given row. So for the above input, my expected output is this.
输出:
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
现在我的问题是以下输入内容，我无法弄清楚如何获得预期的结果集.
Now my problem is with the below inputs that I can't figure out how to get expected result set.
输入:
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来获得如下所示的准确结果，但是对查询进行调整并不能使我了解所需的内容.任何建议将不胜感激-谢谢.
I may have several ids to get the exact result as below but making tweaks to the query doesn't get me to what I wanted. Any suggestion would be appreciated - Thanks.
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
推荐答案
您的第一个大错误是'stordate'不是日期，而是日期的字符串表示形式.因此，所有比较将是字符串的比较，而不是日期.作为字符串，首先是'01/02/2020'或'02/01/2019'.
Your first big mistake is that 'stordate' is not a date, but a character string representation of a date. Thus, all comparisons will be comparisons of a string, not a date. As a string, which comes first, '01/02/2020' or '02/01/2019'.
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'
),
这篇关于查询结果未按预期返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
