Is it possible to UNION queries from tables or views that don't have any result in common? What I'm trying to do is combine data from different views into one result. I have
来自没有任何共同结果的表或视图的UNION查询是否可能?我要做的是将来自不同视图的数据合并为一个结果。我有
select a,b,null,c from VIEW1, VIEW2 where VIEW1.a = VIEW2.a
UNION
select null,null,z,null from VIEW3
I would like the result to be a,b,z,c. Is this where I would use select from? What does that look like?
我希望结果是a,b,z,c。这是我要用的地方吗?那是什么样的?
select ? from (
select a,b,null,c from VIEW1, VIEW2 where VIEW1.a = VIEW2.a
UNION
select null,null,z,null from VIEW3)
I'm using MS SQL Server and the views do not have primary keys. Thanks so much.
我正在使用MS SQL Server,并且视图没有主键。非常感谢。
7 个解决方案
#1
If I understand your question, you're probably getting results like this:
如果我理解你的问题,你可能会得到这样的结果:
a1, b1, null, c1
a2, b2, null, c2
a3, b2, null, c3
null, null, z1, null
null, null, z2, null
null, null, z3, nul
l
.. but what you're trying to get are results like this:
..但你想要得到的结果是这样的:
a1, b1, z1, c1
a2, b2, z2, c2
a3, b2, z3, c3
Do I understand the problem correctly?
我能正确理解问题吗?
If this is correct, you'll need to have a way to join these subqueries together, so that you can tell SQL that the 1's go together, and the 2's go together, and so on.
如果这是正确的,你需要有一种方法将这些子查询连接在一起,这样你就可以告诉SQL 1是一起去的,2是一起去的,依此类推。
#2
A union concatenates result sets, it does not combine them.
联合连接结果集,它不会将它们组合在一起。
So what you will get from your first query is this:
那么你从第一个查询得到的是:
a b (null) c
(null) (null) z (null)
If you want to combine them, you'll have to join them, and then you need to have something in common, or you'll have to combine the data in the program.
如果你想要将它们组合在一起,你就必须加入它们,然后你需要有一些共同点,或者你必须将程序中的数据组合起来。
Do you only have 1 row from each?
你们每个只有一排吗?
If so, then if the pattern above is always going to be like that, this would work:
如果是这样,那么如果上面的模式总是那样,那么这将起作用:
SELECT SQ1.a, SQ1.b, SQ2.z, SQ1.c
FROM (
SELECT 1 k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT 1 k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
However, if you don't know whether View3.a has a value or View1.a has a value, and you want the one from the first query if there is a value 3, then this would work:
但是,如果您不知道View3.a是否有值,或者View1.a是否有值,并且您希望第一个查询中的值有值3,那么这将起作用:
SELECT COALESCE(SQ1.a, SQ2.a) a, COALESCE(SQ1.b, SQ2.b) b,
COALESCE(SQ1.z, SQ2.z) z, COALESCE(SQ1.c, SQ2.c) c
FROM (
SELECT 1 k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT 1 k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
But, and there's a big BUT in here. If you have more than one row in either of the views, you'll going to end up with data that doesn't belong together. In that case, you must have something in common.
但是,这里有一个很大的优点。如果任一视图中有多行,您将最终得到不属于一起的数据。在这种情况下,你必须有一些共同点。
Here's the full code that I tried, along with the results:
这是我尝试的完整代码以及结果:
USE master
GO
DROP DATABASE TestDB
GO
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE View1
(
a INT,
b INT,
c INT
)
GO
CREATE TABLE View2
(
a INT,
z INT
)
GO
CREATE TABLE View3
(
z INT
)
GO
INSERT INTO View1 (a, b, c) VALUES (10, 20, 30)
GO
INSERT INTO View2 (a, z) VALUES (10, 40)
GO
INSERT INTO View3 (z) VALUES (50)
GO
SELECT View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a
UNION
SELECT NULL a, NULL b, z, NULL c
FROM View3
SELECT SQ1.a, SQ1.b, SQ2.z, SQ1.c
FROM (
SELECT 1 k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT 1 k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
SELECT COALESCE(SQ1.a, SQ2.a) a, COALESCE(SQ1.b, SQ2.b) b,
COALESCE(SQ1.z, SQ2.z) z, COALESCE(SQ1.c, SQ2.c) c
FROM (
SELECT 1 k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT 1 k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
Results:
a b z c
----------- ----------- ----------- -----------
NULL NULL 50 NULL
10 20 NULL 30
(2 row(s) affected)
a b z c
----------- ----------- ----------- -----------
10 20 50 30
(1 row(s) affected)
a b z c
----------- ----------- ----------- -----------
10 20 50 30
(1 row(s) affected)
If you add one single row to View3, like this:
如果向View3添加一行,如下所示:
INSERT INTO View3 (z) VALUES (51)
Then you'll get these results, notice the doubled rows:
然后你会得到这些结果,注意加倍的行:
a b z c
----------- ----------- ----------- -----------
NULL NULL 50 NULL
NULL NULL 51 NULL
10 20 NULL 30
(3 row(s) affected)
a b z c
----------- ----------- ----------- -----------
10 20 50 30
10 20 51 30
(2 row(s) affected)
a b z c
----------- ----------- ----------- -----------
10 20 50 30
10 20 51 30
(2 row(s) affected)
#3
If I understand this: are you trying to 'collapse' your results and get rid of all the NULLs? If so, will ever result from view3 correspond to a result from view1/view2? If so, what is the relationship? If not, do the number of results at least match?
如果我理解这一点:你是否试图“折叠”你的结果并摆脱所有的NULL?如果是这样,view3会产生对应于view1 / view2的结果吗?如果是这样,那么关系是什么?如果没有,结果的数量是否至少匹配?
#4
For multiple records, ROW_NUMBER() approach worked for me. The Select 1 k approach was returning a Cartesian product.
对于多个记录,ROW_NUMBER()方法对我有效。选择1 k方法返回笛卡尔积。
SELECT SQ1.a, SQ1.b, SQ2.z, SQ1.c
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY a) k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY b) k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
#5
Can it be that you are looking for something like this? (just a wild guess)
难道你正在寻找这样的东西吗? (只是一个疯狂的猜测)
SELECT
VIEW1.a,
VIEW1.b,
(SELECT TOP 1 z FROM VIEW3) AS z,
VIEW2.c
FROM
VIEW1, VIEW2
WHERE
VIEW1.a = VIEW2.a
#6
The results from any sql select, including a union, are a static set of columns. (No polymorphism.)
任何sql select的结果(包括union)都是一组静态列。 (没有多态性。)
But you don't have to use all of the columns in each row, you use null values in the rows where the column doesn't have a value. I also suggest that you include a 'type' row so the client can tell the type (and interesting columns) for a given row.
但是您不必使用每一行中的所有列,而是在列没有值的行中使用空值。我还建议您包含一个'type'行,以便客户端可以告诉给定行的类型(和有趣的列)。
Example:
(Select
'room' as view_type
, rooms.room as room
, NULL as color
From rooms
)
UNION ALL
(Select
'color' as view_type
, NULL as room
, colors.color as color
From colors
)
#7
You actually want a cross join:
你真的想要一个交叉连接:
select v1.a,v1.b,VIEW3.z,v1.c from (SELECT a,b,c, FROM VIEW1, VIEW2 where VIEW1.a = VIEW2.a) as v1 CROSS JOIN VIEW3
选择v1.a,v1.b,VIEW3.z,v1.c from(SELECT a,b,c,FROM VIEW1,VIEW2,其中VIEW1.a = VIEW2.a)为v1 CROSS JOIN VIEW3
#1
If I understand your question, you're probably getting results like this:
如果我理解你的问题,你可能会得到这样的结果:
a1, b1, null, c1
a2, b2, null, c2
a3, b2, null, c3
null, null, z1, null
null, null, z2, null
null, null, z3, nul
l
.. but what you're trying to get are results like this:
..但你想要得到的结果是这样的:
a1, b1, z1, c1
a2, b2, z2, c2
a3, b2, z3, c3
Do I understand the problem correctly?
我能正确理解问题吗?
If this is correct, you'll need to have a way to join these subqueries together, so that you can tell SQL that the 1's go together, and the 2's go together, and so on.
如果这是正确的,你需要有一种方法将这些子查询连接在一起,这样你就可以告诉SQL 1是一起去的,2是一起去的,依此类推。
#2
A union concatenates result sets, it does not combine them.
联合连接结果集,它不会将它们组合在一起。
So what you will get from your first query is this:
那么你从第一个查询得到的是:
a b (null) c
(null) (null) z (null)
If you want to combine them, you'll have to join them, and then you need to have something in common, or you'll have to combine the data in the program.
如果你想要将它们组合在一起,你就必须加入它们,然后你需要有一些共同点,或者你必须将程序中的数据组合起来。
Do you only have 1 row from each?
你们每个只有一排吗?
If so, then if the pattern above is always going to be like that, this would work:
如果是这样,那么如果上面的模式总是那样,那么这将起作用:
SELECT SQ1.a, SQ1.b, SQ2.z, SQ1.c
FROM (
SELECT 1 k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT 1 k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
However, if you don't know whether View3.a has a value or View1.a has a value, and you want the one from the first query if there is a value 3, then this would work:
但是,如果您不知道View3.a是否有值,或者View1.a是否有值,并且您希望第一个查询中的值有值3,那么这将起作用:
SELECT COALESCE(SQ1.a, SQ2.a) a, COALESCE(SQ1.b, SQ2.b) b,
COALESCE(SQ1.z, SQ2.z) z, COALESCE(SQ1.c, SQ2.c) c
FROM (
SELECT 1 k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT 1 k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
But, and there's a big BUT in here. If you have more than one row in either of the views, you'll going to end up with data that doesn't belong together. In that case, you must have something in common.
但是,这里有一个很大的优点。如果任一视图中有多行,您将最终得到不属于一起的数据。在这种情况下,你必须有一些共同点。
Here's the full code that I tried, along with the results:
这是我尝试的完整代码以及结果:
USE master
GO
DROP DATABASE TestDB
GO
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE View1
(
a INT,
b INT,
c INT
)
GO
CREATE TABLE View2
(
a INT,
z INT
)
GO
CREATE TABLE View3
(
z INT
)
GO
INSERT INTO View1 (a, b, c) VALUES (10, 20, 30)
GO
INSERT INTO View2 (a, z) VALUES (10, 40)
GO
INSERT INTO View3 (z) VALUES (50)
GO
SELECT View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a
UNION
SELECT NULL a, NULL b, z, NULL c
FROM View3
SELECT SQ1.a, SQ1.b, SQ2.z, SQ1.c
FROM (
SELECT 1 k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT 1 k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
SELECT COALESCE(SQ1.a, SQ2.a) a, COALESCE(SQ1.b, SQ2.b) b,
COALESCE(SQ1.z, SQ2.z) z, COALESCE(SQ1.c, SQ2.c) c
FROM (
SELECT 1 k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT 1 k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
Results:
a b z c
----------- ----------- ----------- -----------
NULL NULL 50 NULL
10 20 NULL 30
(2 row(s) affected)
a b z c
----------- ----------- ----------- -----------
10 20 50 30
(1 row(s) affected)
a b z c
----------- ----------- ----------- -----------
10 20 50 30
(1 row(s) affected)
If you add one single row to View3, like this:
如果向View3添加一行,如下所示:
INSERT INTO View3 (z) VALUES (51)
Then you'll get these results, notice the doubled rows:
然后你会得到这些结果,注意加倍的行:
a b z c
----------- ----------- ----------- -----------
NULL NULL 50 NULL
NULL NULL 51 NULL
10 20 NULL 30
(3 row(s) affected)
a b z c
----------- ----------- ----------- -----------
10 20 50 30
10 20 51 30
(2 row(s) affected)
a b z c
----------- ----------- ----------- -----------
10 20 50 30
10 20 51 30
(2 row(s) affected)
#3
If I understand this: are you trying to 'collapse' your results and get rid of all the NULLs? If so, will ever result from view3 correspond to a result from view1/view2? If so, what is the relationship? If not, do the number of results at least match?
如果我理解这一点:你是否试图“折叠”你的结果并摆脱所有的NULL?如果是这样,view3会产生对应于view1 / view2的结果吗?如果是这样,那么关系是什么?如果没有,结果的数量是否至少匹配?
#4
For multiple records, ROW_NUMBER() approach worked for me. The Select 1 k approach was returning a Cartesian product.
对于多个记录,ROW_NUMBER()方法对我有效。选择1 k方法返回笛卡尔积。
SELECT SQ1.a, SQ1.b, SQ2.z, SQ1.c
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY a) k, View1.a, b, NULL z, c
FROM View1 INNER JOIN View2 ON View1.a = View2.a) SQ1
INNER JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY b) k, NULL a, NULL b, z, NULL c
FROM View3) SQ2 ON SQ1.k = SQ2.k
#5
Can it be that you are looking for something like this? (just a wild guess)
难道你正在寻找这样的东西吗? (只是一个疯狂的猜测)
SELECT
VIEW1.a,
VIEW1.b,
(SELECT TOP 1 z FROM VIEW3) AS z,
VIEW2.c
FROM
VIEW1, VIEW2
WHERE
VIEW1.a = VIEW2.a
#6
The results from any sql select, including a union, are a static set of columns. (No polymorphism.)
任何sql select的结果(包括union)都是一组静态列。 (没有多态性。)
But you don't have to use all of the columns in each row, you use null values in the rows where the column doesn't have a value. I also suggest that you include a 'type' row so the client can tell the type (and interesting columns) for a given row.
但是您不必使用每一行中的所有列,而是在列没有值的行中使用空值。我还建议您包含一个'type'行,以便客户端可以告诉给定行的类型(和有趣的列)。
Example:
(Select
'room' as view_type
, rooms.room as room
, NULL as color
From rooms
)
UNION ALL
(Select
'color' as view_type
, NULL as room
, colors.color as color
From colors
)
#7
You actually want a cross join:
你真的想要一个交叉连接:
select v1.a,v1.b,VIEW3.z,v1.c from (SELECT a,b,c, FROM VIEW1, VIEW2 where VIEW1.a = VIEW2.a) as v1 CROSS JOIN VIEW3
选择v1.a,v1.b,VIEW3.z,v1.c from(SELECT a,b,c,FROM VIEW1,VIEW2,其中VIEW1.a = VIEW2.a)为v1 CROSS JOIN VIEW3