我想结合以下查询。 [英] I want to combine following queries .

问题描述

;  WITH  MyCte（EType，EventDate） AS  
（
  SELECT  ' 考试',E.DateOfExam  FROM  tbl_ExamCreation E 
  UNION   ALL  
  SELECT  ' 讲座'，LS.StartDateTime  FROM  tbl_LectureScheduling LS 
）
 
  SELECT  EType，EventDate  FROM  MyCte  ORDER   BY   1   DESC  

------------------------------------

  SELECT  tbl_ExamCreation.Exam_ID，tbl_ExamCreation.ExamName，tbl_ExamAttendence.StudentID，tbl_ExamAttendence.Exam_ID  AS  Expr1，
 tbl_LectureAttendence.StudentID  AS  Expr2，tbl_LectureAttendence.LectureScheduleID，tbl_LectureAttendence.status，tbl_Lectures.LectureID，
 
 tbl_Lectures.LectureName，
 tbl_LectureScheduling.LectureScheduleID  AS  Expr3，tbl_LectureScheduling.LectureID  AS  Expr4，tbl_LectureScheduling.StartDateTime，
 tbl_ExamCreation.DateOfExam 
  FROM  tbl_LectureScheduling  INNER   JOIN  
 tbl_LectureAttendence  ON  tbl_LectureScheduling.LectureScheduleID = tbl_LectureAttendence。 LectureScheduleID  INNER   JOIN  
 tbl_Lectures  ON  tbl_LectureScheduling.LectureID = tbl_Lectures.LectureID  CROSS   JOIN  
 tbl_ExamCreation  INNER   JOIN  
 tbl_ExamAttendence  ON  tbl_ExamCreation.Exam_ID = tbl_ExamAttendence.Exam_ID 
  WHERE （tbl_ExamAttendence.StudentID =  1044 ） AND （tbl_LectureAttendence.StudentID =  1044 ）

解决方案

要组合查询，首先应该具有相同数量的列，并且它们在逻辑上应该是相同的类型，然后您可以使用 UNION 就像你在第一个块中一样。

;  WITH  MyCte（EType，EventDate，EName，EStatus） AS  
（
  SELECT  < span class =code-string>'  Exam'  AS  Expr1，E.DateOfExam ，E.ExamName，tbl_ExamAttendence.status 
  FROM  tbl_ExamCreation  AS  E  INNER   JOIN  
 tbl_ExamAttendence  ON  E.Exam_ID = tbl_ExamAttendence。 Exam_ID 
  WHERE （tbl_ExamAttendence.StudentID =  1044 ）
  UNION   ALL  
  SELECT  ' 讲座'  AS  Expr1，LS.StartDateTime，tbl_Lectures.LectureName ，tbl_LectureAttendence.status 
  FROM  tbl_LectureScheduling  AS  LS  INNER   JOIN  
 tbl_LectureAttendence  ON  LS.LectureScheduleID = tbl_LectureAttendence.LectureScheduleID  INNER   JOIN  
 tbl_Lectures  ON  LS.LectureID = tbl_Lectures.LectureID 
  WHERE （tbl_LectureAttendence.StudentID =  1044 ）
）
 
  SELECT   TOP   5  EType，EventDate，EName，EStatus  FROM  MyCte  ORDER   BY  EventDate  DESC  

;WITH MyCte(EType,EventDate) AS
(
SELECT 'Exam',E.DateOfExam FROM tbl_ExamCreation E
UNION ALL
SELECT 'Lecture',LS.StartDateTime FROM tbl_LectureScheduling LS
)
 
SELECT EType,EventDate FROM MyCte ORDER BY 1 DESC 

------------------------------------

SELECT     tbl_ExamCreation.Exam_ID, tbl_ExamCreation.ExamName, tbl_ExamAttendence.StudentID, tbl_ExamAttendence.Exam_ID AS Expr1, 
                      tbl_LectureAttendence.StudentID AS Expr2, tbl_LectureAttendence.LectureScheduleID, tbl_LectureAttendence.status, tbl_Lectures.LectureID, 

tbl_Lectures.LectureName,
                       tbl_LectureScheduling.LectureScheduleID AS Expr3, tbl_LectureScheduling.LectureID AS Expr4, tbl_LectureScheduling.StartDateTime, 
                      tbl_ExamCreation.DateOfExam
FROM         tbl_LectureScheduling INNER JOIN
                      tbl_LectureAttendence ON tbl_LectureScheduling.LectureScheduleID = tbl_LectureAttendence.LectureScheduleID INNER JOIN
                      tbl_Lectures ON tbl_LectureScheduling.LectureID = tbl_Lectures.LectureID CROSS JOIN
                      tbl_ExamCreation INNER JOIN
                      tbl_ExamAttendence ON tbl_ExamCreation.Exam_ID = tbl_ExamAttendence.Exam_ID
WHERE     (tbl_ExamAttendence.StudentID = 1044) AND (tbl_LectureAttendence.StudentID = 1044)

解决方案

To combine queries you should first have the same number of columns and they should logically be the same type, then you can use UNION like you have in the first "block".

;WITH MyCte(EType,EventDate,EName,EStatus) AS
(
SELECT     'Exam' AS Expr1, E.DateOfExam, E.ExamName,tbl_ExamAttendence.status
FROM         tbl_ExamCreation AS E INNER JOIN
                      tbl_ExamAttendence ON E.Exam_ID = tbl_ExamAttendence.Exam_ID
WHERE     (tbl_ExamAttendence.StudentID = 1044)
UNION ALL
SELECT     'Lecture' AS Expr1, LS.StartDateTime, tbl_Lectures.LectureName,tbl_LectureAttendence.status
FROM         tbl_LectureScheduling AS LS INNER JOIN
                      tbl_LectureAttendence ON LS.LectureScheduleID = tbl_LectureAttendence.LectureScheduleID INNER JOIN
                      tbl_Lectures ON LS.LectureID = tbl_Lectures.LectureID
WHERE     (tbl_LectureAttendence.StudentID = 1044)
)

SELECT TOP 5 EType,EventDate,EName,EStatus FROM MyCte ORDER BY EventDate DESC

这篇关于我想结合以下查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！