勘误sqlserver mysql

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
where s.sno=t.sno;
select distinct sno,sname,age from(
select s.*,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;
问题七
使用集合论解决所有问题 不使用聚合函数
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.* from student s,take a,take b where s.sno=a.sno and a.sno=b.sno and a.cno='CS112' and b.cno='CS114';
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='CS112' and b.cno='CS114');
mysql
select s.* from student s,take t where s.sno=t.sno group by s.sno having sum(case when t.cno in('CS112','CS114') then 1 else 0 end)=2;
select s.*,(select sum(case when t.cno in('CS112','CS114') then 1 else 0 end) from take t where t.sno=s.sno) as sm from student s;
select sno,sname,age from(select s.*,(select sum(case when t.cno in('CS112','CS114') then 1 else 0 end) from take t where t.sno=s.sno) as sm from student s)x where sm=2;
sql server
select s.*,sum(case when t.cno in('CS112','CS114') then 1 else 0 end) over(partition by s.sno) as sm
from rozen.dbo.student s,rozen.dbo.take t where 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 sm
from rozen.dbo.student s,rozen.dbo.take t where s.sno=t.sno)x where sm=2;
ANOTHER
mysql
select s.*,min(t.cno) as mincno,max(t.cno) as maxcno from student s,take t where s.sno=t.sno group by s.sno;
select s.* from student s,take t where s.sno=t.sno and t.cno in('CS112','CS114') group by s.sno having max(t.cno)!=min(t.cno);
sql server
select s.*,
max(t.cno) over(partition by s.sno) as maxcno,
min(t.cno) over(partition by s.sno) as mincno
from rozen.dbo.student s,rozen.dbo.take t
where s.sno=t.sno and t.cno in ('CS112','CS114');
select distinct sno,sname,age from(
select s.*,
max(t.cno) over(partition by s.sno) as maxcno,
min(t.cno) over(partition by s.sno) as mincno
from rozen.dbo.student s,rozen.dbo.take t
where s.sno=t.sno and t.cno in ('CS112','CS114')
)x where maxcno!=mincno;
问题九
集合论
select a.*,b.* from teach a left join teach b on a.lname=b.lname and a.cno!=b.cno;
select p.* from teach a left join teach b on a.lname=b.lname and a.cno!=b.cno inner join professor p on a.lname=p.lname where b.lname is null;
ANOTHER集合论
select p.* from professor p where p.lname not in
(select a.lname from teach a,teach b where a.lname=b.lname and a.cno>b.cno);错误
select p.* from professor p,teach t where p.lname=t.lname and p.lname not in
(select a.lname from teach a,teach b where a.lname=b.lname and a.cno>b.cno);
mysql
select p.*,t.* from professor p,teach t where p.lname=t.lname;
select p.* from professor p,teach t where p.lname=t.lname
group by p.lname having count(*)=1;
select p.* from professor p where p.lname in (select lname from teach group by lname having count(*)=1);
select p.*,count(*) as cnt from professor p,teach t where p.lname=t.lname group by p.lname;
sql server
select p.*,count(*) over(partition by t.lname) from professor p,teach t where p.lname=t.lname;
问题十
集合论
temp
select a.*,b.* from take a join take b on a.sno=b.sno order by a.sno,a.cno,b.cno;
temp
select a.*,b.* from take a join take b on a.sno=b.sno and a.cno<b.cno order by a.sno,a.cno,b.cno;
select a.*,b.*,c.* from take a join take b on a.sno=b.sno and a.cno<b.cno
left join take c on a.sno=c.sno and a.cno!=c.cno and b.cno!=c.cno;
final
select a.*,b.* from take a join take b on a.sno=b.sno and a.cno='CS112' and b.cno='CS114' order by a.sno,a.cno,b.cno;
select a.*,b.*,c.* from take a join take b on a.sno=b.sno and a.cno='CS112' and b.cno='CS114' left join take c on a.sno=c.sno and a.cno!=c.cno and b.cno!=c.cno;
select a.*,b.*,c.* from take a join take b on a.sno=b.sno and a.cno='CS112' and b.cno='CS114' left join take c on a.sno=c.sno and a.cno!=c.cno and b.cno!=c.cno where c.sno is null;
select s.* from student s where s.sno in(
select a.sno from take a join take b on a.sno=b.sno and a.cno='CS112' and b.cno='CS114' left join take c on a.sno=c.sno and a.cno!=c.cno and b.cno!=c.cno where c.sno is null);
select s.* from take a join take b on a.sno=b.sno and a.cno='CS112' and b.cno='CS114' left join take c on a.sno=c.sno and a.cno!=c.cno and b.cno!=c.cno join student s on a.sno=s.sno where c.sno is null;

ANOTER集合论 全部使用自然连接
错误结果 不可以联着写 为什么？
select s.* from student s,take a,take b
where s.sno=a.sno=b.sno and a.cno='CS112' and b.cno='CS114' and s.sno not in
(select a.sno from take a,take b,take c where a.sno=b.sno=c.sno and a.cno>b.cno>c.cno);
正确结果
select s.* from student s,take a,take b
where s.sno=a.sno and s.sno=b.sno and a.cno='CS112' and b.cno='CS114' and s.sno not in
(select a.sno from take a,take b,take c where a.sno=b.sno and a.sno=c.sno and a.cno>b.cno and b.cno>c.cno);

mysql
select s.* from student s,take t where s.sno=t.sno group by s.sno
having sum(case when t.cno in('CS112','CS114') then 1 else 0 end)=2 and count(*)=2;
sql server
select s.*,
SUM(case when t.cno in('CS112','CS114') then 1 else 0 end) over(partition by s.sno) as sm,
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.*,
SUM(case when t.cno in('CS112','CS114') then 1 else 0 end) over(partition by s.sno) as sm,
COUNT(*) over(partition by s.sno) as cnt
from rozen.dbo.student s,rozen.dbo.take t where s.sno=t.sno)x where sm=2 and cnt=2;

ANOTHER SQLSERVER 书中的方案 使用row_number()从而避免使用distinct 为什么？distinct有什么问题么？
select s.sno,s.sname,s.age,
count(*) over(partition by s.sno) as cnt,
sum(case when t.cno in('CS112','CS114') then 1 else 0 end) over(partition by s.sno) as both,
row_number() over(partition by s.sno order by s.sno) as rn
from rozen.dbo.student s,rozen.dbo.take t
where s.sno=t.sno;

select sno,sname,age from(
select s.sno,s.sname,s.age,
count(*) over(partition by s.sno) as cnt,
sum(case when t.cno in('CS112','CS114') then 1 else 0 end) over(partition by s.sno) as both,
row_number() over(partition by s.sno order by s.sno) as rn
from rozen.dbo.student s,rozen.dbo.take t
where s.sno=t.sno) x
where cnt=2 and both=2 and rn=1;

问题十一
mysql
select a.* from student a,student b where a.age>b.age group by a.sno having count(*)=2;
select a.* from student a where (select count(*) from student b where b.age<a.age)=2;
sql server
select s.*,DENSE_RANK() over(order by age) as rnk from rozen.dbo.student s;
select sno,sname,age from(
select s.*,DENSE_RANK() over(order by age) as rnk from rozen.dbo.student s) x where rnk=3;
集合论
select a.* from student a,student b,student c where a.age>b.age and b.age>c.age
and a.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);

问题十二
mysql
select s.* from student s,take t where s.sno=t.sno group by s.sno
having count(s.sno)=(select count(*) from courses);
sql server语法比较严格
select s.* from rozen.dbo.student s,rozen.dbo.take t where s.sno=t.sno
group by s.sno,s.sname,s.age having COUNT(s.sno)=(select COUNT(*) from rozen.dbo.courses);
Distinct语法错误！
select s.sno,s.sname,s.age,
count(distinct t.cno) over(partition by s.sno) as cnt,
count(distinct c.title) over() as total,
row_number() over(partition by s.sno order by c.cno) as rn
from rozen.dbo.courses c
left join rozen.dbo.take t on(c.cno=t.cno)
left join rozen.dbo.student s on(t.sno=s.sno);
原始解决方案 集合论 非常精辟
select * from student where sno not in
(select s.sno from student s,courses c
where (s.sno,c.cno) not in (select sno,cno from take));
问题十三
mysql
select s.* from student s where s.age=(select max(age) from student);
sqlserver
select s.*,max(s.age) over() as oldest from rozen.dbo.student s;
select sno,sname,age
from (select s.*,max(s.age) over() as oldest from rozen.dbo.student s)x
where age=oldest;
原始方案 数据集
select a.*,b.* from student a,student b where a.age<b.age order by a.age,b.age;
select s.* from student s where s.sno not in(select a.sno from student a,student b where a.age<b.age);