我想结合以下查询。 [英] 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,EStatusFROM 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屋!
查看全文