SQL1 返回具有聚合名称的行,而 SQL2 返回非聚合名称。
问题是执行这两个SQL时聚合逻辑有什么区别。谢谢。
SQL1
SELECT
name,
CASE WHEN COUNT(CASE WHEN course = 'SQL' THEN 1 END) > 0 THEN 'o' END AS SQL,
CASE WHEN COUNT(CASE WHEN course = 'UNIX' THEN 1 END) > 0 THEN 'o' END AS UNIX,
CASE WHEN COUNT(CASE WHEN course = 'Java' THEN 1 END) > 0 THEN 'o' END AS Java
FROM Courses
GROUP BY name;
SQL2
SELECT name,
CASE WHEN course = 'SQL' THEN '○' ELSE NULL END s,
CASE WHEN course = 'UNIX' THEN '○' ELSE NULL END u,
CASE WHEN course = 'Java' THEN '○' ELSE NULL END j
FROM Courses
GROUP BY name,course;
创建表
CREATE TABLE Courses
(name VARCHAR(32),
course VARCHAR(32),
PRIMARY KEY(name, course));
INSERT INTO Courses VALUES('Tom', 'SQL');
INSERT INTO Courses VALUES('Tom', 'UNIX');
INSERT INTO Courses VALUES('Jack', 'SQL');
INSERT INTO Courses VALUES('Mike', 'SQL');
INSERT INTO Courses VALUES('Mike', 'Java');
INSERT INTO Courses VALUES('Jane', 'UNIX');
INSERT INTO Courses VALUES('Mary', 'SQL');