目录:
- 题
- 数据集生成
- 解
题:Count Student Number in Departments
- 测试数据集生成
USE master
GO
IF (EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name='LeetCode'))
BEGIN
DROP DATABASE LeetCode
SELECT 1 AS 'deleted_LC'
END
GO
CREATE DATABASE LeetCode
GO
USE LeetCode
GO
IF (OBJECT_ID('department', 'U') IS NOT NULL)
BEGIN
DROP TABLE department
SELECT 1 AS 'deleted_dept'
END
GO
CREATE TABLE department
(
dept_id INT,
dept_name VARCHAR(20),
CONSTRAINT PK_dept_id PRIMARY KEY(dept_id)
)
INSERT INTO department
VALUES
(1, 'Engineering'), (2, 'Science'), (3, 'Law'), (4, 'Aa')
GO
IF (EXISTS (SELECT * FROM sysobjects WHERE name='student'))
BEGIN
DROP TABLE student
SELECT 1 AS 'deleted_stu'
END
GO
CREATE TABLE student
(
student_id INT
CONSTRAINT PK_student PRIMARY KEY,
student_namme VARCHAR(20),
gender VARCHAR(5)
CONSTRAINT Check_gender CHECK(gender IN ('M', 'F')),
dept_id INT,
CONSTRAINT FK_dept_id FOREIGN KEY(dept_id) REFERENCES department(dept_id)
)
INSERT INTO student
VALUES
(1, 'Jack', 'M', 1), (2, 'Jane', 'F', 1), (3, 'Mark', 'M', 2)
GO
SELECT * FROM student
SELECT * FROM department
GO
- 查询
-- 查询项目名 * 学科人数
-- S1- FULL JOIN + GROUP BY
SELECT dept.dept_name, COUNT(stu.student_id) Cnt_stu
FROM department dept
FULL JOIN student stu
ON dept.dept_id=stu.dept_id
GROUP BY dept.dept_name
ORDER BY Cnt_stu DESC, dept_name ASC
-- S2- 效率更低 JOIN + 子查询
SELECT dept.dept_name, ISNULL(Stu.Cnt_stu, 0) AS number_student
FROM department dept
LEFT JOIN(
SELECT dept.dept_name, COUNT(stu.student_id) Cnt_stu
FROM department dept, student stu
WHERE dept.dept_id=stu.dept_id
GROUP BY dept.dept_name
) Stu
ON dept.dept_name=Stu.dept_name
ORDER BY number_student DESC, dept.dept_name ASC
-- S3- case clause + left join + group by
-- 效率更佳
SELECT dept.dept_name,
SUM(CASE
WHEN student_id IS NULL THEN 0
ELSE 1
END) AS student_number
FROM department dept
LEFT JOIN student stu
ON dept.dept_id=stu.dept_id
GROUP BY dept.dept_name
ORDER BY student_number DESC, dept_name ASC