阿摩線上測驗
題組內容
第二題：
講師資料表如下：
instructor(id, name, dept_name, salary)
欄位說明:
id 為講師編號，name 為講師姓名，dept_name 為講師科系，salary 為講師月薪 請寫出下列問題之 T-SQL 語法：
（三）列出講師最多的科系名稱和講師人數（可能會有多個科系）。【10 分】
詳解 (共 3 筆)
小書僮Roy
詳解 #6064326
•考點: HAVING、MAX()、C...
(共 464 字，隱藏中）
前往觀看
Jung
詳解 #5796408
select dept_name, count(*) from instructor group by dept_name having count(*) = (select MAX(count(*)) from instructor group by dept_name);
god Manto
詳解 #6079999
SELECT dept_name, COUNT(*) AS instructor_count
FROM instructor
GROUP BY dept_name
HAVING COUNT(*) = (
SELECT MAX(NumInstructors)
FROM (
SELECT COUNT(*) AS NumInstructors
FROM instructor
GROUP BY dept_name
)
);
FROM instructor
GROUP BY dept_name
HAVING COUNT(*) = (
SELECT MAX(NumInstructors)
FROM (
SELECT COUNT(*) AS NumInstructors
FROM instructor
GROUP BY dept_name
)
);