SQL Cookbook中文版

出版时间:2007-10-1  出版社:清华大学出版社  作者:(美)莫利纳罗 著  译者:王强,王晓  
Tag标签:无  

内容概要

SQL 是计算机世界的语言,在用关系数据库开发报表时,将数据放入数据库以及从数据库中取出来,都需要SQL 的知识。很多人以一种马马虎虎的态度在使用SQL,根本没有意识到自己掌握着多么强大的武器。本书的目的是打开读者的视野,看看SQL 究竟能干什么,以改变这种状况。
本书是一本指南,其中包含了一系列SQL 的常用问题以及它们的解决方案,希望能对读者的日常工作有所帮助。本书将相关主题的小节归成章,如果读者遇到不能解决的SQL 新问题,可以先找到最可能适用的章,浏览其中各小节的标题,希望读者能从中找到解决方案,至少可以找到点灵感。
在这本书中有150 多个小节,这还仅仅是SQL 所能做的事情的一鳞半爪。解决日常编程问题的解决方案的数量仅取决于需要解决的问题的数量,本书没有覆盖所有问题,事实上也不可能覆盖;然而从中可以找到许多共同的问题及其解决方案,这些解决方案中用到许多技巧,读者学到这些技巧就可以将它们扩展并应用到本书不可能覆盖的其他新问题上。
毫无疑问,本书的目标是让读者看到,SQL 能够做多少一般认为是SQL 问题范围之外的事情。在过去的10 年间,SQL 走过了很长的路,许多过去只能用C 和JAVA等过程化语言解决的典型问题现在都可以直接用SQL 解决了,但是很多开发人员并没有意识到这一事实。本书就是要帮助大家认识到这一点。
现在,在对我刚才的话产生误解之前我先要申明:我是“如果没坏,就别去修它”这一教义的忠实信徒。例如,假如你有一个特定的业务问题要解决,目前只用SQL检索数据,而其他复杂的业务逻辑由其他语言完成,如果代码没有问题,而且性能也过得去,那么,谢天谢地。我绝对无意建议你扔掉以前的代码重新寻求完全SQL 的解决方案;我只是请你敞开思想,认识到1995 年编程用的SQL 跟2005 年用的不是一回事,今天的SQL 能做的事要多得多。

作者简介

Anthony Molinaro是wireless Generation公司的数据库开发人员。他多年从事帮助开发人员改进其sQL查询的工作,具有丰富的实践经验。Anthony酷爱sQL,在相关领域,他小有名气,客户在遇到困难的sQL查询问题时,就会想到他,他总能起到关键作用。他博学多才,对关系理论有深入的理解,有9年解决复杂sQL问题的实战经验。Anthony通晓新的和功能强大的sQL功能,比如,添加到最新sQL标准中的窗口函数语法等。

图书封面

图书标签Tags

评论、评分、阅读与下载


    SQL Cookbook中文版 PDF格式下载


用户评论 (总计4条)

 
 

  •     2.5
      select ename,sal,comm,
      
      3.6
      select el.ename,el.loc,eb.received
      from (select e.empno,e.ename,d.loc
      from emp e,dept d
      where e.deptno=d.deptno) el
      left join emp_bonus eb
      on el.empno=eb.empno;
      
      select e.ename,d.loc,eb.received
      from emp e join dept d
      on e.deptno=d.deptno
      left join emp_bonus eb
      on e.empno=eb.empno
      order by 2;
      
      select e.ename,d.loc,
      (select eb.received
      from emp_bonus eb
      where eb.empno=e.empno) as received
      from emp e,dept d
      where e.deptno=d.deptno;
      
      select *
      from (
      select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,count(*) as cnt
      from emp e
      group by empno,ename,job,mgr,hiredate,sal,comm,deptno
      ) e
      where not exists(
      select null
      from (
      select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno,count(*) as cnt
      from v37 v
      group by empno,ename,job,mgr,hiredate,sal,comm,deptno
      ) v
      where v.empno=e.empno
      and v.ename=e.ename
      and v.job=e.job
      and v.mgr=e.mgr
      and v.hiredate=e.hiredate
      and v.sal=e.sal
      and v.deptno=e.deptno
      and v.cnt=e.cnt
      and coalesce(v.comm,0)=coalesce(e.comm,0)
      );
      
      select * from emp e
      where not exists
      (select null from v37 v
      where v.empno=e.empno
      and v.ename=e.ename
      and v.job=e.job
      and v.mgr=e.mgr
      and v.hiredate=e.hiredate
      and v.sal=e.sal
      and v.deptno=e.deptno
      )
      
      select *
      from (
      select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno,count(*) as cnt
      from v37 v
      group by empno,ename,job,mgr,hiredate,sal,comm,deptno
      ) v
      where not exists(
      select null
      from (
      select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,count(*) as cnt
      from emp e
      group by empno,ename,job,mgr,hiredate,sal,comm,deptno
      ) e
      where v.empno=e.empno
      and v.ename=e.ename
      and v.job=e.job
      and v.mgr=e.mgr
      and v.hiredate=e.hiredate
      and v.sal=e.sal
      and v.deptno=e.deptno
      and v.cnt=e.cnt
      and coalesce(v.comm,0)=coalesce(e.comm,0)
      );
      
      select * from v37 v
      where not exists
      (select null from emp e
      where v.empno=e.empno
      and v.ename=e.ename
      and v.job=e.job
      and v.mgr=e.mgr
      and v.hiredate=e.hiredate
      and v.sal=e.sal
      and v.deptno=e.deptno
      );
      
      3.9
      select deptno,
      sum(distinct sal) as totalsal,
      sum(bonus) as totalbonus
      from
      (
      select e.deptno,
      e.sal,
      e.sal*case when eb.type=1 then 0.1
      when eb.type=2 then 0.2
      when eb.type=3 then 0.3
      end as bonus
      from emp e join emp_bonus_39 eb
      on e.empno=eb.empno
      where e.deptno=10
      ) x;
      
      select deptno,
      sum(sal) as totalsal,
      sum(bonus) as totalbonus
      from(
      select e.deptno,
      e.sal,
      sum(
      e.sal*case when eb.type=1 then 0.1
      when eb.type=2 then 0.2
      when eb.type=3 then 0.3
      end
      ) as bonus
      from emp e join emp_bonus_39 eb
      on e.empno=eb.empno
      where e.deptno=10
      group by e.empno
      ) x;
      
      select e.empno,
      e.ename,
      e.sal,
      e.sal*case when eb.type=1 then 0.1
      when eb.type=2 then 0.2
      when eb.type=3 then 0.3
      end as bonus
      from emp e join emp_bonus_39 eb
      on e.empno=eb.empno
      where e.deptno=10;
      
      select d.deptno,
      d.totalsal,
      sum(
      e.sal*case when eb.type=1 then 0.1
      when eb.type=2 then 0.2
      else 0.3
      end
      ) as totalbonus
      from emp e,
      emp_bonus_39 eb,
      (
      select deptno,sum(sal) as totalsal from emp where deptno=10
      ) d
      where e.empno=eb.empno
      and e.deptno=d.deptno;
      
      select d.deptno,
      d.totalsal,
      sum(
      e.sal*case when eb.type=1 then 0.1
      when eb.type=2 then 0.2
      else 0.3 end
      ) as totalbonus
      from emp e join emp_bonus_39 eb
      on e.empno=eb.empno
      join
      (select deptno,sum(sal) as totalsal from emp where deptno=10 group by deptno) d
      on d.deptno=e.deptno
      group by d.deptno,d.totalsal;
      
      select d.deptno,
      d.totalsal,
      sum(
      e.sal*case when eb.type=1 then 0.1
      when eb.type=2 then 0.2
      else 0.3 end
      ) as totalbonus
      from emp e,
      emp_bonus_39 eb,
      (select deptno,sum(sal) as totalsal from emp where deptno=10 group by deptno) d
      where e.empno=eb.empno
      and e.deptno=d.deptno
      group by d.deptno,d.totalsal;
      
      3.10
      select e.deptno,
      e.empno,
      e.ename,
      e.sal,
      e.sal*case when eb.type=1 then 0.1
      when eb.type=2 then 0.2
      when eb.type=3 then 0.3
      end as bonus
      from emp e left join emp_bonus_310 eb
      on e.empno=eb.empno
      where e.deptno=10;
      
      select e.deptno,
      e.empno,
      e.ename,
      e.sal,
      e.sal*case when eb.type=1 then 0.1
      when eb.type=2 then 0.2
      when eb.type=3 then 0.3
      else 0
      end as bonus
      from emp e left join emp_bonus_310 eb
      on e.empno=eb.empno
      where e.deptno=10;
      
      select e.deptno,
      e.empno,
      e.sal,
      sum(
      e.sal*case when eb.type=1 then 0.1
      when eb.type=2 then 0.2
      when eb.type=3 then 0.3
      else 0
      end
      ) as bonus
      from emp e left join emp_bonus_310 eb
      on e.empno=eb.empno
      where e.deptno=10
      group by e.empno;
      
      select d.deptno,d.totalsal,
      sum(
      e.sal*case when eb.type=1 then 0.1
      when eb.type=2 then 0.2
      when eb.type=3 then 0.3
      end
      ) as totalbonus
      from emp e,
      emp_bonus_310 eb,
      (select deptno,sum(sal) as totalsal from emp where deptno=10) d
      where e.empno=eb.empno
      and e.deptno=d.deptno
      group by d.deptno;
      
      select d.deptno,d.dname,e.ename
      from dept d full outer join emp e
      on(d.deptno=e.deptno);
      
      mysql不支持全外连接
      select d.deptno,d.dname,e.ename
      from dept d left join emp e
      on e.deptno=d.deptno
      union
      select d.deptno,d.dname,e.ename
      from dept d right join emp e
      on e.deptno=d.deptno;
      
      4.10
      错误
      update emp_410
      set sal=(select sal from new_sal_410),
      comm=(select sal from new_sal_410)*0.5
      where deptno=(select deptno from new_sal_410)
      
      update emp_410 e
      set (e.sal,e.comm)=(
      select ns.sal,ns.sal/2 from new_sal_410 ns where e.deptno=ns.deptno
      )
      where exists (select null from new_sal_410 ns where ns.deptno=e.deptno);
      错误有问题
      
      update emp_410 e
      set e.sal=(select ns.sal from new_sal_410 ns where ns.deptno=e.deptno),
      e.comm=(select ns.sal from new_sal_410 ns where ns.deptno=e.deptno)/2
      where exists (select null from new_sal_410 ns where ns.deptno=e.deptno);
      
      oracle
      update (
      select e.sal as emp_sal,e.comm as emp_comm,ns.sal as new_sal,ns.sal/2 as new_comm
      from emp_410 e,new_sal_410 ns
      where e.deptno=ns.deptno)
      set emp_sal=new_sal,emp_comm=new_comm;
      
      sql server
      update
      e.sal=ns.sal,
      e.comm=ns.sal/2
      from emp_410 e,
      new_sal_410 ns
      where e.deptno=ns.deptno;
      
      4.11
      create table emp_commission as select deptno,empno,ename,comm from emp where false;
      insert into emp_commission(deptno,empno,ename)
      values(10,7782,'CLARK'),
      (10,7839,'KING'),
      (10,7934,'MILLER');
      
      select ec.*
      from emp_commission ec join emp e
      on ec.empno=e.empno
      where e.sal<2000;
      
      select ec.*
      from emp_commission ec join emp e
      on ec.empno=e.empno
      where e.sal>=2000;
      
      select e.empno,e.ename,e.deptno
      from emp e join emp_commission ec
      on e.empno=ec.empno
      where e.sal>=2000;
      
      delete from emp_commission where empno in
      (select emp_commission.empno from emp join emp_commission
      on emp.empno=emp_commission.empno
      where emp.sal<2000);
      
      update emp_commission
      set comm=1000 where empno in
      (select e.empno from emp e join emp_commission ec
      on e.empno=ec.empno
      where e.sal>=2000);
      
      select * from emp
      where empno in
      (select e.empno from emp e join emp_commission ec
      on e.empno=ec.empno
      where e.sal>=2000);
      4.16
      create table dupes(id integer,name varchar(10));
      insert into dupes values(1,'NAPOLEON'),
      (2,'DYNAMITE'),
      (3,'DYNAMITE'),
      (4,'SHE SELLS'),
      (5,'SEA SHELLS'),
      (6,'SEA SHELLS'),
      (7,'SEA SHELLS');
      
      4.17
      create table dept_accidents
      (
      deptno int,
      accident_name varchar(20)
      );
      
      insert into dept_accidents
      values(10,'broken foot'),
      (10,'flesh wound'),
      (20,'fire'),
      (20,'fire'),
      (20,'flood'),
      (30,'bruised glute');
      
      select deptno from dept_accidents group by deptno having count(*)>=3;
      
      6.1
      select substr(e.ename,iter.pos,1) as c
      from (select ename from emp where ename='KING') e,
      (select id as pos from t10) iter
      where iter.pos<=length(e.ename);
      
      select ename,iter.pos
      from(select ename from emp where ename='KING') e,
      (select id as pos from t10) iter;
      
      select id,ename from
      t10,
      (select 'KING' as ename) e
      where t10.id<=length(ename);
      
      select substr(ename,t10.id,1) as string
      from t10,
      (select 'KING' as ename) e;
      
      select substr(ename,t10.id,1) as string
      from t10,
      (select 'KING' as ename) e
      where t10.id<=length(ename);
      
      select substr(ename,t10.id,1) as string
      from t10,
      (select ename from emp where ename='king') e
      where t10.id<=length(ename);
      
      select
      substr(ename,iter.pos,length(ename)+1-iter.pos) as A,
      substr(ename,length(ename)+1-iter.pos,iter.pos) as B
      from
      (select id as pos from t10) iter,
      (select ename from emp where ename='KING') e
      where iter.pos<=length(e.ename);
      
      6.2
      select ''''';
      select 'apples core','apple''s core', case when '' is null then 0 else 1 end;
      
      6.3
      select (length('10,clark,manager')-length(replace('10,clark,manager',',','')))/length(',') as count;
      
      select
      (
      length('hello hello')-
      length(replace('hello hello','ll',''))
      )/length('ll')
      as correct,
      (
      length('hello hello')-
      length(replace('hello hello','ll',''))
      )
      as incorrect;
      
      6.4
      select ename,
      replace(
      replace(
      replace(
      replace(
      replace(
      ename,'U',''),'O',''),'I',''),'E',''),'A','')
      as stripped1,
      sal,
      replace(sal,'0','') as stripped2
      from 64_emp;
      
      6.6
      create view 66_view as
      select ename as data
      from emp
      where deptno=10
      union all
      select concat(ename,', $',sal,'.00') as data
      from emp
      where deptno=20
      union all
      select concat(ename,deptno) as data
      from emp
      where deptno=30;
      
      select data
      from (
      select v.data,iter.pos,
      substring(v.data,iter.pos,1) c,
      ascii(substring(v.data,iter.pos,1)) val
      from view_66 v,
      (select id as pos from t100) iter
      where iter.pos<=len(v.data)
      ) x
      group by data
      having min(val) between 48 and 122;
      
      6.7
      select
      substr('Stewie Griffin',iter.pos,1)
      from
      (select id as pos from t100) as iter
      where
      iter.pos<=length('Stewie Griffin');
      
      sql server
      select
      substrING('Stewie Griffin',iter.pos,1) as c,
      ASCII(substrING('Stewie Griffin',iter.pos,1)) as val
      from
      (select id as pos from t100) as iter
      where
      iter.pos<=LEN('Stewie Griffin');
      
      Mysql
      trim
      concat_ws
      substring_index
      
      select cas e
       when cnt=2 then
       trim(trailing '.' from
       concat_ws()
      )
      
      select name,length(name)-length(replace(name,' ','')) as cnt
      from(
      select replace('Stewie Griffin','.','') as name from t1
      ) x
      
      mysql解决方案
      
      select case
      when count=1
      then
      concat_ws('.',
      substr(substring_index(name,' ',1),1,1),
      substr(substring_index(name,' ',-1),1,1)
      )
      when count=2
      then
      concat_ws('.',
      substr(name,1,1),
      substr(name,length(substring_index(name,' ',1))+2,1),
      substr(substring_index(name,' ',3),1,1)
      )
      end as result
      from
      (
      select name,length(name)-length(replace(name,' ','')) as count
      from (select replace(trim(both ' ' from ' Stewie Griffin'),'.','') as name from t1) x
      ) y;
      
      mysql另一种解决方案
      select group_concat(c separator '.') as data
      from
      (
      select
      substr(x.name,iter.pos,1) c
      from
      (select 'Stewie Griffin' as name from t1) x,
      (select id as pos from t100) as iter
      where iter.pos<=length(x.name)
      and ascii(substr(x.name,iter.pos,1)) between 65 and 90
      ) y;
      
      6.8
      我的太臃肿
      select ename from
      (
      select
      ename,
      substr(ename,length(ename)-1,2) as enamec
      from emp
      )x
      order by enamec;
      
      标准答案
      select ename from emp
      order by substr(ename,length(ename)-1,2);
      
      6.9
      mysql
      select concat_ws(' ',e.ename,e.empno,d.dname) as data
      from emp e join dept d on e.deptno=d.deptno;
      
      sql server
      select e.ename+' '+CAST(e.empno as CHAR(4))+' '+d.dname as data
      from sqlcookbook.dbo.emp e join sqlcookbook.dbo.dept d on e.deptno=d.deptno;
      
      mysql
      select * from 68_view
      order by substring_index(data,' ',2);
      
      select substring_index(data,' ',3) as temp from 68_view;
      
      select substring_index(data,' ',-2) as temp from 68_view;
      
      select substring_index(substring_index(data,' ',-2),' ',1) as temp from 68_view;
      
      select * from 68_view
      order by substring_index(substring_index(data,' ',-2),' ',1);
      
      6.10
      mysql
      select deptno,group_concat(distinct ename order by empno separator '@') from emp group by deptno;
      
      sql server
      select count(*) over (partition by deptno) from emp;
      
      select deptno,count(*) over (partition by deptno),
      cast(ename as varchar(100)),
      empno,
      1
      from sqlcookbook.dbo.emp;
      
      with x(deptno,cnt,list,empno,len)
      as(
      select deptno,count(*) over (partition by deptno),
      cast(ename as varchar(100)),
      empno,
      1
      from sqlcookbook.dbo.emp
      union all
      select x.deptno,x.cnt,
      cast(x.list+','+e.ename as varchar(100)),
      e.empno,x.len+1
      from sqlcookbook.dbo.emp e,x
      where e.deptno=x.deptno
      and e.empno>x.empno
      )
      select * from x where deptno=10;
      
      with x(deptno,list,empno,cnt,len) as
      (
      select deptno,CAST(ename as varchar(100)),empno,COUNT(*) over (partition by deptno),1
      from sqlcookbook.dbo.emp
      union all
      select x.deptno,CAST(x.list+','+e.ename as varchar(100)),e.empno,x.cnt,x.len+1
      from x,sqlcookbook.dbo.emp e
      where x.deptno=e.deptno
      and x.empno<e.empno
      )
      select * from x where x.cnt=x.len order by 1;
      
      6.11
      mysql
      
      select
      substring_index(substring_index(val.list,',',iter.pos-1-(length(val.list)-length(replace(val.list,',',''))+1)),',',1) as empno
      from
      (select '7654,7698,7782,7788' as list from t1) val,
      (select id as pos from t10) iter
      where
      iter.pos<=length(val.list)-length(replace(val.list,',',''))+1;
      
      select
      substring_index(substring_index(val.list,',',iter.pos),',',-1) as empno
      from
      (select '7654,7698,7782,7788' as list from t1) val,
      (select id as pos from t10) iter
      where
      iter.pos<=length(val.list)-length(replace(val.list,',',''))+1;
      
      select * from emp where empno in
      (
      select
      substring_index(substring_index(val.list,',',iter.pos),',',-1) as empno
      from
      (select '7654,7698,7782,7788' as list from t1) val,
      (select id as pos from t10) iter
      where
      iter.pos<=length(val.list)-length(replace(val.list,',',''))+1
      );
      
      sql server
      select substring(c,2,charindex(',',c,2)-2) as emp
      from (
      select substring(csv.emps,iter.pos,len(csv.emps)) as c
      from
      (select ','+'7654,7698,7782,7788'+',' as emps from sqlcookbook.dbo.t1) csv,
      (select id as pos from sqlcookbook.dbo.t100) iter
      where iter.pos<=len(csv.emps)
      ) x
      where len(c)>1
      and substring(c,1,1)=',';
      
      6.12
      mysql
      select ename,group_concat(c order by c separator '') as xename
      from
      (select
      e.ename,substr(e.ename,iter.pos,1) as c
      from
      emp e,
      (select id as pos from t10) iter
      where
      iter.pos<=length(e.ename)
      ) x
      group by ename;
      
      sql server
      select
      ename,
      max(case when pos=1 then c else '' end)+
      max(case when pos=2 then c else '' end)+
      max(case when pos=3 then c else '' end)+
      max(case when pos=4 then c else '' end)+
      max(case when pos=5 then c else '' end)+
      max(case when pos=6 then c else '' end)
      from
      (
      select
      e.ename,
      substring(e.ename,iter.pos,1) as c,
      row_number() over(partition by e.ename order by substring(e.ename,iter.pos,1)) as pos
      from
      sqlcookbook.dbo.emp e,
      (select id as pos from sqlcookbook.dbo.t10) iter
      where iter.pos<=len(e.ename)
      )x
      group by ename;
      
      sql server
      select
      ename,
      case when pos=1 then c else '' end+
      case when pos=2 then c else '' end+
      case when pos=3 then c else '' end+
      case when pos=4 then c else '' end+
      case when pos=5 then c else '' end+
      case when pos=6 then c else '' end
      from
      (
      select
      e.ename,
      substring(e.ename,iter.pos,1) as c,
      row_number() over(partition by e.ename order by substring(e.ename,iter.pos,1)) as pos
      from
      sqlcookbook.dbo.emp e,
      (select id as pos from sqlcookbook.dbo.t10) iter
      where iter.pos<=len(e.ename)
      )x
      group by ename;
      没有加max提示错误 选择列表中的列 'x.pos' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
      
      6.13
      mysql
      create view 613_view as
      select concat(substr(ename,1,2),deptno,substr(ename,3,2)) as mixed from emp where deptno=10
      union all
      select empno from emp where deptno=20
      union all
      select ename from emp where deptno=30
      ;
      自己的解决方案
      select
      v.mixed,
      iter.pos as pos,
      substr(v.mixed,iter.pos,1) as c
      from
      613_view v,
      (select id as pos from t10) iter
      where
      iter.pos<=length(v.mixed)
      and ascii(substr(v.mixed,iter.pos,1)) between 48 and 57
      
      select
      mixed,
      group_concat(c order by pos separator '')
      from
      (
      select
      v.mixed,
      iter.pos as pos,
      substr(v.mixed,iter.pos,1) as c
      from
      613_view v,
      (select id as pos from t10) iter
      where
      iter.pos<=length(v.mixed)
      and ascii(substr(v.mixed,iter.pos,1)) between 48 and 57
      ) x
      group by mixed
      ;
      
      sql server自己的解决方案1书中未提供解决方案
      
      select substring(ename,1,2)+cast(deptno as varchar(4))+substring(ename,3,2) as mixed from sqlcookbook.dbo.emp where deptno=10
      union all
      select cast(empno as varchar(4)) from sqlcookbook.dbo.emp where deptno=20
      union all
      select ename from sqlcookbook.dbo.emp where deptno=30
      ;
      
      select
      mixed,
      iter.pos as pos,
      substring(v.mixed,iter.pos,1) as c
      from
      sqlcookbook.dbo.view_613 v,
      (select id as pos from sqlcookbook.dbo.t10) iter
      where
      iter.pos<=len(v.mixed)
      and ascii(substring(v.mixed,iter.pos,1)) between 48 and 57;
      
      select
      mixed,
      c,
      row_number() over(partition by mixed order by pos) as pos
      from
      (
      select
      mixed,
      iter.pos as pos,
      substring(v.mixed,iter.pos,1) as c
      from
      sqlcookbook.dbo.view_613 v,
      (select id as pos from sqlcookbook.dbo.t10) iter
      where
      iter.pos<=len(v.mixed)
      and ascii(substring(v.mixed,iter.pos,1)) between 48 and 57
      ) x;
      
      select
      mixed,
      max(case when pos=1 then c else '' end)+
      max(case when pos=2 then c else '' end)+
      max(case when pos=3 then c else '' end)+
      max(case when pos=4 then c else '' end) as data
      from
      (
      select
      mixed,
      c,
      row_number() over(partition by mixed order by pos) as pos
      from
      (
      select
      mixed,
      iter.pos as pos,
      substring(v.mixed,iter.pos,1) as c
      from
      sqlcookbook.dbo.view_613 v,
      (select id as pos from sqlcookbook.dbo.t10) iter
      where
      iter.pos<=len(v.mixed)
      and ascii(substring(v.mixed,iter.pos,1)) between 48 and 57
      ) x
      )y
      group by mixed;
      
      610对自身迭代连接列值?
      
      select * from sqlcookbook.dbo.view_613 where ISNUMERIC(mixed)=1;sql server将数据找出来?
      
      6.14
      mysql
      create view 614_view as
      select 'mo,larry,curly' as name
      from t1
      union all
      select 'tina,gina,jaunita,regina,leena' from t1;
      自己的解决方案
      select substring_index(substring_index(name,',',2),',',-1) as name from 614_view;
      一个未完成的思路
      select
      v.name,
      iter.pos,
      substr(v.name,iter.pos,1) as c
      from
      614_view v,
      (select id as pos from t100) iter
      where
      iter.pos<=length(v.name)
      order by name,pos;
      很类似的方案
      
      select
      v.name,
      iter.pos
      from
      614_view v,
      (select id as pos from t10) iter
      where
      iter.pos<=length(v.name)-length(replace(v.name,',',''))+1
      order by name,pos;
      
      select
      v.name,
      iter.pos,
      substring_index(substring_index(name,',',iter.pos),',',-1) as res
      from
      614_view v,
      (select id as pos from t10) iter
      where
      iter.pos<=length(v.name)-length(replace(v.name,',',''))+1
      order by name,pos;
      
      select res from
      (
      select
      v.name,
      iter.pos,
      substring_index(substring_index(name,',',iter.pos),',',-1) as res
      from
      614_view v,
      (select id as pos from t10) iter
      where
      iter.pos<=length(v.name)-length(replace(v.name,',',''))+1
      order by name,pos
      ) x
      where pos=2;
      
      select
      v.name,
      iter.pos
      from
      614_view v,
      (select id as pos from t100) iter
      where
      iter.pos<=length(v.name) and substr(v.name,iter.pos,1)=','
      order by name,pos;
      
      
      sql server
      select 'mo,larry,curly' as name
      union all
      select 'tina,gina,jaunita,regina,leena';
      自己的解决方案
      select SUBSTRING(name,CHARINDEX(',',name)+1,LEN(name)) as name from sqlcookbook.dbo.view_614;
      
      select SUBSTRING(name,1,CHARINDEX(',',name)-1) from
      (
      select SUBSTRING(name,CHARINDEX(',',name)+1,LEN(name)) as name from sqlcookbook.dbo.view_614
      ) x;
      
      更通用化的方案
      select ','+v.name+',' as name,iter.pos from
      sqlcookbook.dbo.view_614 v,
      (select id as pos from sqlcookbook.dbo.t10) iter
      where iter.pos<=len(v.name)+2
      order by name,pos;
      
      select
      name,
      SUBSTRING(name,pos,LEN(name)),
      pos
      from
      (
      select ','+v.name+',' as name,iter.pos from
      sqlcookbook.dbo.view_614 v,
      (select id as pos from sqlcookbook.dbo.t10) iter
      where iter.pos<=len(v.name)+2
      ) x
      order by name,pos;
      
      select
      name,
      ROW_NUMBER() over(partition by name order by pos) as num,
      SUBSTRING(temp,2,CHARINDEX(',',temp,2)-2) as res
      from
      (
      select
      name,
      SUBSTRING(name,pos,LEN(name)) as temp,
      pos
      from
      (
      select ','+v.name+',' as name,iter.pos from
      sqlcookbook.dbo.view_614 v,
      (select id as pos from sqlcookbook.dbo.t10) iter
      where iter.pos<=len(v.name)+2
      )x
      )y
      where SUBSTRING(temp,1,1)=',' and LEN(temp)>1;
      
      select res from
      (
      select
      name,
      ROW_NUMBER() over(partition by name order by pos) as pos,
      SUBSTRING(temp,2,CHARINDEX(',',temp,2)-2) as res
      from
      (
      select
      name,
      SUBSTRING(name,pos,LEN(name)) as temp,
      pos
      from
      (
      select ','+v.name+',' as name,iter.pos from
      sqlcookbook.dbo.view_614 v,
      (select id as pos from sqlcookbook.dbo.t10) iter
      where iter.pos<=len(v.name)+2
      )x
      )y
      where SUBSTRING(temp,1,1)=',' and LEN(temp)>1
      )z
      where pos=2;
      
      6.15
      mysql
      create table table_615(ip varchar(15));
      insert into table_615 values('192.168.1.1'),('168.0.1.255');
      
      select
      substring_index(ip,'.',1) as A,
      substring_index(substring_index(ip,'.',2),'.',-1) as B,
      substring_index(substring_index(ip,'.',3),'.',-1) as C,
      substring_index(substring_index(ip,'.',4),'.',-1) as D
      from table_615;
      
      sql server
      INSERT INTO [sqlcookbook].[dbo].[table_615]
       ([ip])
       VALUES
       ('192.168.1.1'),('168.0.1.255');
      GO
      
      未使用递归 无法保证按照原顺序输出
      select
      temp.ip,
      iter.pos,
      SUBSTRING(temp.ip,iter.pos,LEN(temp.ip)) as temp
      from
      (select '.'+ip+'.' as ip from sqlcookbook.dbo.table_615) temp,
      (select id as pos from sqlcookbook.dbo.t100) as iter
      where
      iter.pos<=LEN(temp.ip);
      
      select
      ROW_NUMBER() over(partition by ip order by pos) as num,
      SUBSTRING(temp,2,CHARINDEX('.',temp,2)-2) as ipdiv,
      ip
      from
      (
      select
      temp.ip,
      iter.pos,
      SUBSTRING(temp.ip,iter.pos,LEN(temp.ip)) as temp
      from
      (select '.'+ip+'.' as ip from sqlcookbook.dbo.table_615) temp,
      (select id as pos from sqlcookbook.dbo.t100) as iter
      where
      iter.pos<=LEN(temp.ip)
      )x
      where SUBSTRING(temp,1,1)='.' and LEN(temp)>1;
      
      select
      MAX(case when num=1 then ipdiv end) as A,
      MAX(case when num=2 then ipdiv end) as B,
      MAX(case when num=3 then ipdiv end) as C,
      MAX(case when num=4 then ipdiv end) as D,
      ip
      from
      (
      select
      ROW_NUMBER() over(partition by ip order by pos) as num,
      SUBSTRING(temp,2,CHARINDEX('.',temp,2)-2) as ipdiv,
      ip
      from
      (
      select
      temp.ip,
      iter.pos,
      SUBSTRING(temp.ip,iter.pos,LEN(temp.ip)) as temp
      from
      (select '.'+ip+'.' as ip from sqlcookbook.dbo.table_615) temp,
      (select id as pos from sqlcookbook.dbo.t100) as iter
      where
      iter.pos<=LEN(temp.ip)
      )x
      where SUBSTRING(temp,1,1)='.' and LEN(temp)>1
      )y
      group by ip;
      
      书中的方案不值得仔细推敲的,如何遍历行?游标?
      with x(pos,ip) as
      (
      select 1 as pos,'.92.111.0.222' as ip from sqlcookbook.dbo.t1
      union all
      select pos+1,ip from x where pos+1<=20
      )
      select * from x;
      
      with x(pos,ip) as
      (
      select 1 as pos,'.92.111.0.222' as ip from sqlcookbook.dbo.t1
      union all
      select pos+1,ip from x where pos+1<=20
      )
      select
      pos,
      ip,
      right(ip,pos) as c,
      substring(right(ip,pos),2,len(ip)) as d
      from x
      where pos<=len(ip)
      and substring(right(ip,pos),1,1)='.';
      
      7.6
      select e.ename,e.empno,e.sal,
      (select sum(sal) from emp d where d.empno<=e.empno) as running_sal
      from emp e order by empno;
      
      select e.ename as enmae1,e.empno as empno1,e.sal as sal1,
      d.ename as ename2,d.empno as empno2,d.sal as sal2
      from emp e,emp d
      where d.empno<e.empno
      and e.empno=7566;
      
      7.7
      select e.ename,e.empno,e.sal,
      (select exp(sum(ln(d.sal))) from emp d where d.empno<=e.empno) as x
      from emp e order by e.deptno,e.empno;
      
      select e.ename,e.empno,e.sal,
      (select exp(sum(ln(d.sal))) from emp d where d.empno<=e.empno and e.deptno=d.deptno) as x
      from emp e order by e.deptno,e.empno;
      
      select e.ename,e.empno,e.sal,
      (select exp(sum(ln(d.sal))) from emp d where d.empno<=e.empno and d.deptno=e.deptno) as x
      from emp e where e.deptno=10 order by e.deptno,e.empno;
      
      
      select e.ename,e.empno,e.sal,
      (select exp(sum(ln(d.sal))) from emp d where d.empno<=e.empno and d.deptno=e.deptno) as x
      from emp e order by e.deptno,e.empno;
      
      7.8
      更优秀的方案?游标?
      select e.*,
      ((select sum(-d.sal) from emp d where d.sal<=e.sal)+(select min(sal) from emp)*2) as x
      from emp e order by e.sal;
      
      select e.*,
      ((select sum(-d.sal) from emp d where d.sal<=e.sal and d.deptno=e.deptno)+(select min(sal) from emp d where d.deptno=e.deptno)*2) as x
      from emp e order by e.deptno,e.sal;
      
      原书方案错误
      select a.empno,a.ename,a.sal,
      (select case when a.empno=min(b.empno) then sum(b.sal)
      else sum(-b.sal)
      end
      from emp b
      where b.empno<=a.empno
      and b.deptno=a.deptno) as rnk
      from emp a
      order by deptno,sal;
      
      select a.empno,a.ename,a.sal,
      (select case when a.empno=min(b.empno) then sum(b.sal)
      else sum(-b.sal)
      end
      from emp b
      where b.empno<=a.empno
      and b.deptno=a.deptno) as rnk
      from emp a
      order by deptno,empno;
      
      7.9
      sqlserver可以实现求每个deptno中sal的众数
      select deptno,sal,COUNT(*) from sqlcookbook.dbo.emp group by sal,deptno;
      
      select deptno,sal,DENSE_RANK() over(partition by deptno order by cnt desc) from
      (select deptno,sal,COUNT(*) as cnt from sqlcookbook.dbo.emp group by sal,deptno) x;
      
      select * from
      (
      select deptno,sal,DENSE_RANK() over(partition by deptno order by cnt desc) as rnk from
      (select deptno,sal,COUNT(*) as cnt from sqlcookbook.dbo.emp group by sal,deptno) x
      ) y
      where rnk=1;
      
      mysql
      select sal from emp where deptno=20 group by sal
      having count(*)>=all(select count(*) from emp where deptno=20 group by sal);
      找出每一个deptno的sal的众数?
      select deptno,sal,count(*) from emp group by deptno,sal;
      with x(select deptno,sal,count(*) from emp group by deptno,sal) select * from x;
      
      7.10
      mysql
      select e.sal from emp e,emp d
      where e.deptno=d.deptno and e.deptno=20
      and sum(case when e.sal=d.sal then 1 else 0 end)>=abs(sum(sign(e.sal-d.sal)))
      group by e.sal;
      错误。聚合函数不能用在where里面?
      
      select e.sal from emp e,emp d
      where e.deptno=d.deptno and e.deptno=20
      group by e.sal
      having sum(case when e.sal=d.sal then 1 else 0 end)>=abs(sum(sign(e.sal-d.sal)));
      
      create table t_710 (sal integer);
      insert into t_710 values(1),(2),(3),(4),(5),(6);
      
      select a.sal from t_710 a,t_710 b
      group by a.sal
      having abs(sum(sign(a.sal-b.sal)))<=1;失败,必须使用书中的方案
      
      create table t_7101 (sal integer);
      insert into t_7101 values(1),(2),(2),(2),(3),(4),(5);
      
      select a.sal from t_7101 a,t_7101 b
      group by a.sal
      having abs(sum(sign(a.sal-b.sal)))<=1;
      
      select a.sal,abs(sum(sign(a.sal-b.sal))) as index1,sum(case when a.sal=b.sal then 1 else 0 end) as index2 from t_7101 a,t_7101 b group by a.sal;
      -----------------
      提取出每一个deptno下sal的中位数
      select a.deptno,a.sal,b.sal from emp a,emp b where a.deptno=b.deptno order by a.deptno,a.sal,b.sal;
      
      select a.deptno,a.sal,
      sum(case when a.sal=b.sal then 1 else 0 end) as index1,
      abs(sum(sign(a.sal-b.sal))) as index2
      from emp a,emp b
      where a.deptno=b.deptno
      group by a.deptno,a.sal;
      
      select a.sal,a.deptno,
      (abs(sum(sign(a.sal-b.sal)))-sum(case when a.sal=b.sal then 1 else 0 end)) as inex
      from emp a,emp b
      where a.deptno=b.deptno
      group by a.sal,a.deptno;
      
      错误?
      select a.sal,a.deptno
      from emp a,emp b
      where a.deptno=b.deptno
      having (abs(sum(sign(a.sal-b.sal)))-sum(case when a.sal=b.sal then 1 else 0 end))<=0
      group by a.sal,a.deptno;
      错误?
      
      select sal,deptno from(
      select a.sal,a.deptno,
      (abs(sum(sign(a.sal-b.sal)))-sum(case when a.sal=b.sal then 1 else 0 end)) as index1
      from emp a,emp b
      where a.deptno=b.deptno
      group by a.sal,a.deptno
      ) x
      where index1<=0;
      
      select deptno,avg(sal) as midd from(
      select a.sal,a.deptno,
      (abs(sum(sign(a.sal-b.sal)))-sum(case when a.sal=b.sal then 1 else 0 end)) as index1
      from emp a,emp b
      where a.deptno=b.deptno
      group by a.sal,a.deptno
      ) x
      where index1<=0
      group by deptno;
      
      sql server
      select sal,
      count(*) over() total,
      cast(count(*) over() as decimal)/2 mid,
      ceiling(cast(count(*) over() as decimal)/2) next,
      row_number() over(order by sal) rn
      from sqlcookbook.dbo.emp
      where deptno=20;
      
      select AVG(sal) from(
      select sal,
      count(*) over() total,
      cast(count(*) over() as decimal)/2 mid,
      ceiling(cast(count(*) over() as decimal)/2) next,
      row_number() over(order by sal) rn
      from sqlcookbook.dbo.emp
      where deptno=20
      ) x
      where (total%2=0 and rn in (mid,mid+1))
      or (total%2=1 and rn=next);
      
      select deptno,sal,ROW_NUMBER() over(partition by deptno order by sal) as id,
      count(*) over(partition by deptno) as cnt
      from sqlcookbook.dbo.emp;
      
      select deptno,AVG(sal) from(
      select deptno,sal,ROW_NUMBER() over(partition by deptno order by sal) as id,
      count(*) over(partition by deptno) as cnt
      from sqlcookbook.dbo.emp
      ) x
      where (cnt%2=0 and id in(cnt/2,cnt/2+1))
      or (cnt%2=1 and id=ceiling(CAST(cnt as decimal)/2))
      group by deptno;
      
      7.11
      mysql
      select (select sum(sal) from emp where deptno=10)*100/(select sum(sal) from emp);
      select sum(case when deptno=10 then sal else 0 end)*100/sum(sal) from emp;
      select deptno,sum(sal) as ds from emp group by deptno;
      select deptno,ds*100/(select sum(sal) from emp) as p from(
      select deptno,sum(sal) as ds from emp group by deptno
      )x;
      sql server
      select (SUM(case when deptno=10 then cast(sal as decimal) else 0 end)*100/SUM(cast(sal as decimal))) as pct from sqlcookbook.dbo.emp;
      select distinct deptno,SUM(sal) over(partition by deptno) as dsal,SUM(sal) over() as total from sqlcookbook.dbo.emp;
      select deptno,dsal*100/total from(
      select distinct deptno,SUM(cast(sal as decimal)) over(partition by deptno) as dsal,SUM(cast(sal as decimal)) over() as total from sqlcookbook.dbo.emp
      )x;
      
      7.12
      mysql
      select deptno,sum(coalesce(comm,0))/count(*) as avgcomm from emp group by deptno;
      select deptno,avg(coalesce(comm,0)) as avgcomm from emp group by deptno;
      select deptno,avg(comm) as avgcomm from emp group by deptno;
      
      7.13
      mysql
      只能去掉一个最高值和一个最低值
      select deptno,(sum(sal)-max(sal)-min(sal))/(count(*)-2) as xavgsal from emp group by deptno;
      最高值和最低值可能有重复出现的
      select avg(sal) from(
      select sal from emp where sal not in
      (
      (select max(sal) from emp),
      (select min(sal) from emp)
      ))x;
      
      select sal,max(sal) maxsal,min(sal) minsal from emp;错误结果
      select e.sal,a.maxsal,a.minsal from
      emp e,(select max(sal) as maxsal,min(sal) as minsal from emp) a;
      select avg(sal) from(
      select e.sal,a.maxsal,a.minsal from
      emp e,(select max(sal) as maxsal,min(sal) as minsal from emp) a
      ) x
      where sal not in(maxsal,minsal);
      每一个deptno去掉一个最大值,去掉一个最小值后的平均值 使用集合的思想!
      select deptno,max(sal) as maxsal,min(sal) as minsal from emp group by deptno;
      
      select e.deptno,e.sal,a.maxsal,a.minsal from emp e,
      (select deptno,max(sal) as maxsal,min(sal) as minsal from emp group by deptno) a
      where a.deptno=e.deptno order by deptno,sal;
      
      select deptno,avg(sal) from(
      select e.deptno,e.sal,a.maxsal,a.minsal from emp e,
      (select deptno,max(sal) as maxsal,min(sal) as minsal from emp group by deptno) a
      where a.deptno=e.deptno
      ) x where sal not in(maxsal,minsal) group by deptno;
      
      sql server
      select sal,max(sal) over() as maxsal,min(sal) over() as minsal from sqlcookbook.dbo.emp;
      
      select AVG(sal) from(
      select sal,max(sal) over() as maxsal,min(sal) over() as minsal from sqlcookbook.dbo.emp
      )x
      where sal not in(maxsal,minsal);
      
      每一个deptno去掉一个最大值,去掉一个最小值后的平均值 使用集合的思想
      select deptno,sal,MAX(sal) over(partition by deptno) as maxsal,
      MIN(sal) over(partition by deptno) as minsal from sqlcookbook.dbo.emp order by deptno,sal;
      
      select deptno,AVG(sal) from(
      select deptno,sal,MAX(sal) over(partition by deptno) as maxsal,
      MIN(sal) over(partition by deptno) as minsal from sqlcookbook.dbo.emp
      ) x where sal not in(maxsal,minsal) group by deptno;
      
      7.14
      mysql书中无此解决方案
      create view view_714 as select concat(ename,hiredate) as str from emp;
      
      select v.str,substr(v.str,iter.pos,1) as substr from view_714 v,(select id as pos from t100) iter where iter.pos<=length(v.str) order by str;不加iter.pos的后果,乱序
      
      select v.str,iter.pos,substr(v.str,iter.pos,1) as substr from view_714 v,(select id as pos from t100) iter where iter.pos<=length(v.str) order by v.str,iter.pos;
      
      select v.str,iter.pos,substr(v.str,iter.pos,1) as substr
      from view_714 v,(select id as pos from t100) iter
      where iter.pos<=length(v.str) and ascii(substr(v.str,iter.pos,1)) between 48 and 57 order by str,pos;
      
      select group_concat(substr) from(
      select v.str,iter.pos,substr(v.str,iter.pos,1) as substr
      from view_714 v,(select id as pos from t100) iter
      where iter.pos<=length(v.str) and ascii(substr(v.str,iter.pos,1)) between 48 and 57
      )x group by str;乱序
      
      select group_concat(substr,'') from(
      select v.str,iter.pos,substr(v.str,iter.pos,1) as substr
      from view_714 v,(select id as pos from t100) iter
      where iter.pos<=length(v.str) and ascii(substr(v.str,iter.pos,1)) between 48 and 57
      order by str,pos
      )x group by str;乱序
      
      select str,group_concat(substr order by pos separator '') as result from(
      select v.str,iter.pos,substr(v.str,iter.pos,1) as substr
      from view_714 v,(select id as pos from t100) iter
      where iter.pos<=length(v.str) and ascii(substr(v.str,iter.pos,1)) between 48 and 57
      order by str,pos
      )x group by str;
      
      sqlserver书中无此解决方案
      view_714
      select ename+cast(sal as varchar) as str from sqlcookbook.dbo.emp;
      解决方案一 将多行聚合成一行
      select iter.pos,v.str,SUBSTRING(v.str,iter.pos,1) as sub from sqlcookbook.dbo.view_714 v,
      (select id as pos from t100) iter where iter.pos<=LEN(v.str) and ISNUMERIC(SUBSTRING(v.str,iter.pos,1))=1;
      
      select ROW_NUMBER() over(partition by str order by pos) as cnt,STR,sub from(
      select iter.pos,v.str,SUBSTRING(v.str,iter.pos,1) as sub from sqlcookbook.dbo.view_714 v,
      (select id as pos from t100) iter where iter.pos<=LEN(v.str) and ISNUMERIC(SUBSTRING(v.str,iter.pos,1))=1
      ) x;
      
      select STR,
      max(case when cnt=1 then sub else '' end)+
      max(case when cnt=2 then sub else '' end)+
      max(case when cnt=3 then sub else '' end)+
      max(case when cnt=4 then sub else '' end)+
      max(case when cnt=5 then sub else '' end)+
      max(case when cnt=6 then sub else '' end) as res from(
      select ROW_NUMBER() over(partition by str order by pos) as cnt,STR,sub from(
      select iter.pos,v.str,SUBSTRING(v.str,iter.pos,1) as sub from sqlcookbook.dbo.view_714 v,
      (select id as pos from t100) iter where iter.pos<=LEN(v.str) and ISNUMERIC(SUBSTRING(v.str,iter.pos,1))=1
      ) x) y group by STR;
      
      解决方案二 递归的调用自身?必须建中间表
      
      7.15
      mysql
      create view view_715(id,amt,trx) as
      select 1,100,'PR' from t1 union all
      select 2,100,'PR' from t1 union all
      select 3,50, 'PY' from t1 union all
      select 4,100,'PR' from t1 union all
      select 5,200,'PY' from t1 union all
      select 6,50, 'PY' from t1;
      
      标量子查询
      select
      case when a.trx='PR' then 'PURCHASE' else 'PAYMENT' end as trx_type,
      a.amt,
      (select sum(case when b.trx='PR' then b.amt else -1*b.amt end) from view_715 b where b.id<=a.id) as balance
      from view_715 a;
      集合论
      select id,case when trx='PR' then amt else -amt end as amtx from view_715;
      
      select * from view_715 a,
      (select id,case when trx='PR' then amt else -amt end as amtx from view_715) b
      where b.id<=a.id order by a.id,b.id;
      
      select a.*,b.id as idb,b.amtx from view_715 a,
      (select id,case when trx='PR' then amt else -amt end as amtx from view_715) b
      where b.id<=a.id order by a.id,b.id;
      
      select
      case when trx='PR' then 'PURCHASE' else 'PAYMENT' end as TRX_TYPE,amt,
      sum(amtx) as balance from(
      select a.*,b.id as idb,b.amtx from view_715 a,
      (select id,case when trx='PR' then amt else -amt end as amtx from view_715) b
      where b.id<=a.id) x group by id;
      ***************************************************************************
      附录A:
      select ename,deptno,count(*) over() as cnt
      from sqlcookbook.dbo.emp
      order by 2;
      
      select ename,deptno,count(*) over() as cnt
      from sqlcookbook.dbo.emp
      where deptno=10
      order by 2;
      
      select ename,deptno,COUNT(*) over(partition by deptno) as cnt
      from sqlcookbook.dbo.emp order by 2;
      
      mysql分组的解决方案
      select e.ename,e.deptno,
      (select count(*) from emp d
      where e.deptno=d.deptno) as cnt
      from emp e
      order by 2;
      
      select (select count(*) as cnt from emp d where d.deptno=e.deptno) as test from emp e;
      
      sql server
      select
      ename,
      deptno,
      count(*) over(partition by deptno) as dept_cnt,
      job,
      count(*) over(partition by job) as job_cnt
      from sqlcookbook.dbo.emp
      order by 2;
      
      mysql
      select e.ename,e.deptno,
      (select count(*) from emp d where e.deptno=d.deptno) as dept_cnt,
      job,
      (select count(*) from emp d where e.job=d.job) as job_cnt
      from emp e order by 2;
      
      sqlserver
      select comm,count(*) over(partition by comm) as cnt
      from sqlcookbook.dbo.emp;
      
      select comm,COUNT(comm) over(partition by comm) as cnt
      from sqlcookbook.dbo.emp;
      select coalesce(comm,-1) as comm,COUNT(comm) over(partition by comm) as cnt
      from sqlcookbook.dbo.emp order by 1 desc;
      
      mysql
      
      select e.comm,(select count(*) from emp d where d.comm=e.comm) as cnt from emp e;
      
      select coalesce(comm,-1) as comm from emp;
      
      select e.comm,
      (select count(coalesce(d.comm,-1)) from emp d where coalesce(d.comm,-1)=coalesce(e.comm,-1)) as cnt from emp e
      order by 1;
      
      over order by子句问题
      sql server加order by 通不过?P465
      select deptno,ename,hiredate,sal,
      sum(sal) over(partition by deptno) as total1,
      sum(sal) over() as total2,
      sum(sal) over(order by hiredate) as total3,
      sum(sal) over(partition by deptno order by hiredate) as total4
      from sqlcookbook.dbo.emp;
      
      框架子句 sqlserver通不过
      select deptno,
      ename,
      hiredate,
      sal,
      sum(sal) over(partition by deptno) as total1,
      sum(sal) over() as total2,
      sum(sal) over(order by hiredate range between unbounded preceding and current row) as total3
      from emp
      where deptno=10;
      
      附录B 回顾Rozenshtein《the essence of sql》
      create table student(
      sno integer,
      sname varchar(10),
      age integer
      );
      
      create table courses(
      cno varchar(5),
      title varchar(10),
      credits integer
      );
      
      create table professor(
      lname varchar(10),
      dept varchar(10),
      salary integer,
      age integer
      );
      
      create table take(
      sno integer,
      cno varchar(5)
      );
      
      create table teach(
      lname varchar(10),
      cno varchar(5)
      );
      
      insert into student values
      (1,'AARON',20),
      (2,'CHUCK',21),
      (3,'DOUG',20),
      (4,'MAGGIE',19),
      (5,'STEVE',22),
      (6,'JING',18),
      (7,'BRIAN',21),
      (8,'KAY',20),
      (9,'GILLIAN',20),
      (10,'CHAD',21);
      
      insert into courses values
      ('CS112','PHYSICS',4),('CS113','CALCULUS',4),('CS114','HISTORY',4);
      
      insert into professor values
      ('CHOI','SCIENCE',400,45),('GUNN','HISTORY',300,60),('MAYER','MATH',400,55),('POMEL','SCIENCE',500,65),('FEUER','MATH',400,40);
      
      insert into take values(1,'CS112'),(1,'CS113'),(1,'CS114'),(2,'CS112'),(3,'CS112'),(3,'CS114'),(4,'CS112'),(4,'CS113'),(5,'CS113'),(6,'CS113'),(6,'CS114');
      
      insert into teach values('CHOI','CS112'),('CHOI','CS113'),('CHOI','CS114'),('POMEL','CS113'),('MAYER','CS112'),('MAYER','CS114');
      
      问题一
      mysql
      原始方案
      select * from student where sno not in (select sno from take where cno='CS112');
      集合论
      select a.*,b.* from student a left join take b on a.sno=b.sno;
      select a.*,b.* from student a left join take b on a.sno=b.sno group by a.sno,a.sname,a.age having max(case when b.cno='CS112' then 1 else 0 end)=0;
      sql server
      集合论的思路
      select s.*,t.* from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno;
      
      select s.*,case when t.cno='CS112' then 1 else 0 end as flag
      from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno;
      
      select s.* from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno
      group by s.sno,s.sname,s.age
      having MAX(case when t.cno='CS112' then 1 else 0 end)=0;
      
      sql server
      一
      select s.*,t.* from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno;
      
      select s.*,case when t.cno='CS112' then 1 else 0 end as flag
      from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno;
      
      select s.* from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno
      group by s.sno,s.sname,s.age
      having MAX(case when t.cno='CS112' then 1 else 0 end)=0;
      二
      select s.*,
      MAX(case when t.cno='CS112' then 1 else 0 end) as takecs112
      from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno group by s.sno,s.age,s.sname;
      
      select sno,sname,age from(
      select s.*,
      MAX(case when t.cno='CS112' then 1 else 0 end) as takecs112
      from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno group by s.sno,s.age,s.sname
      ) x where takecs112=0;
      三
      select s.*,MAX(case when t.cno='CS112' then 1 else 0 end) over(partition by s.sno) as takecs112
      from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno;
      
      select sno,sname,age from(
      select s.*,MAX(case when t.cno='CS112' then 1 else 0 end) over(partition by s.sno) as takecs112
      from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno
      )x where takecs112=0;
      
      问题二
      mysql
      select s.*,t.* from student s left join take t on s.sno=t.sno;
      select s.* from student s left join take t on s.sno=t.sno group by s.sno,s.sname,s.age having sum(case when t.cno in ('CS112','CS114') then 1 else 0 end)=1;
      
      sql server
      select s.*,SUM(case when t.cno in ('CS112','CS114') then 1 else 0 end) over(partition by s.sno) as cnt
      from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno;
      
      select distinct sno,sname,age from(
      select s.*,SUM(case when t.cno in ('CS112','CS114') then 1 else 0 end) over(partition by s.sno) as cnt
      from rozen.dbo.student s left join rozen.dbo.take t on s.sno=t.sno)x where cnt=1;
      
      先查出既选了112也选了114的sno
      集合论 自连接
      select a.*,b.* from take a,take b where a.sno=b.sno order by a.sno,a.cno,b.cno;
      select a.*,b.* from take a,take b where a.sno=b.sno and a.cno='CS112' and b.cno='CS114';
      select s.sno,s.sname,s.age from student s inner join take t on s.sno=t.sno
      select s.* from student s inner join take t on s.sno=t.sno
      where t.cno in ('CS112','CS114') and s.sno not in(
      select a.sno from take a,take b where a.sno=b.sno and a.cno='CS112' and b.cno='CS114'
      );
      统计
      select sno from take group by sno having sum(case when cno in ('CS112','CS114') then 1 else 0 end)=2;
      
      问题三
      mysql
      一
      select s.*,t.cno from student s,take t where s.sno=t.sno group by s.sno having count(*)=1 and t.cno='CS112';
      二
      select s.* from student s,take t where s.sno=t.sno and t.cno='CS112' and s.sno in
      (select sno from take group by sno having count(*)=1);
      三
      select s.*,t.cno,count(*) from student s,take t where s.sno=t.sno group by s.sno,s.sname,s.age;
      select sno,sname,age from(
      select s.*,t.cno,count(*) as cnt from student s,take t where s.sno=t.sno group by s.sno,s.sname,s.age)x where cno='CS112' and cnt=1;
      
      sql server
      select s.*,t.cno,COUNT(*) over(partition by t.sno) as cnt
      from rozen.dbo.student s,rozen.dbo.take t where s.sno=t.sno;
      
      select sno,sname,age from(
      select s.*,t.cno,COUNT(*) over(partition by t.sno) as cnt
      from rozen.dbo.student s,rozen.dbo.take t where s.sno=t.sno
      )x where cnt=1 and cno='CS112';
      集合论
      select t.sno from take t,(select sno from take group by sno having count(*)=1) flag
      where t.sno=flag.sno and t.cno='CS112';
      
      select s.* from student s,take t,
      (select sno from take group by sno having count(*)=1) sigle
      where s.sno=t.sno and t.cno='CS112' and t.sno=sigle.sno;
      原始解决方案 perfect!
      select s.* from student s,take t where s.sno=t.sno and s.sno not in(
      select sno from take where cno!='CS112');
      
      问题四
      mysql
      select s.* from student s,take t where s.sno=t.sno group by sno having count(*)<=2;
      
      sql server
      select s.*,COUNT(*) over(partition by s.sno) as cnt from rozen.dbo.student s,rozen.dbo.take t
      where s.sno=t.sno;
      
      select distinct sno,sname,age from(
      select s.*,COUNT(*) over(partition by s.sno) as cnt from rozen.dbo.student s,rozen.dbo.take t
      where s.sno=t.sno) x where cnt<=2
      
      原始解决方案:集合论
      不使用聚集函数抽取出选课数量小于2的学生
      select a.*,b.*,c.* from take a,take b,take c where a.sno=b.sno and b.sno=c.sno and a.cno<b.cno and b.cno<c.cno;
      select distinct s.* from student s,take t where s.sno=t.sno and s.sno not in(
      select a.sno from take a,take b,take c where a.sno=b.sno and b.sno=c.sno and a.cno<b.cno and b.cno<c.cno);
      
      问题五
      mysql
      有几种情况要考虑11123,1233,1223
      一 集合论
      select a.*,b.* from student a left join student b on b.age<a.age order by a.sno,b.age;
      select a.* from student a left join student b on b.age<a.age
      group by a.sno having count(*)<=2;
      二
      select count(*) from student where age<18;
      select a.* from student a
      where (select count(*) from student b where b.age<a.age)<=2;
      
      sql server内置函数DENSE_RANK()
      select s.*,DENSE_RANK() over(order by s.age) as rnk from rozen.dbo.student s;
      select sno,sname,age from(
      select s.*,DENSE_RANK() over(order by s.age) as rnk from rozen.dbo.student s
      ) x where rnk<=3;
      
      原始解决方案 集合论 没有任何的聚合函数 仅仅通过集合处理所有问题。
      select a.* from student a,student b,student c,student d
      where a.age>b.age and b.age>c.age and c.age>d.age group by sno;
      
      select s.* from student s where sno not in(
      select a.sno from student a,student b,student c,student d
      where a.age>b.age and b.age>c.age and c.age>d.age);
      
      问题六
      只用集合,不用聚合函数
      select a.sno from take a,take b where a.sno=b.sno and a.cno>b.cno;
      select s.* from student s where s.sno in(select a.sno from take a,take b where a.sno=b.sno and a.cno>b.cno);
      select distinct s.* from student s,take a,take b where s.sno=a.sno and a.sno=b.sno and a.cno>b.cno;可能是效率的问题
      mysql
      select s.* from student s,take t where s.sno=t.sno group by s.sno having count(*)>1;
      
      select s.*,(select count(*) as cnt from take t where t.sno=s.sno) from student s;
      select sno,sname,age from(
      select s.*,(select count(*) from take t where t.sno=s.sno) as cnt from student s) x where cnt>1;
      
      sql server
      select s.*,COUNT(*) over(partition by t.sno) as cnt from rozen.dbo.student s,rozen.dbo.take t
  •     本书的各个例子将使用SQL的各种情况都讲得很详细,并且思路清晰,一次性读完意义不大,还是比较适合在寻求解决方案的时候翻一翻。
  •     朋友很早以前就推荐过这本,但一直没时间读,近来借朋友的读了读,感觉很不错,很开阔思路,也了解了一些以前不常用的但很实用的函数。美中不满的就是感觉翻译的一般而且有错别字。不过感觉还是值得一读的。
  •     内容比较多,以例子的讲解为主,很多解决方法令人耳目一新,但是有些东西感觉重复太多,比如说字符的处理,讲来讲去其实就是一个东西,非要把几个雷同的例子翻来覆去地捣弄,读起来有点疲劳,另外一些针对ORACLE的解决方案,在10g中可以换用正则表达式轻松搞定,此书更适合遇到问题时查阅。
      
      最精彩的章节当属附录部分,值得推荐。另外翻译还是有些问题,比较生硬。
      
      适合开发人员吧,DBA就不推荐了。
 

250万本中文图书简介、评论、评分,PDF格式免费下载。 第一图书网 手机版

京ICP备13047387号-7