Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- id name city salary dept
- SELECT salary WHERE salary > (SELECT AVG(salary) FROM *)
- select * from b where ref > (select avg(ref) from b);
- select * from b having ref > (select avg(ref) from b);
- mysql> select * from b;
- +------+------------+------+
- | id | d2 | ref |
- +------+------------+------+
- | 300 | 2010-12-12 | 3 |
- | 300 | 2011-12-12 | 2 |
- | 300 | 2012-12-12 | 1 |
- | 400 | 2011-12-12 | 1 |
- +------+------------+------+
- 4 rows in set (0.00 sec)
- mysql> select * from b having ref > avg(ref);
- +------+------------+------+
- | id | d2 | ref |
- +------+------------+------+
- | 300 | 2010-12-12 | 3 |
- +------+------------+------+
- 1 row in set (0.00 sec)
- mysql> select * from b having ref > (select avg(ref) from b);
- +------+------------+------+
- | id | d2 | ref |
- +------+------------+------+
- | 300 | 2010-12-12 | 3 |
- | 300 | 2011-12-12 | 2 |
- +------+------------+------+
- 2 rows in set (0.02 sec)
- mysql> select * from b where ref > (select avg(ref) from b);
- +------+------------+------+
- | id | d2 | ref |
- +------+------------+------+
- | 300 | 2010-12-12 | 3 |
- | 300 | 2011-12-12 | 2 |
- +------+------------+------+
- 2 rows in set (0.00 sec)
- mysql> select *,avg(ref) from b having ref > avg(ref);
- +------+------------+------+----------+
- | id | d2 | ref | avg(ref) |
- +------+------------+------+----------+
- | 300 | 2010-12-12 | 3 | 1.7500 |
- +------+------------+------+----------+
- 1 row in set (0.00 sec)
- SELECT Salary
- FROM (
- SELECT
- Salary,
- AVG(Salary) OVER () AS AvgSalary
- FROM atable
- ) s
- WHERE Salary > AvgSalary
- SELECT *FROM table_name WHERE salary > avg(select salary from table_name)
- select empno,e.deptno,sal
- from emp e, ( select deptno,avg(sal) avsal
- from emp
- group by deptno
- ) a
- where e.sal > a.avsal
- and e.deptno = a.deptno;
- select salary from Employee where salary > (select ava(salary) from employee)
- select e1.* from emp e1 inner join (select avg(sal) avg_sal,dept_id from emp group by
- dept_id) as e2 on e1.dept_id=e2.dept_id and e1.sal>e2.avg_sal
- select * from emp where sal > (select avg(sal) from emp)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement