SQL Cookbook中文版

用戶評(píng)論 (總計(jì)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
  　　錯(cuò)誤
  　　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);
  　　錯(cuò)誤有問(wèn)題
  　　
  　　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;
  　　
  　　標(biāo)準(zhǔn)答案
  　　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;
  　　沒(méi)有加max提示錯(cuò)誤 選擇列表中的列 'x.pos' 無(wú)效，因?yàn)樵摿袥](méi)有包含在聚合函數(shù)或 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書(shū)中未提供解決方案
  　　
  　　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對(duì)自身迭代連接列值？
  　　
  　　select * from sqlcookbook.dbo.view_613 where ISNUMERIC(mixed)=1;sql server將數(shù)據(jù)找出來(lái)？
  　　
  　　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;
  　　一個(gè)未完成的思路
  　　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
  　　
  　　未使用遞歸 無(wú)法保證按照原順序輸出
  　　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;
  　　
  　　書(shū)中的方案不值得仔細(xì)推敲的，如何遍歷行？游標(biāo)？
  　　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
  　　更優(yōu)秀的方案？游標(biāo)？
  　　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;
  　　
  　　原書(shū)方案錯(cuò)誤
  　　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可以實(shí)現(xiàn)求每個(gè)deptno中sal的眾數(shù)
  　　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);
  　　找出每一個(gè)deptno的sal的眾數(shù)?
  　　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;
  　　錯(cuò)誤。聚合函數(shù)不能用在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;失敗，必須使用書(shū)中的方案
  　　
  　　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;
  　　-----------------
  　　提取出每一個(gè)deptno下sal的中位數(shù)
  　　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;
  　　
  　　錯(cuò)誤？
  　　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;
  　　錯(cuò)誤？
  　　
  　　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
  　　只能去掉一個(gè)最高值和一個(gè)最低值
  　　select deptno,(sum(sal)-max(sal)-min(sal))/(count(*)-2) as xavgsal from emp group by deptno;
  　　最高值和最低值可能有重復(fù)出現(xiàn)的
  　　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;錯(cuò)誤結(jié)果
  　　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);
  　　每一個(gè)deptno去掉一個(gè)最大值，去掉一個(gè)最小值后的平均值 使用集合的思想！
  　　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);
  　　
  　　每一個(gè)deptno去掉一個(gè)最大值，去掉一個(gè)最小值后的平均值 使用集合的思想
  　　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書(shū)中無(wú)此解決方案
  　　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書(shū)中無(wú)此解決方案
  　　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;
  　　
  　　解決方案二 遞歸的調(diào)用自身？必須建中間表
  　　
  　　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;
  　　
  　　標(biāo)量子查詢
  　　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子句問(wèn)題
  　　sql server加order by 通不過(guò)？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通不過(guò)
  　　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');
  　　
  　　問(wèn)題一
  　　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;
  　　
  　　問(wèn)題二
  　　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'
  　　);
  　　統(tǒng)計(jì)
  　　select sno from take group by sno having sum(case when cno in ('CS112','CS114') then 1 else 0 end)=2;
  　　
  　　問(wèn)題三
  　　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');
  　　
  　　問(wèn)題四
  　　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
  　　
  　　原始解決方案：集合論
  　　不使用聚集函數(shù)抽取出選課數(shù)量小于2的學(xué)生
  　　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);
  　　
  　　問(wèn)題五
  　　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內(nèi)置函數(shù)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;
  　　
  　　原始解決方案 集合論 沒(méi)有任何的聚合函數(shù) 僅僅通過(guò)集合處理所有問(wèn)題。
  　　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);
  　　
  　　問(wèn)題六
  　　只用集合，不用聚合函數(shù)
  　　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;可能是效率的問(wèn)題
  　　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
•   　　本書(shū)的各個(gè)例子將使用SQL的各種情況都講得很詳細(xì)，并且思路清晰，一次性讀完意義不大，還是比較適合在尋求解決方案的時(shí)候翻一翻。
•   　　朋友很早以前就推薦過(guò)這本，但一直沒(méi)時(shí)間讀，近來(lái)借朋友的讀了讀，感覺(jué)很不錯(cuò)，很開(kāi)闊思路，也了解了一些以前不常用的但很實(shí)用的函數(shù)。美中不滿的就是感覺(jué)翻譯的一般而且有錯(cuò)別字。不過(guò)感覺(jué)還是值得一讀的。
•   　　內(nèi)容比較多，以例子的講解為主，很多解決方法令人耳目一新，但是有些東西感覺(jué)重復(fù)太多，比如說(shuō)字符的處理，講來(lái)講去其實(shí)就是一個(gè)東西，非要把幾個(gè)雷同的例子翻來(lái)覆去地?fù)v弄，讀起來(lái)有點(diǎn)疲勞，另外一些針對(duì)ORACLE的解決方案，在10g中可以換用正則表達(dá)式輕松搞定，此書(shū)更適合遇到問(wèn)題時(shí)查閱。
  　　
  　　最精彩的章節(jié)當(dāng)屬附錄部分，值得推薦。另外翻譯還是有些問(wèn)題，比較生硬。
  　　
  　　適合開(kāi)發(fā)人員吧，DBA就不推薦了。

相關(guān)圖書(shū)

• 莫泊桑中短篇小說(shuō)選
• 青春之門：放浪篇
• 冷酷的心
• 衛(wèi)生的現(xiàn)代性
• 某種意義
• 改良與革命
• 外國(guó)影人錄
• 小咕嚕大鬧香香甜甜山
• 堂吉訶德
• 諦觀有情
• 田村ゆかり寫(xiě)真集 そらいろ。
• フォトダイアリー 蓮佛美沙子 ｗｉｔｈ 映畫(huà)「転校生 さよならあなた」
• 聽(tīng)劍
• 癲狂的藝術(shù)
• 一千零一夜
• 巴列霍詩(shī)選
• 蟻囈
• 讀《金瓶梅》品明朝社會(huì)
• 隨訪連戰(zhàn)的日子
• 優(yōu)游人間
• 千年回望
• 生命之書(shū)
• 活著的殉道者
• 千年文化
• 花事