时间戳

首页 > 解决方案 > 如何选择列中相同且大于2的值

sql-server - 如何选择列中相同且大于2的值

问题描述

我需要选择所有教授超过 2 门课程的教职员工。这需要在 WHERE 子句中的子查询中完成，并且需要从 Course 表中完成。这是一些SQL文件。我没有包含一个具有 'ae' 的 varchar 值的附加表来缩短问题。

CREATE TABLE Faculty
(Faculty_ID VARCHAR(2), 
LastName VARCHAR(20), 
FirstName VARCHAR(20), 
Department VARCHAR(20), 
Campus VARCHAR(10)); 

INSERT INTO Faculty VALUES ('1', 'Bloomberg', 'Mike', 'Business', 'Kent'); 
INSERT INTO Faculty VALUES ('2', 'Smith', 'Adam', 'Economics', 'Kent'); 
INSERT INTO Faculty VALUES ('3', 'Shakespeare', 'Bill', 'English', 'South'); 
INSERT INTO Faculty VALUES ('4', 'Euler', 'Lynn', 'Math', 'Deerwood'); 
INSERT INTO Faculty VALUES ('5', 'Einstein', 'Al', 'Science', 'South'); 


CREATE TABLE Course
(Course_ID CHAR(2), 
Ref_Number CHAR(5),
Faculty_ID VARCHAR(2),
Term VARCHAR(2),
Enrollment INTEGER,
TotRev FLOAT ); 

INSERT INTO Course VALUES ('1', '12345', 'a', 'A', 24, 12345.00 ); 
INSERT INTO Course VALUES ('2', '54321', '3', 'B', 18, 21435.00 ); 
INSERT INTO Course VALUES ('3', '13524', '1', 'B', 7,  1256.00 ); 
INSERT INTO Course VALUES ('4', '24653', '1', 'C', 29, 54421.00 ); 
INSERT INTO Course VALUES ('5', '98765', '5', 'A', 35, 246753.00); 
INSERT INTO Course VALUES ('6', '14862', '2', 'B', 14, 9876.00); 
INSERT INTO Course VALUES ('7', '96032', '1', 'C', 8,  863159.00); 
INSERT INTO Course VALUES ('8', '81256', '5', 'A', 5,  98762.00); 
INSERT INTO Course VALUES ('9', '64321', '2', 'C', 23, 2965.00); 
INSERT INTO Course VALUES ('10','90908', 'a', 'A', 45, 91724.00); 
INSERT INTO Course VALUES ('11','90908', '3', 'A', 23, 73725.00);
INSERT INTO Course VALUES ('12','90908', '3', 'A', 16, 84224.00);
INSERT INTO Course VALUES ('13','90908', 'b', 'A', 13, 42719.00);

到目前为止，我的查询是您在下面看到的...

SELECT FirstName, LastName, Faculty.Faculty_ID
FROM Faculty JOIN Course
    ON Faculty.Faculty_ID = Course.Faculty_ID
WHERE Faculty.Faculty_ID /**/
    (SELECT Faculty.Faculty_ID /**/
    FROM Course)

结果应显示 在此处输入图像描述

标签： sql-server

解决方案

如果这必须在WHERE子查询中完成，您可以执行以下操作：

SELECT F.Faculty_ID,
       F.LastName,
       F.FirstName,
       F.Department,
       F.Campus
FROM dbo.Faculty F
WHERE (SELECT COUNT(*)
       FROM dbo.Course C
       WHERE C.Faculty_ID = F.Faculty_ID) > 2;

推荐阅读