SELECT * FROM TABLENAME WHERE CHENGJI > (SELECT AVG(CHENGJI) FROM TABLENAME)
select t.* from test_data t where t.value > (select avg(tt.value) from test_data tt)
不好意思,忘记了一个条件,不用子查询,用having
select id from 成绩表 group by id having score>(select avg(score) from 成绩表)
为什么一定要用having,效率提高了?
SELECT * FROM (SELECT T.*, AVG(SCORE) OVER() AS AVG_SCORE FROM (SELECT '小明1' NAME, '12' AGE ,'100' SCORE FROM DUAL UNION ALL SELECT '小明2' NAME, '12' AGE ,'90' SCORE FROM DUAL UNION ALL SELECT '小明3' NAME, '12' AGE ,'80' SCORE FROM DUAL UNION ALL SELECT '小明4' NAME, '12' AGE ,'86' SCORE FROM DUAL UNION ALL SELECT '小明5' NAME, '12' AGE ,'98' SCORE FROM DUAL UNION ALL SELECT '小明6' NAME, '12' AGE ,'87' SCORE FROM DUAL UNION ALL SELECT '小明7' NAME, '12' AGE ,'60' SCORE FROM DUAL UNION ALL SELECT '小明8' NAME, '12' AGE ,'85' SCORE FROM DUAL ) T) WHERE SCORE > AVG_SCORE ;
from test_data t
where t.value > (select avg(tt.value) from test_data tt)
(SELECT T.*, AVG(SCORE) OVER() AS AVG_SCORE
FROM
(SELECT '小明1' NAME, '12' AGE ,'100' SCORE FROM DUAL UNION ALL
SELECT '小明2' NAME, '12' AGE ,'90' SCORE FROM DUAL UNION ALL
SELECT '小明3' NAME, '12' AGE ,'80' SCORE FROM DUAL UNION ALL
SELECT '小明4' NAME, '12' AGE ,'86' SCORE FROM DUAL UNION ALL
SELECT '小明5' NAME, '12' AGE ,'98' SCORE FROM DUAL UNION ALL
SELECT '小明6' NAME, '12' AGE ,'87' SCORE FROM DUAL UNION ALL
SELECT '小明7' NAME, '12' AGE ,'60' SCORE FROM DUAL UNION ALL
SELECT '小明8' NAME, '12' AGE ,'85' SCORE FROM DUAL ) T)
WHERE SCORE > AVG_SCORE ;