DB2 SQL查询结果多余数据

发布于08月25日

enter image description here

DB2第10和第11版 SQL为:

WITH T AS(
SELECT ID FROM
(SELECT ID FROM (VALUES(1),(2),(3)) T(ID)
GROUP BY ID) WHERE ID IN(1,2,3)
  )
SELECT ID FROM T WHERE ID IN(2,3)
UNION
SELECT ID FROM T WHERE 1 = 0

预期结果为%2行%2和%3，但有%3条记录

原来的复杂查询已被简化

推荐答案

它"看起来"像是一个错误，changing from union to union all made no difference，但如果在最后的查询中颠倒SELECT的顺序，您将只返回2行.也许其他人可以解释这种差异.以下是一把DB2小提琴的曲子:

WITH T AS(
SELECT ID FROM
(SELECT ID FROM (VALUES(1),(2),(3)) T(ID)
GROUP BY ID) WHERE ID IN(1,2,3)
  )
SELECT ID FROM T WHERE ID IN(2,3)
--UNION
--SELECT ID FROM T WHERE 1 = 0

ID
2
3

WITH T AS(
SELECT ID FROM
(SELECT ID FROM (VALUES(1),(2),(3)) T(ID)
GROUP BY ID) WHERE ID IN(1,2,3)
  )
--SELECT ID FROM T WHERE ID IN(2,3)
--UNION
SELECT ID FROM T WHERE 1 = 0

未返回任何行

WITH T AS(
SELECT ID FROM
(SELECT ID FROM (VALUES(1),(2),(3)) T(ID)
GROUP BY ID) WHERE ID IN(1,2,3)
  )
SELECT ID FROM T WHERE ID IN(2,3)
UNION ALL
SELECT ID FROM T WHERE 1 = 0

ID
1
2
3

WITH T AS(
SELECT ID FROM
(SELECT ID FROM (VALUES(1),(2),(3)) T(ID)
GROUP BY ID) WHERE ID IN(1,2,3)
  )
SELECT ID FROM T WHERE 1 = 0
UNION ALL
SELECT ID FROM T WHERE ID IN(2,3)