英文:
Columns are aggregated incompletely
问题
这是我的表格
ID|CREATED|VER |
--+-------+--------+
61| NULL |2023.4.6|
61| NULL | null |
对所有列进行了聚合，如下所示
SELECT ID,
SUM(CASE WHEN CREATED IS NOT NULL THEN 1 ELSE 0 END) AS CREATED_AGG,
SUM(CASE WHEN VER IS NULL THEN 1 ELSE 0 END) AS VER_AGG
FROM TABLE
GROUP BY ID, CREATED, VER
结果
ID|CREATED_AGG|VER_AGG |
--+-----------+------------------+
61| 0| 0|
61| 0| 1|
我认为下面是预期结果。为什么看起来它没有完全聚合？
ID|CREATED_AGG|VER_AGG |
--+-----------+------------------+
61| 0| 1|
英文:
This is my table
ID|CREATED|VER |
--+-------+--------+
61| NULL |2023.4.6|
61| NULL | null |
Did aggregation on all of the columns like this
SELECT ID,
SUM(CASE WHEN CREATED IS NOT NULL THEN 1 ELSE 0 END) AS CREATED_AGG,
SUM(CASE WHEN VER IS NULL THEN 1 ELSE 0 END) AS VER_AGG
FROM TABLE
GROUP BY ID, CREATED, VER
result
ID|CREATED_AGG|VER_AGG |
--+-----------+------------------+
61| 0| 0|
61| 0| 1|
I think below is the expected result. Why does it look like it is not fully aggregated?
ID|CREATED_AGG|VER_AGG |
--+-----------+------------------+
61| 0| 1|
答案1
得分: 1
提供的数据和查询的结果在所有符合标准的SQL数据库上都是正确且可重现的。
在“CREATED”和“VER”列中的“NULL”被分组到不同的组中，因此会导致多个结果行，因为“NULL”不被视为可与“其他”“NULL”进行比较。因此，“NULL”不在同一组中。
要实现所需的结果，只需按“ID”分组即可。
英文:
The result for the data and query provided is correct and reproducible on all standard compliant SQL databases.
The NULLs in columns CREATED and VER are grouped into separate groups - thus resulting in multiple result rows - since NULL is not considered comparable to "other" NULLs. So, the NULLs are not in the same group.
To achieve the desired outcome, grouping just by ID does the trick.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论