1、SQL查询每一年+以前的销售金额:
表:
需要的结果:
SQL语句:
select b.ta_year,sum(a.ta_num)
from testa as a ,testa as b
where a.ta_year<=b.ta_year group by b.ta_year
2、SQL对每一列的统计
表:
需要的结果:
SQL语句:
select time,
sum(case winorlose when '胜' then 1 else 0 end) as '胜',
sum(case winorlose when '负' then 1 else 0 end) as '负'
from testb group by time
或者是:
select N.time,N.win,M.lose
from (select time ,count(*) win from testb where winorlose='胜' group by time)N
inner join (select time ,count(*) lose from testb where winorlose='负' group by time)M
on N.time=M.time
inner join与where的变换:
select N.time,N.win,M.lose
from (select time ,count(*) win from testb where winorlose='胜' group by time)N,
(select time ,count(*) lose from testb where winorlose='负' group by time)M
where N.time=M.time
select time ,count(*) win from testb where winorlose='胜' group by time
select time ,count(*) lose from testb where winorlose='负' group by time
其实是弄成两张表,再关联起来
3、SQL将列的横向排列
select year,
(select amount from test1 t1 where month='1' and t1.year=test1.year) 'm1',
(select amount from test1 t1 where month='2' and t1.year=test1.year) 'm2',
(select amount from test1 t1 where month='3' and t1.year=test1.year) 'm3',
(select amount from test1 t1 where month='3' and t1.year=test1.year) 'm4'
表:
需要的结果:
SQL语句:
select year,
max(case month when '1' then amount else '0' end) as 'm1',
max(case month when '2' then amount else '0' end) as 'm2',
max(case month when '3' then amount else '0' end) as 'm3',
max(case month when '4' then amount else '0' end) as 'm4'
from test1 group by year
解读:
当month是1的时候,把它变为amount的值
或者:
select A.year ,A.amount 'm1',B.amount 'm2',C.amount 'm3',d.amount 'm4'
from (select year , max(amount) amount from test1 where month='1' group by year)A,
(select year , max(amount) amount from test1 where month='2' group by year)B,
(select year , max(amount) amount from test1 where month='3' group by year)C,
(select year , max(amount) amount from test1 where month='4' group by year)D
where A.year=B.year and A.year=C.year and A.year=D.year
select year , max(amount) from test1 where month='1' group by year
解读:
关键的部分就是要把表拆掉,怎么拆就是一个问题
通用解法:
select distinct year,
(select amount from test1 t1 where month='1' and t1.year=test1.year) 'm1',
(select amount from test1 t1 where month='2' and t1.year=test1.year) 'm2',
(select amount from test1 t1 where month='3' and t1.year=test1.year) 'm3',
(select amount from test1 t1 where month='3' and t1.year=test1.year) 'm4'
from test1
或者
from test1 group by year
4、SQL学习过程:having 的用法
having子句对group by子句设置条件的方式与where子句和select语句交互的方式类似。
where子句搜索条件在进行分组操作之前应用;而having搜索条件在进行分组操作之后应用。
having语法与where语法类似,但having可以包含聚合函数,而where只能跟单行函数。
having子句可以引用选择列表中出现的任意项。
下面的查询是返回平均薪水大于2000的部门:
select dep_id , avg(salary) from s_emp group by dept_id having avg(salary)>2000
理解应用where、group by和having子句的正确序列对编写高效的查询代码会有所帮助:
where子句用来筛选from子句中指定的操作所产生的行。
group by子句用来分组where子句的输出。
having子句用来从分组的结果中筛选行。
对于可以在分组操作之前应用的搜索条件,在where子句中指定它们更有效。这样可以减少必须分组的行数。应当在having子句中指定的搜索条件只是那些必须在执行分组操作之后应用的搜索条件。因为having是在查询出的结果集中进行筛选,这个操作需要排序、总计等操作。如果先通过WHERE子句限制查询的记录数,可以减少这方面的开销。
Microsoft SQL Server 2000 查询优化器可处理这些条件中的大多数。如果查询优化器确定having搜索条件可以在分组操作之前应用,那么它就会在分组之前应用。查询优化器可能无法识别所有可以在分组操作之前应用的having搜索条件。建议将所有这些搜索条件放在where子句中而不是having子句中。
如果having中包含多个条件,那么这些条件将通过and、or 或not组合在一起。以下示例显示如何按出版商分组 titles,只包括那些标识号大于 0800、支付的总预付款已超过 $15,000 且销售书籍的平均价格小于 $20 的出版商。
SELECT pub_id,
SUM(advance) AS AmountAdvanced, AVG(price) AS AveragePrice FROM pubs.dbo.titles
WHERE pub_id > '0800'
GROUP BY pub_id
HAVING SUM(advance) > 15000
AND AVG(price) < 20
ORDER BY
可以用来为 GROUP BY 子句的输出排序。下面的示例显示使用 ORDER BY 子句以定义返回 GROUP BY 子句中的行为递减顺序:
SELECT pub_id,
SUM(advance) AS AmountAdvanced, AVG(price) AS AveragePrice FROM pubs.dbo.titles
WHERE pub_id > '0800' AND price >= 5 GROUP BY pub_id
HAVING SUM(advance) > 15000 AND AVG(price) < 20
ORDER BY pub_id DESC
5、SQL对列的横向排列
表:
需要的结果:
SQL语句:
select dep,
(select yeji from table1 t1 where mon='一月份' and t1.dep=table1.dep)'一月份',
(select yeji from table1 t2 where mon='二月份' and t2.dep=table1.dep)'二月份',
(select yeji from table1 t3 where mon='三月份' and t3.dep=table1.dep)'三月份'
from table1 group by dep
select yeji from table1 t1 where mon='一月份'
或者:
select dep, case when mon='一月份' then yeji else 0 end '一月份',
max(case when mon='二月份' then yeji else 0 end) '二月份',
max(case when mon='三月份' then yeji else 0 end) '三月份'
from table1 group by dep
7、经典问题
I.问题:
一张叫test3的表,里面只有一个有用字段name,一共有4条纪录,分别是a,b,c,d,对应四个球队,现在四个球对进行比赛,用一条sql语句显示所有可能的比赛组合。
表:
需要的结果:
SQL语句:
select a.name,b.name from test3 a,test3 b where a.name<b.name
II.问题:
有两个表A和B,均有key和value两个字段,如果B的key在A中也有,就把B的value换为A中对应的value。
SQL语句:
update b set b.value=(select a.value from a where a.key=b.key) where b.id in(select b.id from b , a where b.key=a.key)
III.问题:
表:
需要的结果:
SQL语句:
select Id,
max(case when proid=1 then proname else 0 end) 'pro1',
max(case when proid=2 then proname else 0 end) 'pro2'
from test4 group by Id
注意:
没有加max会出现以下的情况:
其实过程是这样的:
首先:
select Id,
(case when proid=1 then proname else 0 end) 'pro1',
(case when proid=2 then proname else 0 end) 'pro2'
from test4
这样查出来的记录数是跟Id行数同样多的
然后:
在已有的结果集中进行分组
select Id,
(case when proid=1 then proname else 0 end) 'pro1',
(case when proid=2 then proname else 0 end) 'pro2'
from test4 group by Id
这样查出来的结果是pro2不对
最后加上max
select Id,
(case when proid=1 then proname else 0 end) 'pro1',
max(case when proid=2 then proname else 0 end) 'pro2'
from test4 group by Id
那么问题来了:
为什么第一个不用加max?
或者:
select Id,
(select proname from test4 t4 where proid=1 and t4.Id=test4.Id)'pro1',
(select proname from test4 t4 where proid=2 and t4.Id=test4.Id)'pro2'
from test4 group by Id
IV.问题:ifnull的用法
表:
dep
emp
需要的结果:
SQL语句:
select depid, ifnull(dep,'无部门') '部门', name from emp left join dep on dep.id=emp.depid
注意:
在MySQL中是ifnull,在SQL Server中是isnull
V.问题:group_concat的用法
表:
需要的结果:
SQL语句:
select t5.a1 as a1, group_concat(t5.a2 separator '') as a2 from test5 as t5 group by a1
select t5.a1 a1,group_concat(t5.a2 separator '') a2 from test5 t5 group by a1
as可以不用
VI.问题:
查询A(ID,Name)表中第31至40条记录,ID作为主键可能是不是连续增长的列
SQL语句:
在SQL Server中的写法:
select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by A ) T) order by A
在MySQL中的写法:
select * from A limit 31,10
VII.问题:not in用法
表:
需要的结果:
SQL语句:
select * from test6 where id not in(select min(id) from test6 group by sign,goods,amount)
删除除了id不一样,其他都一样的冗余信息,那么可以用delete from
VIII.问题:Oracle专属decode以及普通SQL的替代方法
表:
需要的结果:
SQL语句:
select id,name,score,(case when score<60 then 'fail' else 'pass' end) mark from test7
Oracle的decode用法:
select id, name,score,decode(sign(score-60),-1,'fail','pass') as mark from test7
IX.问题:case when的通用解法
pid为产品ID,pnum为产品库存量,sid为仓库ID。请用SQL语句实现将上表中的数据合并。
表:
需要的结果:
SQL语句:
1.通用解法:
select pid,
sum(case when sid=1 then pnum else 0 end) as s1,
sum(case when sid=2 then pnum else 0 end) as s2,
sum(case when sid=3 then pnum else 0 end) as s3
from test10 group by pid
2.特殊解法,在没有最后一行的情况下:
select pid,
(select pnum from test10 t where sid=1 and t.pid=test10.pid) s1,
(select pnum from test10 t where sid=2 and t.pid=test10.pid) s2,
(select pnum from test10 t where sid=3 and t.pid=test10.pid) s3
from test10 group by pid
X.问题:
列出各个部门中工资高于本部门的平均工资的员工数和部门号,并按部门号排序
表:
需要的结果:
SQL语句:
select id,name,salary,depid tid
from test11 where salary>(select avg(salary) from test11 group by depid having depid=tid)
order by tid
或者:
select id,name,salary,depid from test11,(select avg(salary) avg,depid tid from test11 group by depid) t
where test11.depid=t.tid and test11.salary>t.avg
第二种结果:
SQL语句:
select count(*), tid from (
select id,depid tid
from test11 where salary>(select avg(salary) from test11 group by depid having depid=tid) ) t
group by tid
或者:
select count(id),depid from test11,(select avg(salary) avg,depid tid from test11 group by depid) t
where test11.depid=t.tid and test11.salary>t.avg
group by depid
XI.问题:
找出每门课成绩都在80分以上的学生姓名
表:
需要的结果:
是ww这个人
SQL语句:
select distinct name from test12 where name not in(select distinct name from test12 where score<80)
select name,min(score) from test12 group by name having min(score)>80
8、SQL联合查询(航空公司)的几个问题
问题:
表:
City:
Flight:
1.查询起飞城市是北京的所有航班,按到达城市的名字排序
SQL:
select flight.FlightId, s.CityName, e.CityName from city s, city e, flight
where s.CityId=StartCityId and s.CityName='北京' and e.CityId=EndCityId order by e.CityName desc
结果:
SQL:
select * from flight, city where EndCityId=CityId and StartCityId=
(select CityId from city where CityName='北京')
order by city.CityName desc
结果:
9、SQL联合查询(老师学生)的几个问题
问题:
1.显示的字段要有老师的name,age,每个老师所带的学生人数
2.只列出老师age为40以下,学生age为12以上的记录
表:
teacher:
student:
tea_stu:
SQL语句:
不考虑学生年龄的情况下:
select t.teaid tid,t.name name,t.age,count(ts.stuid)
from teacher t,tea_stu ts where t.teaid=ts.teaid group by tid
在考虑学生年龄的情况下:
1.统计分组信息,作为一张中间表
select teaid,count(*) from tea_stu ts group by teaid
2.然后是剔除年龄小于12岁的学生,需要与student相关联
select teaid,count(*) from student stu,tea_stu ts
where stu.stuid=ts.stuid and stu.age>12 group by teaid
3.最后是将2做成虚表与teacher相关联,并筛除大于45的老师
select tea.teaid,tea.name,tea.age,counts.total from teacher tea,
(select teaid,count(*) total from student stu,tea_stu ts
where stu.stuid=ts.stuid and stu.age>12 group by teaid) counts
where tea.teaid=counts.teaid and tea.age<45