postgresql 将两个不相关的视图合并为一个视图

Merge two unrelated views into a single view

提问by user519753

Let's say I have in my first view (ClothingID, Shoes, Shirts)and in the second view I have (ClothingID, Shoes, Shirts)HOWEVER the data is completely unrelated, even the ID field is not related in anyway. I want them combined into 1 single view for reporting purposes. so the 3rd view (the one I'm trying to make) should look like this: (ClothingID, ClothingID2, Shoes, Shoes2, Shirts, Shirts2)so there's no relation AT ALL, I'm just putting them side by side, unrelated data into the same view.

假设我在第一个视图中(ClothingID, Shoes, Shirts)和在第二个视图中都有(ClothingID, Shoes, Shirts)数据完全不相关，即使 ID 字段也不相关。我希望它们合并为 1 个单一视图以用于报告目的。所以第三个视图（我试图制作的那个）应该是这样的：(ClothingID, ClothingID2, Shoes, Shoes2, Shirts, Shirts2)所以根本没有关系，我只是把它们并排放置，不相关的数据到同一个视图中。

Any help would be strongly appreciated

任何帮助将不胜感激

回答by Erwin Brandstetter

You want to combinethe results, yet be able to tell the rows apart.
To duplicate all columns would be a bit of an overkill. Add a column with info about the source:

您想要组合结果，但又能够区分行。
复制所有列有点矫枉过正。添加包含有关来源信息的列：

SELECT 'v1'::text AS source, clothingid, shoes, shirts
FROM   view1

UNION  ALL
SELECT 'v2'::text AS source, clothingid, shoes, shirts
FROM   view2;

回答by Micha? Powaga

select v1.ClothingID, v2.ClothingID as ClothingID2, v1.Shoes, v2.Shoes as Shoes2,
    v1.Shirts, v2.Shirts as Shirts2
from (
    select *, row_number() OVER (ORDER BY ClothingID) AS row
    from view_1
) v1
full outer join (
    select *, row_number() OVER (ORDER BY ClothingID) AS row
    from view_2
) v2 on v1.row = v2.row

I think that full outer jointhat joins table using new unrelated column rowwill do the job.

我认为full outer join使用新的不相关列连接表row可以完成这项工作。

row_number()exists in PostgreSQL 8.4 and above.

row_number()存在于PostgreSQL 8.4 及更高版本中。

If you have lower version you can imitate row_number, example below. It's going to work only if ClothingIDis unique in a scope of view.

如果你有低版本你可以模仿row_number，下面的例子。只有ClothingID在视图范围内是唯一的，它才会起作用。

select v1.ClothingID, v2.ClothingID as ClothingID2, v1.Shoes, v2.Shoes as Shoes2,
    v1.Shirts, v2.Shirts as Shirts2
from (
    select *, (select count(*) from view_1 t1 
        where t1.ClothingID <= t.ClothingID) as row
    from view_1 t
) v1
full outer join (
    select *, (select count(*) from view_2 t2 
        where t2.ClothingID <= t.ClothingID) as row
    from view_2 t
) v2 on v1.row = v2.row

Addedafter comment:

评论后补充：

I've noticed and corrected mistake in preceding query.

我已经注意到并更正了前面查询中的错误。

I'll try to explain a bit. First of all we'll have to add a row numbers to both views to make sure that there are no gaps in id's. This is quite simple way:

我会试着解释一下。首先，我们必须向两个视图添加行号，以确保 id 中没有间隙。这是非常简单的方法：

select *, (select count(*) from view_1 t1 
    where t1.ClothingID <= t.ClothingID) as row
from view_1 t

This consist of two things, simple query selecting rows(*):

这包括两件事，简单的查询选择行（*）：

select *
from view_1 t

and correlated subquery (read more on wikipedia):

和相关子查询（在维基百科上阅读更多内容）：

(
    select count(*)
    from view_1 t1
    where t1.ClothingID <= t.ClothingID
) as row

This counts for each row of outer query (here it's (*)) preceding rows including self. So you might say count all rows which have ClothingIDless or equal like current row for each row in view. For unique ClothingID(that I've assumed) it gives you row numbering (ordered by ClothingID).

这对包括 self. 因此，您可能会说ClothingID对视图中的每一行计算与当前行相同或更少的所有行。对于独特的ClothingID（我已经假设），它为您提供行编号（按 排序ClothingID）。

Live example on data.stackexchange.com - row numbering.

data.stackexchange.com上的实时示例- 行编号。

After that we can use both subqueries with row numbers to join them (full outer joinon Wikipedia), live example on data.stackexchange.com - merge two unrelated views.

之后，我们可以使用带有行号的两个子查询来连接它们（full outer join在维基百科上），data.stackexchange.com上的实例 - 合并两个不相关的视图。

回答by Bohemian

If the views are unrelated, SQL will struggle to deal with it. You can do it, but there's a better and simpler way...

如果视图不相关，SQL 将很难处理它。你可以做到，但有一个更好更简单的方法......

I suggest merging them one after the other, rather than side-by-sideas you have suggested, ie a unionrather than a join:

我建议将它们一个接一个地合并，而不是像你建议的那样并排，即联合而不是连接：

select 'view1' as source, ClothingID, Shoes, Shirts
from view1
union all
select 'view2', ClothingID, Shoes, Shirts
from view2

This would be the usual approach for this kind of situation, and is simple to code and understand.

这将是这种情况的常用方法，并且易于编码和理解。

Note the use of UNION ALL, which preserves row order as selected and does not remove duplicates, as opposed to UNION, which sorts the rows and removes duplicates.

请注意使用UNION ALL，它保留选定的行顺序并且不删除重复项，而 则相反UNION，它对行进行排序并删除重复项。

Edited

已编辑

Added a column indicating which view the row came from.

添加了一列，指示该行来自哪个视图。

回答by N t

You could use Rownumber as a join parameter, and 2 temp tables?

您可以使用 Rownumber 作为连接参数和 2 个临时表吗？

So something like:

所以像：

    Insert @table1
    SELECT ROW_NUMBER() OVER (ORDER BY t1.Clothing_ID ASC) [Row_ID], Clothing_ID, Shoes, Shirts)
    FROM Table1 

    Insert @table2
    SELECT ROW_NUMBER() OVER (ORDER BY t1.Clothing_ID ASC)[RowID], Clothing_ID, Shoes, Shirts)
    FROM Table2

    Select  t1.Clothing_ID, t2.Clothing_ID,t1.Shoes,t2.Shoes, t1.Shirts,t2.Shirts
    from @table1 t1
    JOIN atable2 t2 on t1.Row_ID = t2.Row_ID

I think that should be roughly sensible. Make sure you are using the correct join so the full output for both queries appear

我认为这应该是大致合理的。确保使用正确的连接，以便显示两个查询的完整输出

e;fb

e;fb

回答by Basavaraj

You can try following:

您可以尝试以下操作：

SELECT *
FROM (SELECT row_number() over(), * FROM table1) t1
FULL JOIN (SELECT row_number() over(), * FROM table2) t2 using(row_number)