如何在SQL Server中加入两个查询的结果？ [英] How do I join the results of two queries in SQL Server?

问题描述

这是我今天的第二个SQL问题 - 我是一个新手上的DBA东西...

我试图加入一个复杂的sql查询将大约12个表中的数据合并到单个表中。虽然在数据库中有一对多的关系，但我知道每个关系的最大数目。

所以，我有（用堆栈溢出的帮助！）flatten我的数据库的第一级，并有一对查询，现在必须连接在一起：

（简略）

  SELECT 
 A.StudentId，C1.Topic AS SIoC1，C1.Level AS SIoCScore1 
 FROM评估A 
 LEFT JOIN关注C1 ON A.Id = Assessment_Id和C1.TopicNumber = 1 
 WHERE A.Type ='School'
 
 SELECT 
 A.StudentId，C1.Topic AS PIoC1，C1.Level AS PIoCScore1 
 FROM评估A 
 LEFT JOIN关注C1 ON A.Id = Assessment_Id和C1.TopicNumber = 1 
 WHERE A.Type ='Parent'
  

是否可以将查询命名为别名？

或者我还可以如何连接这两个查询，

  | A.Id | SIoC1 | SIoCScore1 | PIoC1 | PIoCScore1 | 
  

** UPDATE **
其背后的领域是进行评估，学校和家长都必须报告。因此，单行标识了同时具有学校和父级值的评估。

我使用SQL Server 2005。

谢谢！

* FURTHER UPDATE *
这个查询似乎做了...

  SELECT PreConcerns.StudentId，TIoC1，TIoCScore1，PIoC1，PIoCScore1 
 FROM 
评估PreConcerns 
 LEFT OUTER JOIN 
（
 SELECT 
 P.StudentId，C1.Topic AS TIoC1，C1.Level AS TIoCScore1 
 FROM评估P 
 LEFT JOIN注意C1 ON P.Id = C1.Assessment_Id和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评估P 
 LEFT JOIN关注C1 ON P.Id = C1.Assessment_Id和C1 .TopicNumber = 1 
 WHERE P.Type ='Parent'
）pcons 
 ON pcons.StudentId = PreConcerns.StudentId 
  

进一步更新（需要2！） **
m不确定我应该重新打开这个新的问题吗？）
我今天回来工作，找到我的上述'解决方案'没有解决问题 - 它为每个评估创建两行。

为了回顾一下，我有以下表格：

  （int：id，varchar（50）：name）
评估（int：id，date：date，int：StudentId，）
 Concern（int：id，int：assessment_id，varchar（20） topic，int：level）
  

所以对于系统中的每个学生，类型学校，一个类型为父。

我想创建一个单行，其中包含评估和关注：

（伪列：）

  | Assessment.StudentId | SchoolConcernTopic | SchoolConcernLevel | ParentConcernTopic | ParentConcernLevel | 
  

或从上述sql：

  | PreConcerns.StudentId | SIoC1 | SIoCScore1 | PIoC1 | PIoCScore1 | 
  

每个学生只填充一行，
我有这个工作与上面的SQL的结构，但它返回两行！

感谢一如既往！

div class =h2_lin>解决方案

这可以通过一个查询来完成。 IN子句只是一种奇怪的方式： A.Type ='School'OR A.Type ='Parent'

  SELECT 
 A.Id，C1.Topic AS PIoC1，C1.Level AS PIoCScore1 
 FROM评估A 
 LEFT JOIN关注C1 ON A. Id = Assessment_Id和C1.TopicNumber = 1 
 WHERE A.输入IN（'School'，'Parent'）
  

This is my second SQL question today - I'm a bit of a newbie on the DBA stuff...

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.

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.

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.

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:

| 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!!

Thanks as always!

解决方案

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'

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')

这篇关于如何在SQL Server中加入两个查询的结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！