sqlquery3.sql

经典的SOL面试题,涵盖了数据库各个层面的问题和答案。

/*
select b.year,sum(a.salary) 
from salary a,salary b 
group by b.year 
having 
a.year<=b.year 
order by b.year




select b.year,sum(a.salary) 
from salary a,salary b 
where a.year<=b.year group by b.year 
order by b.year



select s1.year "year",
(select sum(s2.salary) from salary s2 where s2.year<=s1.year) "salary" from salary s1


*/

select a.year , 
(select sum(b.salary) from salary  b where b.year <=a.year ) from salary a order by salary

select a.year,sum(b.salary)
from salary a , salary b
where b.year<=a.year
group by a.year

/*
上述方法区别:
理念: from a, b   +   group by 
      from a      +   order by 

*/



select * from myCount
select sum(num) from myCount where num<5 and num>3 -- 没有group by列时可以用where子句,      进行聚合计算,  where 子句中不能有聚合函数
select sum(num) from myCount group by co           -- 有group by列时可以用group by子句,     进行聚合计算

/* 
第一: where , group by, having 为三者顺序
第二: where, having 均可进行group by 筛选, 区别: where 用在group by 前, 对group by前 结果进行筛选
                                           having用在group by 后, 对group by后 结果进行筛选 

第三: 没有group by列时可以用where子句,      进行聚合计算
      有group by列时可以用group by子句,     进行聚合计算
*/