sqlserver- order by多字段排序

目录：

• 题
• 数据集生成
• 解

---

题：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