T-SQL子查詢，找出選修最少科目的學生姓名

我的 SQL 資料庫中有以下表格和資料，我需要使用T-SQL子查詢找出選課最少的學生的姓名。

預期結果圖片鏈接：預期結果

表 01 名稱 (tblSubject)

CREATE TABLE tblSubject
(
    SubjectID INT PRIMARY KEY NOT NULL,
    SubjectName NVARCHAR (50) NOT NULL
)
GO

表 02 名稱 (tblSemester)

CREATE TABLE tblSemester 
(
    SemesterID INT PRIMARY KEY NOT NULL,
    SemesterName NVARCHAR (50) NOT NULL
)
GO

表 03 名稱 (tblTeacher)

CREATE TABLE tblTeacher
(
    TeacherID INT PRIMARY KEY NOT NULL IDENTITY,
    TeacherName NVARCHAR (50) NOT NULL
)
GO

表 04 名稱 (tblTeacher_Details)

CREATE TABLE tblTeacher_Details
(
    TeacherID INT REFERENCES tblTeacher (TeacherID) NOT NULL,
    SemesterID INT REFERENCES tblSemester (SemesterID) NOT NULL,
    SubjectID INT REFERENCES tblSubject (SubjectID) NOT NULL
    PRIMARY KEY (TeacherID, SemesterID, SubjectID )
)
GO

表 05 姓名 (tblStudent)

CREATE TABLE tblStudent
(
    StudentID INT PRIMARY KEY IDENTITY NOT NULL,
    StudentName NVARCHAR (50) NOT NULL
)
GO

表 06 姓名 (tblStudent_Details)

CREATE TABLE tblStudent_Details
(
    StudentID INT REFERENCES tblStudent (StudentID) NOT NULL,
    SemesterID INT REFERENCES tblSemester (SemesterID) NOT NULL,
    SubjectID INT REFERENCES tblSubject (SubjectID) NOT NULL,
    TeacherID INT REFERENCES tblTeacher (TeacherID) NOT NULL
    PRIMARY KEY (StudentID, SemesterID, SubjectID, TeacherID )
)
GO

將資料插入表名 (tblSubject)

INSERT INTO tblSubject
VALUES 
    (1, 'C#'),
    (2, 'Data Base'),
    (3, 'Web Design'),
    (4, 'MIS'),
    (5, 'PHP'),
    (6, 'Project Management'),
    (7, 'PCL'),
    (8, 'Software Engineering'),
    (9, 'Data Minin')
GO

將資料插入表名 (tblSemester)

INSERT INTO tblSemester
VALUES 
    (1, 'Spring'),
    (2, 'Summer'),
    (3, 'Fall'),
    (4, 'Winter')
GO

將資料插入表名 (tblTeacher)

INSERT INTO tblTeacher
VALUES
    ('A'),
    ('B'),
    ('C')
GO

將資料插入表名 (tblTeacher_Details)

INSERT INTO tblTeacher_Details
([TeacherID], [SemesterID], [SubjectID])
VALUES
    (1,1,1),
    (2,1,2),
    (3,1,3),
    (1,2,1),
    (2,2,9),
    (3,2,4),
    (1,3,5),
    (2,3,2),
    (3,3,6),
    (1,4,7),
    (2,4,8),
    (3,4,3)
GO

將資料插入表名 (tblStudent)

INSERT INTO tblStudent
VALUES
    ('AA'),
    ('BB'),
    ('DD'),
    ('EE'),
    ('HH'),
    ('GG'),
    ('FF'),
    ('CC'),
    ('II')
GO

將資料插入表名 (tblStudent_Details)

INSERT INTO tblStudent_Details
([StudentID], [SemesterID], [SubjectID], [TeacherID])
VALUES
    (1,1,1,1),
    (2,1,1,1),
    (3,1,1,1),
    (1,1,2,2),
    (8,1,2,2),
    (9,1,2,2),
    (4,1,3,3),
    (5,1,3,3),
    (2,1,3,3),
    (6,1,3,3),
    (4,2,1,1),
    (6,2,1,1),
    (2,2,9,2),
    (7,2,9,2),
    (5,2,9,2),
    (1,2,4,3),
    (2,2,4,3),
    (8,2,4,3),
    (5,3,5,1),
    (9,3,5,1),
    (4,3,2,2),
    (6,3,2,2),
    (3,3,2,2),
    (9,3,6,3),
    (3,3,6,3),
    (7,3,6,3),
    (8,4,7,1),
    (7,4,7,1),
    (1,4,8,2),
    (2,4,8,2),
    (6,4,8,2),
    (1,4,3,3),
    (8,4,3,3),
    (3,4,3,3)
GO  

uj5u.com熱心網友回復：

您可以使用此查詢：

select StudentName, count(1) [Number Of Subject] 
from tblStudent_Details d left join tblStudent s on s.StudentID=d.StudentID
group by StudentName
having count(1) = (select top 1 count(1) from tblStudent_Details group by StudentID order by 1)
order by StudentName

/* Output:
StudentName Number Of Subject
EE          3
FF          3
HH          3
II          3
*/

uj5u.com熱心網友回復：

嘗試一下 ：

select StudentName,count(*) from   tblStudent s
join   tblStudent_Details d on s.StudentID = d.StudentID
group by StudentName
order by 2 

標籤：sql-server 查询语句 子查询

上一篇：Asp.Net-Core將JavaScript函式傳遞給區域視圖

下一篇：在子查詢中使用WHERE子句查看