从两个表中获取所有严格匹配的记录 [英] Get all the strictly matching records from two tables

问题描述

我有以下类型的表格





DECLARE @tbStudent TABLE（StudentId INT，StudentName NVARCHAR（100））

INSERT INTO @tbStudent（StudentId，StudentName）

VALUES（101，'James'），（102，'Smith'），（103，'Jackson'），（104， 'Peter'）;



DECLARE @tbStudentCourseDetails TABLE（StudentId INT，CourseId INT）

INSERT INTO @tbStudentCourseDetails（StudentId，CourseId）< br $>
价值（101,1），（101,3），（101,6），

（102,4），（102,3），

（103,5），（103,3），

（104,1），（104,3），

（105,1）;





DECLARE @tbCourseDone TABLE（CourseId INT）

INSERT INTO @tbCourseDone（CourseId）

VALUES（1），（3）;



SELECT * FROM @tbStudent

SELECT * FROM @tbStudentCourseDetails

SELECT * FROM @tbCourseDone





现在我需要获得StudentId，StudentName，CourseId记录已完成@tbCourseDone表中定义的所有课程，即1和3



输出应为

StudentId StudentName CourseId

101 James 1

101 James 3

104彼得1

104彼得3



我尝试过的事情：



我试过In运算符，但它正在获取学生的任何课程匹配的记录



我也试过以下查询并得到我想要的结果：



DECLARE @RecCount int =（ SELECT count（*）FROM @tbCourseDone）



; with myCTE

AS

（

SELECT EM.StudentId

FROM @tbStudent EM

INNER JOIN @tbStudentCourseDetails teld ON EM.StudentId = teld.StudentId

INNER加入@tbCourseDone t ON t.CourseId = teld.CourseId

GROUP BY EM.StudentId HAVING COUNT（Em.StudentId）= @ RecCount

）



SELECT EM.StudentId，StudentN ame，teld.CourseId

FROM @tbStudent EM

INNER JOIN @tbStudentCourseDetails teld ON EM.StudentId = teld.StudentId

INNER JOIN @tbCourseDone t ON t.CourseId = teld.CourseId

INNER JOIN myCTE cte ON cte.StudentId = EM.StudentId





但它不是优化查询，我认为可以缩短。请帮助我得到这个结果。

I have following type of tables


DECLARE @tbStudent TABLE (StudentId INT, StudentName NVARCHAR(100))
INSERT INTO @tbStudent (StudentId , StudentName)
VALUES (101,'James'),(102,'Smith'),(103,'Jackson'),(104,'Peter');

DECLARE @tbStudentCourseDetails TABLE (StudentId INT, CourseId INT)
INSERT INTO @tbStudentCourseDetails (StudentId , CourseId)
VALUES (101,1),(101,3),(101,6),
(102,4),(102,3),
(103,5),(103,3),
(104,1),(104,3),
(105,1);


DECLARE @tbCourseDone TABLE (CourseId INT)
INSERT INTO @tbCourseDone(CourseId)
VALUES (1),(3);

SELECT * FROM @tbStudent
SELECT * FROM @tbStudentCourseDetails
SELECT * FROM @tbCourseDone


Now i need to get the StudentId, StudentName,CourseId records where Student has done all the courses defined in @tbCourseDone table i.e. 1 and 3

Output should be
StudentId StudentName CourseId
101 James 1
101 James 3
104 Peter 1
104 Peter 3

What I have tried:

I tried In operator but it is fetching the records of the student where any course is matched

I also tried below query and got the result i want:

DECLARE @RecCount int=(SELECT count(*) FROM @tbCourseDone)

;WITH myCTE
AS
(
SELECT EM.StudentId
FROM @tbStudent EM
INNER JOIN @tbStudentCourseDetails teld ON EM.StudentId = teld.StudentId
INNER JOIN @tbCourseDone t ON t.CourseId=teld.CourseId
GROUP BY EM.StudentId HAVING COUNT(Em.StudentId)=@RecCount
)

SELECT EM.StudentId,StudentName,teld.CourseId
FROM @tbStudent EM
INNER JOIN @tbStudentCourseDetails teld ON EM.StudentId = teld.StudentId
INNER JOIN @tbCourseDone t ON t.CourseId=teld.CourseId
INNER JOIN myCTE cte ON cte.StudentId=EM.StudentId


But it is not optimized query and can be shorten i think. Please help me in getting this result.

推荐答案

可能有一种更简单的方法可以做到这一点，但首先想到的是一个双 NOT EXISTS test：

There might be a simpler way to do this, but the first thing that comes to mind is a double NOT EXISTS test:

SELECT
    S.StudentId,
    S.StudentName,
    C.CourseId
FROM
    @tbStudent As S
    INNER JOIN @tbStudentCourseDetails As C
    ON C.StudentId = S.StudentId
WHERE
    -- Only return details for the required courses:
    Exists
    (
        SELECT 1
        FROM @tbCourseDone As D
        WHERE D.CourseId = C.CourseId
    )
And
    -- Exclude students who haven't done all required courses:
    Not Exists
    (
        SELECT 1
        FROM @tbCourseDone As D
        WHERE Not Exists
        (
            SELECT 1
            FROM @tbStudentCourseDetails As C2
            WHERE C2.CourseId = D.CourseId
            And C2.StudentId = S.StudentId
        )
    )
ORDER BY
    S.StudentId,
    C.CourseId
;

这篇关于从两个表中获取所有严格匹配的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！