--把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
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));
这不是效率不效率的问题,WHERE不能用于聚合函数,只能用HAVING。
你和楼上说的是一个意思,我也这样写了,但还是会报错,你看一下到底是哪里的问题,感觉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)存起来吧,它只是个列名,用于输出。因为我单独把这层嵌套拿出来运行也会报错。
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);
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)); --------------这里少了个别名
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 平均成绩
说个方法试试。 把课程按平均分正排序使用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; 如果有相同的也可以一起取出来
你说的完整语法是这样吧: 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更好一点,因为序号是连续的。
--把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
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
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)存起来吧,它只是个列名,用于输出。因为我单独把这层嵌套拿出来运行也会报错。
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)); --------------这里少了个别名
FROM T_COURSE, T_SCORE
WHERE T_COURSE.COURSENO = T_SCORE.COURSENO
GROUP BY COURSENAME
ORDER BY 平均成绩
把课程按平均分正排序使用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;
如果有相同的也可以一起取出来
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更好一点,因为序号是连续的。