SQL联合但垂直 [英] SQL union but vertically

问题描述

最近开始使用SQL中的简单查询

recently started with simple queries in SQL

我想知道是否有可能像联合"一样水平地垂直添加数据

I was wondering if there is a possibility to add data vertically like "union" does horizontally

我有两个要合并的选择

month year data1 data2 grouped by month, year
month year data3 data4 grouped by month, year

我要存档的是

month year data1 data2 data3 data4

是否有任何功能可以像Union一样进行存档?我曾尝试通过加入"来做到这一点，但失败了

Is there any function working like union to archive this? I was trying to do so with "join" but failed horribly

推荐答案

听起来像是 FULL JOIN 可能就是您想要的.它包括来自两个操作数的所有行，这些行结合了满足 ON 子句中的表达式的行.如果另一个操作数中没有匹配的伙伴行，则另一个操作数的列将填充 NULL .这就是为什么需要 coalesce()来获取年份或月份的原因.

Sounds like if a FULL JOIN is probably what you want. It include all rows from both operands combining rows, that fulfill the expression in the ON clause. If there is no matching partner row in the other operand, the columns of the other operand are filled with NULL. That's why coalesce() is needed to get the year or month.

SELECT coalesce (x1.year, x2.year) year,
       coalesce (x1.month, x2.month) month,
       x1.data1,
       x1.data2,
       x2.data3,
       x2.data4
       FROM (<your first grouping query>) x1
            FULL JOIN (<your second grouping query>) x2
                      ON x2.year = x1.year
                         AND x2.month = x1.month;

分别用您的第一个或第二个查询替换<您的第一个分组查询> 和<您的第二个分组查询> .

Replace <your first grouping query> and <your second grouping query> with your first or second query, respectively.

这篇关于SQL联合但垂直的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！