SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
HAVING (AVG(SCORE) = (SELECT MIN(AVG(SCORE))
FROM T_SCORE
GROUP BY COURSENO));
查询会报错:不能对包含聚合或子查询的表达式执行聚合函数,就是MIN(AVG(SCORE)。怎么办?
19 个解决方案
#1
--把HAVING去掉,效率低,没用
--然后再套一层
SELECT COURSENAME AS 课程名称,MIN(AVG_SCORE) AS 最低平均成绩
FROM (
SELECT COURSENAME, ROUND(AVG(SCORE),2) AS AVG_SCORE
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
)TB
#2
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
HAVING (AVG(SCORE) = ( select min(avg_SCORE) from(SELECT MIN(AVG(SCORE)) as avg_SCORE --这里多嵌套一层在用min函数
FROM T_SCORE
GROUP BY COURSENO)b));
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
HAVING (AVG(SCORE) = ( select min(avg_SCORE) from(SELECT MIN(AVG(SCORE)) as avg_SCORE --这里多嵌套一层在用min函数
FROM T_SCORE
GROUP BY COURSENO)b));
#3
这不是效率不效率的问题,WHERE不能用于聚合函数,只能用HAVING。
#4
你和楼上说的是一个意思,我也这样写了,但还是会报错,你看一下到底是哪里的问题,感觉GROUP BY 语句用起来特难受,都有点怕了。
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
WHERE (AVG(SCORE) = (SELECT MIN(AVG_SCORE)
FROM(
SELECT AVG(SCORE) AS AVG_SCORE
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME)));
感觉应该是SELECT AVG(SCORE) AS AVG_SCORE的问题,AVG_SCORE应该不能作为变量把AVG(SCORE)存起来吧,它只是个列名,用于输出。因为我单独把这层嵌套拿出来运行也会报错。
#5
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩 FROM T_COURSE, T_SCORE WHERE T_COURSE.COURSENO = T_SCORE.COURSENO GROUP BY COURSENAME HAVING (AVG(SCORE) = (SELECT MIN(SCORE) FROM (SELECT AVG(SCORE) SCORE FROM T_SCORE GROUP BY COURSENO))T);
#6
上面有点写错了,是HAVING(AVG(SCORE) = (SELECT MIN(AVG_SCORE) ...
#7
还可以改成TOP 1的形式.
#8
不要用min,用top
#9
用TOP1 然后用ORDER BY 排序。
#10
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
WHERE (AVG(SCORE) = (SELECT MIN(AVG_SCORE)
FROM(
SELECT AVG(SCORE) AS AVG_SCORE
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME) b)); --------------这里少了个别名
#11
SELECT TOP 1 COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
ORDER BY 平均成绩
#12
哎,弄了半天,原因在这儿,为什么要加这个别名呢?代指什么?
#13
你这种方法也可行,不过局限是不能输出相同的数据。
#14
还有一点疑问,就是COURSENO是主键,而现在是按照COURSENAME分组的,如果COURSENAME相同会出问题吗?
#15
说个方法试试。
把课程按平均分正排序使用Rank排名,然后取排名为1的
Select 课程名称, 平均成绩, rank over(order by 平均成绩) as f_no
From
(
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE as a inner join T_SCORE as b on a.COURSENO = b.COURSENO
) as t
Where f_no = 1;
如果有相同的也可以一起取出来
把课程按平均分正排序使用Rank排名,然后取排名为1的
Select 课程名称, 平均成绩, rank over(order by 平均成绩) as f_no
From
(
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE as a inner join T_SCORE as b on a.COURSENO = b.COURSENO
) as t
Where f_no = 1;
如果有相同的也可以一起取出来
#16
语法是这样。。你问我语法为什么是这样?

#17
我知道了,其实如果要查询非主键的COURSENAME,还是要按照COURSENO和COURSENAME分组,这和按主键COURSENO分组是一样的,因为一个COURSENO只可能对应一个COURSENAME.
#18
你说的完整语法是这样吧:
用DENSE_RANK比RANK更好一点,因为序号是连续的。
WITH T_STU
AS
(
SELECT DENSE_RANK() OVER(ORDER BY SCORE DESC) AS ROW_NUM, NAME, SCORE FROM STU
)
SELECT NAME, SCORE
FROM T_STU
WHERE ROW_NUM = 1;
用DENSE_RANK比RANK更好一点,因为序号是连续的。
#19
两种都可以。因为只关注最小的,所以只要取1就可。至于下一个名次是连续的第2名或者是不连续的第3、4都无所谓了。
#20
#1
--把HAVING去掉,效率低,没用
--然后再套一层
SELECT COURSENAME AS 课程名称,MIN(AVG_SCORE) AS 最低平均成绩
FROM (
SELECT COURSENAME, ROUND(AVG(SCORE),2) AS AVG_SCORE
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
)TB
#2
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
HAVING (AVG(SCORE) = ( select min(avg_SCORE) from(SELECT MIN(AVG(SCORE)) as avg_SCORE --这里多嵌套一层在用min函数
FROM T_SCORE
GROUP BY COURSENO)b));
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
HAVING (AVG(SCORE) = ( select min(avg_SCORE) from(SELECT MIN(AVG(SCORE)) as avg_SCORE --这里多嵌套一层在用min函数
FROM T_SCORE
GROUP BY COURSENO)b));
#3
这不是效率不效率的问题,WHERE不能用于聚合函数,只能用HAVING。
#4
你和楼上说的是一个意思,我也这样写了,但还是会报错,你看一下到底是哪里的问题,感觉GROUP BY 语句用起来特难受,都有点怕了。
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
WHERE (AVG(SCORE) = (SELECT MIN(AVG_SCORE)
FROM(
SELECT AVG(SCORE) AS AVG_SCORE
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME)));
感觉应该是SELECT AVG(SCORE) AS AVG_SCORE的问题,AVG_SCORE应该不能作为变量把AVG(SCORE)存起来吧,它只是个列名,用于输出。因为我单独把这层嵌套拿出来运行也会报错。
#5
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩 FROM T_COURSE, T_SCORE WHERE T_COURSE.COURSENO = T_SCORE.COURSENO GROUP BY COURSENAME HAVING (AVG(SCORE) = (SELECT MIN(SCORE) FROM (SELECT AVG(SCORE) SCORE FROM T_SCORE GROUP BY COURSENO))T);
#6
上面有点写错了,是HAVING(AVG(SCORE) = (SELECT MIN(AVG_SCORE) ...
#7
还可以改成TOP 1的形式.
#8
不要用min,用top
#9
用TOP1 然后用ORDER BY 排序。
#10
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
WHERE (AVG(SCORE) = (SELECT MIN(AVG_SCORE)
FROM(
SELECT AVG(SCORE) AS AVG_SCORE
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME) b)); --------------这里少了个别名
#11
SELECT TOP 1 COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
ORDER BY 平均成绩
#12
哎,弄了半天,原因在这儿,为什么要加这个别名呢?代指什么?
#13
你这种方法也可行,不过局限是不能输出相同的数据。
#14
还有一点疑问,就是COURSENO是主键,而现在是按照COURSENAME分组的,如果COURSENAME相同会出问题吗?
#15
说个方法试试。
把课程按平均分正排序使用Rank排名,然后取排名为1的
Select 课程名称, 平均成绩, rank over(order by 平均成绩) as f_no
From
(
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE as a inner join T_SCORE as b on a.COURSENO = b.COURSENO
) as t
Where f_no = 1;
如果有相同的也可以一起取出来
把课程按平均分正排序使用Rank排名,然后取排名为1的
Select 课程名称, 平均成绩, rank over(order by 平均成绩) as f_no
From
(
SELECT COURSENAME AS 课程名称, ROUND(AVG(SCORE),2) AS 平均成绩
FROM T_COURSE as a inner join T_SCORE as b on a.COURSENO = b.COURSENO
) as t
Where f_no = 1;
如果有相同的也可以一起取出来
#16
语法是这样。。你问我语法为什么是这样?

#17
我知道了,其实如果要查询非主键的COURSENAME,还是要按照COURSENO和COURSENAME分组,这和按主键COURSENO分组是一样的,因为一个COURSENO只可能对应一个COURSENAME.
#18
你说的完整语法是这样吧:
用DENSE_RANK比RANK更好一点,因为序号是连续的。
WITH T_STU
AS
(
SELECT DENSE_RANK() OVER(ORDER BY SCORE DESC) AS ROW_NUM, NAME, SCORE FROM STU
)
SELECT NAME, SCORE
FROM T_STU
WHERE ROW_NUM = 1;
用DENSE_RANK比RANK更好一点,因为序号是连续的。
#19
两种都可以。因为只关注最小的,所以只要取1就可。至于下一个名次是连续的第2名或者是不连续的第3、4都无所谓了。