SQL————经典问题

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