如何在 SQL Server 中加入两个查询的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4875696/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I join the results of two queries in SQL Server?
提问by laura
This is my second SQL question today - I'm a bit of a newbie on the DBA stuff...
这是我今天的第二个 SQL 问题——我是 DBA 方面的新手……
I am trying to join together a complex sql query to merge the data in about 12 tables into 1 single table. Although there are one to many relationships in the database, I know what the maximum numbers of each are to be.
我正在尝试将一个复杂的 sql 查询连接在一起，以将大约 12 个表中的数据合并为 1 个单表。虽然数据库中有一对多的关系，但我知道每个关系的最大数量是多少。
So, I have (with stack overflow's help!) flatten out the first level of my database, and have a pair of queries, which must now be joined together:
所以，我有（在堆栈溢出的帮助下！）展平了我的数据库的第一级，并有一对查询，现在必须连接在一起：
(abridged)
（简略）
SELECT
A.StudentId, C1.Topic AS SIoC1, C1.Level AS SIoCScore1
FROM Assessment A
LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type = 'School'
SELECT
A.StudentId, C1.Topic AS PIoC1, C1.Level AS PIoCScore1
FROM Assessment A
LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type = 'Parent'
Is it possible to name queries as aliases?
Or how else can I join these two queries so the output is like:
是否可以将查询命名为别名？
或者我还能如何加入这两个查询，以便输出如下：
| A.Id | SIoC1 | SIoCScore1 | PIoC1 | PIoCScore1 |
** UPDATE ** The domain behind it is that an Assessment is carried out, on which both the school and the parent must report. So the single row identifies an assessment which has both School and Parent values on it.
** 更新 ** 其背后的领域是进行评估，学校和家长都必须报告。因此，单行标识了一个评估，其中包含学校和家长值。
I'm using SQL Server 2005.
我正在使用 SQL Server 2005。
Thanks!
谢谢！
* FURTHER UPDATE *This query seems to do the job...
* 进一步更新 *这个查询似乎完成了这项工作......
SELECT PreConcerns.StudentId, TIoC1, TIoCScore1, PIoC1, PIoCScore1
FROM
Assessment PreConcerns
LEFT OUTER JOIN
(
SELECT
P.StudentId, C1.Topic AS TIoC1, C1.Level AS TIoCScore1
FROM Assessment P
LEFT JOIN Concern C1 ON P.Id = C1.Assessment_Id and C1.TopicNumber = 1
WHERE P.Type = 'School'
) scons
ON scons.StudentId= PreConcerns.StudentId
LEFT OUTER JOIN
(
SELECT
P.StudentId, C1.Topic AS PIoC1, C1.Level AS PIoCScore1
FROM Assessment P
LEFT JOIN Concern C1 ON P.Id = C1.Assessment_Id and C1.TopicNumber = 1
WHERE P.Type = 'Parent'
) pcons
ON pcons.StudentId = PreConcerns.StudentId
FURTHER UPDATE (take 2!) **(I'm not sure if I should reopen this as a new question??!) I got back to work today, to find my above 'solution' didn't quite solve the problem - it creates two rows for each assessment.
进一步更新（采取 2！）**（我不确定我是否应该将其作为一个新问题重新打开？？！）我今天回去工作，发现我上面的“解决方案”并没有完全解决问题-它为每个评估创建两行。
So to recap, I have the following tables:
回顾一下，我有以下表格：
Student (int:id, varchar(50):name)
Assessment (int:id, date:date, int:StudentId, )
Concern (int:id, int:assessment_id, varchar(20):topic, int:level)
So for each student in the system there is exactly two Assessments - one with type 'School', and one with type 'Parent'.
因此，对于系统中的每个学生，恰好有两个评估 - 一个类型为“学校”，一个类型为“家长”。
I want to create a single row which combines the assessments and concerns:
我想创建一个结合评估和关注点的单行：
(pseudo columns:)
（伪列：）
| Assessment.StudentId | SchoolConcernTopic | SchoolConcernLevel | ParentConcernTopic | ParentConcernLevel |
or from the sql above:
或者从上面的sql：
| PreConcerns.StudentId | SIoC1 | SIoCScore1 | PIoC1 | PIoCScore1 |
With only one row populated per student, which combines both assessments. I have the structure for this working with the above SQL, but it returns two rows! And I can't work out how to update this to only return one - any help gratefully received!!
每个学生只填充一行，它结合了两种评估。我有使用上述 SQL 的结构，但它返回两行！而且我不知道如何更新它以只返回一个 - 感谢收到任何帮助！
Thanks as always!
一如既往的感谢！
回答by Joe Stefanelli
This can be done with just one query. The IN clause is just a fancy way of saying A.Type = 'School' OR A.Type = 'Parent'
这可以通过一个查询来完成。IN 子句只是一种奇特的说法A.Type = 'School' OR A.Type = 'Parent'
SELECT
A.Id, C1.Topic AS PIoC1, C1.Level AS PIoCScore1
FROM Assessment A
LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type IN ('School','Parent')
回答by Tony
Make sure the field names you want are specified on the first query then UNION ALLthe two queries; if you want one set of results tagged on the end of the other.
确保在第一个查询中指定了您想要的字段名称，然后UNION ALL在两个查询中指定；如果您想将一组结果标记在另一组结果的末尾。
Ignore my first answer, I'm taking rubbish as that won't get the result you want. Sorry.
忽略我的第一个答案，我正在接受垃圾，因为这不会得到您想要的结果。对不起。
If you want the output to have repeated columns, where SIoC1and PIoC1are essentially the same but with a different name depending if the row is from a 'School' or 'Parent' then your result set will end up with NULLS all over it, e.g. (random data used as you don't provide sample data)
如果您希望输出具有重复的列，其中SIoC1和PIoC1基本上相同但名称不同，具体取决于该行是来自“学校”还是“父母”，那么您的结果集将最终以 NULLS 结束，例如（由于您不提供样本数据而使用的随机数据）
| A.Id | SIoC1 | SIoCScore1 | PIoC1 | PIoCScore1 |
1 A 10 NULL NULL -- Row type 'School'
2 NULL NULL B 20 -- Row type 'Parent'
etc. etc.
If the data is stored in the same table, with the same field names use some other field to distinguish the rows, such as Type, and display that in the results.
如果数据存储在同一个表中，具有相同的字段名称，则使用其他一些字段来区分行，例如Type，并在结果中显示。
回答by Justin Morgan
One query may be enough:
一个查询可能就足够了：
SELECT
A.Id, A.Type, C1.Topic AS IoC1, C1.Level AS IoCScore1
FROM Assessment A
LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type IN ('School', 'Parent')
回答by Andriy M
I think this query must return the same result as the one in your 'Further Update' section.
我认为此查询必须返回与“进一步更新”部分中的结果相同的结果。
The P.Type = ...conditions have been moved from WHERE to ON sections, so you can get the desired effect without having to resort to joining subselects.
该P.Type = ...条件已移动从哪里上的部分的，这样你就可以得到想要的效果，而不必求助于加入子查询。
SELECT
P.StudentId,
CS.Topic AS TIoC1,
CS.Level AS TIoCScore1,
CP.Topic AS PIoC1,
CP.Level AS PIoCScore1
FROM
Assessment P
LEFT JOIN Concern CS ON P.Id = CS.Assessment_Id
and CS.TopicNumber = 1 and P.Type = 'School'
LEFT JOIN Concern CP ON P.Id = CP.Assessment_Id
and CP.TopicNumber = 1 and P.Type = 'Parent'
EDIT: Seems like grouping should help there. (I'm leaving my initial query so it's easier to see the necessary change for the updated requirement.)
编辑：似乎分组应该在那里有所帮助。（我将离开我的初始查询，以便更容易查看更新需求的必要更改。）
SELECT
P.StudentId,
MAX(CS.Topic) AS TIoC1,
MAX(CS.Level) AS TIoCScore1,
MAX(CP.Topic) AS PIoC1,
MAX(CP.Level) AS PIoCScore1
FROM
Assessment P
LEFT JOIN Concern CS ON P.Id = CS.Assessment_Id
and CS.TopicNumber = 1 and P.Type = 'School'
LEFT JOIN Concern CP ON P.Id = CP.Assessment_Id
and CP.TopicNumber = 1 and P.Type = 'Parent'
GROUP BY P.StudentId
回答by John Pappin
You could select it all in one query and use the CASEstatement to only populate the values where applicable
您可以在一个查询中全部选择并使用该CASE语句仅填充适用的值
SELECT
A.Id,
CASE A.Type
WHEN 'School'
THEN C1.Topic
ELSE NULL
END
AS SIoC1,
CASE A.Type
WHEN 'School'
THEN C1.Level
ELSE NULL
END
AS SIoCScore1,
CASE A.Type
WHEN 'Parent'
THEN C1.Topic
ELSE NULL
END
AS PIoC1,
CASE A.Type
WHEN 'Parent'
THEN C1.Level
ELSE NULL
END AS PIoCScore1
FROM Assessment A
LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type IN ('School', 'Parent')
Edit:Preferably you would store the Type column so you could distinguish between the two types of data you wanted and then you would not have to repeat the columns as in the example above. If you are creating a table then for it to conform to some form of normalization you wouldn't want to have repeated columns, however if you are using this select statement in a VIEW then it would be acceptable to do so.
编辑：您最好存储类型列，以便您可以区分您想要的两种类型的数据，然后您就不必像上面的示例那样重复列。如果您正在创建一个表，那么为了使其符合某种形式的规范化，您不希望有重复的列，但是如果您在 VIEW 中使用此 select 语句，那么这样做是可以接受的。
回答by shubham sharma
SELECT
A.Id,
CASE A.Type
WHEN 'School'
THEN C1.Topic
ELSE NULL
END
AS SIoC1,
CASE A.Type
WHEN 'School'
THEN C1.Level
ELSE NULL
END
AS SIoCScore1,
CASE A.Type
WHEN 'Parent'
THEN C1.Topic
ELSE NULL
END
AS PIoC1,
CASE A.Type
WHEN 'Parent'
THEN C1.Level
ELSE NULL
END AS PIoCScore1
FROM Assessment A
LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type IN ('School', 'Parent')
回答by Amrit
Try this solution
试试这个解决方案
SELECT * INTO #School FROM
(
SELECT
A.StudentId, C1.Topic AS SIoC1, C1.Level AS SIoCScore1
FROM Assessment A
LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type = 'School'
)school
SELECT * INTO #Parent FROM
(
SELECT
A.StudentId, C1.Topic AS PIoC1, C1.Level AS PIoCScore1
FROM Assessment A
LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type = 'Parent'
)parent
SELECT
SL.StudentId
,SL.SIoC1
,SL.SIoCScore1
,PT.PIoC1
,PT.PIoCScore1
From #School SL
LEFT JOIN #Parent PT ON PT.StudentId = SL.StudentId
回答by Vasile Tomoiaga
The result column names must be the same (the alias used) and the same data types:
结果列名称必须相同（使用的别名）和相同的数据类型：
SELECT
A.Id, C1.Topic AS Topic, C1.Level AS Level
FROM Assessment A
LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type = 'School'
UNION
SELECT
A.Id, C1.Topic AS Topic, C1.Level AS Level
FROM Assessment A
LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type = 'Parent'
