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
无
评论、评分、阅读与下载
用户评论 (总计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就不推荐了。