嘗試加入多個表的問題

有問題的學校有兩種不同的方式在學年中期提拔有天賦的學生。第一個簡單地將他們提升到下一個年級；另一種是一次“雙錄取”他們兩個年級。然而，當我們給學生他們的時間表時，我們只想報告學生注冊的最高年級。查詢應回傳任何日期的所述資訊。如果您要對“2013 年 1 月 15 日”的示例資料運行此查詢，它將回傳：

| name  | grade | className |
|-------|-------|-----------|
| Adam  | 8     | Science   |
| Adam  | 8     | English   |
| Alice | 7     | English   |
| Alice | 7     | History   |
| Jack  | 7     | English   |
| Jack  | 7     | Math      |

以下是表格的結構：

CREATE TABLE dbo.[Class]
(
    classID INT NOT NULL PRIMARY KEY,
    name VARCHAR(50)
)

CREATE TABLE dbo.[Person]
(
    personID INT NOT NULL PRIMARY KEY,
    name VARCHAR(50),
    parentID1 INT,
    parentID2 INT,
    gender VARCHAR(10)
)


CREATE TABLE dbo.AttendanceRecord
(
    attendanceID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    personID INT,
    classID INT,
    [date] SMALLDATETIME,
    CONSTRAINT FK_attendancePerson FOREIGN KEY (personID) REFERENCES Person(personID),
    CONSTRAINT FK_attendanceClass FOREIGN KEY (classID) REFERENCES Class(classID)
)


CREATE TABLE dbo.Schedule
(
    personID INT,
    classID INT,
    CONSTRAINT FK_schedulePerson FOREIGN KEY (personID) REFERENCES Person(personID),
    CONSTRAINT FK_scheduleClass FOREIGN KEY (classID) REFERENCES Class(classID)
)

CREATE TABLE dbo.Enrollment 
(
    personID INT,
    startDate SMALLDATETIME,
    endDate SMALLDATETIME,
    grade INT,
    CONSTRAINT FK_enrollmentPerson FOREIGN KEY (personID) REFERENCES Person(personID)
)

這是我正在使用的示例資料：

INSERT INTO dbo.AttendanceRecord
SELECT 25,123,'03/21/2013'
UNION
SELECT 25,456,'03/21/2013'
UNION
SELECT 25,789,'03/21/2013'
UNION
SELECT 13,147,'03/21/2013'
UNION
SELECT 25,123,'03/22/2013'
UNION
SELECT 82,147,'03/22/2013'
UNION
SELECT 82,456,'03/22/2013'
UNION
SELECT 13,147,'03/23/2013'
UNION
SELECT 25,456,'03/23/2013'
UNION
SELECT 13,456,'03/24/2013'
UNION
SELECT 25,456,'03/28/2013'


INSERT INTO dbo.Class
SELECT 123, 'Math'
UNION
SELECT 456, 'English'
UNION
SELECT 789, 'History'
UNION 
SELECT 258, 'Lunch'
UNION
SELECT 147, 'Science'

INSERT INTO dbo.Person
SELECT 25, 'Jack', 255, NULL, 'Male'
UNION
SELECT 13, 'Jill', 100, 101, 'Female'
UNION
SELECT 82, 'Adam', NULL, 200, 'Male'
UNION
SELECT 100, 'Jeff', NULL, NULL, 'Male'
UNION
SELECT 101, 'June', NULL, NULL, 'Female'
UNION
SELECT 200, 'Shirley', NULL, NULL, 'Female'
UNION
SELECT 155, 'Alice', 255, NULL, 'Female'
UNION
SELECT 255, 'Tom', NULL, NULL, 'Male'
UNION
SELECT 19, 'Jake', 100,101, 'Male'


INSERT INTO dbo.Schedule
SELECT 25,123
UNION
SELECT 25,456
UNION
SELECT 25,789
UNION
SELECT 13,147
UNION
SELECT 82,147
UNION
SELECT 82,456
UNION
SELECT 13,456
UNION
SELECT 19,123
UNION
SELECT 19,789
UNION
SELECT 155,456
UNION
SELECT 155,789


INSERT INTO dbo.Enrollment
SELECT 25, '09/01/2012','05/31/2013', 7
UNION
SELECT 25, '03/01/2013','05/31/2013', 8
UNION
SELECT 13, '09/01/2012','05/31/2013', 7
UNION
SELECT 82, '09/01/2012', '12/31/2012', 7
UNION
SELECT 82, '01/01/2013', '05/31/2013', 8
UNION
SELECT 19, '09/01/2012', '05/31/2013', 8
UNION
SELECT 155, '09/01/2012','05/31/2013', 7 

我嘗試使用多個連接來實作此查詢，但似乎沒有任何效果。它要么向我顯示我不想要的大量結果串列，要么引發錯誤。

SELECT P.name, E.grade, C.name AS className
FROM Schedule AS S
JOIN Class AS C ON C.classid = S.classid
JOIN Person AS P ON P.personid = S.personid
JOIN Enrollment AS E ON E.personid = P.personid

Now I know this isnt what I need, but its where I am starting. Not sure if this is the correct direction to be heading

uj5u.com熱心網友回復：

您的資料不清楚，結果提供問題。但是查詢是

SELECT P.NAME,
       C.NAME,
       grade
FROM   (SELECT personid,
               classid,
               Max(grade) grade
        FROM  (SELECT E.personid,
                      A.classid,
                      E.grade
               FROM   (SELECT personid,
                              classid,
                              Min([date]) startDate,
                              Max([date]) endDate
                       FROM   AttendanceRecord
                       GROUP  BY personid,
                                 classid) A
                      JOIN Enrollment E
                        ON E.personid = A.personid
              ---AND A.startDate=E.startDate AND A.endDate=E.endDate
--Data is not clear and it provide no result
              ) AA
        GROUP  BY personid,
                  classid)AB
       JOIN Class C
         ON C.classid = AB.classid
       JOIN Person P
         ON P.personid = AB.personid  

標籤：sql sql-server join

上一篇：?如何在不覆寫現有列資料的情況下在Python中連接表

下一篇：無法自行加入具有NULL值的表