SQL SELECT字符串大于count()

Question

我正在尝试列出研究生比本科生成员更多的组。我觉得我有我的想法背后的概念，但使查询比一个简单的翻译有点困难。下面是我的代码，我目前正在得到一个缺失的右括号错误，其中计数(学生。career = 'GRD')。谢谢。

SELECT studentgroup.name 
COUNT(student.career = 'GRD') - COUNT(student.career = 'UGRD') 
AS Gradnum FROM studentgroup 
INNER JOIN memberof ON studentgroup.GID = memberof.GroupID
INNER JOIN student ON memberof.StudentID = student.SID
WHERE Gradnum > 1;

Answer 1

SELECT studentgroup.GID, max(studentgroup.name)
FROM studentgroup 
INNER JOIN memberof ON studentgroup.GID = memberof.GroupID
INNER JOIN student ON memberof.StudentID = student.SID
GROUP BY studentgroup.GID
HAVING SUM(CASE WHEN student.career = 'GRD' THEN 1 
                WHEN student.career = 'UGRD'THEN -1
                ELSE 0
            END) >0

Answer 2

SELECT studentgroup.name 
SUM(CASE WHEN student.career = 'GRD' THEN 1 ELSE 0 END) - SUM(CASE WHEN student.career = 'UGRD' THEN 1 ELSE 0 END) 
AS Gradnum FROM studentgroup 
INNER JOIN memberof ON studentgroup.GID = memberof.GroupID
INNER JOIN student ON memberof.StudentID = student.SID
WHERE Gradnum > 1
GROUP BY studentgroup.name;

Answer 3

我使用WITH As子句，它被大多数DBMS支持，如SQL Server, PostGresSQL除了MySQL

With grpTbl As
(
SELECT studentgroup.name As StudentGroupName,
       SUM( CASE WHEN student.career = 'GRD' THEN 1 ELSE 0 END ) AS 'TotalGraduate',
       SUM( CASE WHEN student.career = 'UGRD' THEN 1 ELSE 0 END ) AS 'TotalUnderGraduate'
FROM studentgroup 
INNER JOIN memberof ON studentgroup.GID = memberof.GroupID
INNER JOIN student ON memberof.StudentID = student.SID
)

SELECT StudentGroupName
FROM grpTbl 
WHERE TotalGraduate > TotalUnderGraduate

对于MySQL，你可以使用临时表来存储第一次查询的结果集，并过滤掉WHERE子句中研究生多于本科生的GroupNames。此方法也适用于其他DBMS，不同之处在于创建临时表的语法。

CREATE TEMPORARY TABLE grpTbl (
StudentGroupName varchar(255),
TotalGraduate INT,
TotalUnderGraduate INT
);

INSERT INTO grpTbl
SELECT studentgroup.name As StudentGroupName,
           SUM( CASE WHEN student.career = 'GRD' THEN 1 ELSE 0 END ) ,
           SUM( CASE WHEN student.career = 'UGRD' THEN 1 ELSE 0 END ) 
    FROM studentgroup 
    INNER JOIN memberof ON studentgroup.GID = memberof.GroupID
    INNER JOIN student ON memberof.StudentID = student.SID 

 SELECT StudentGroupName
    FROM grpTbl 
    WHERE TotalGraduate > TotalUnderGraduate

 DROP TABLE grpTbl 

Answer 4

多一个选项

SELECT studentgroup.name
FROM studentgroup INNER JOIN memberof ON studentgroup.GID = memberof.GroupID
                  INNER JOIN student ON memberof.StudentID = student.SID
GROUP BY studentgroup.name
HAVING COUNT(CASE WHEN student.career = 'GRD' THEN student.career END) 
         > COUNT(CASE WHEN student.career = 'UGRD' THEN student.career END)