转帖地址：http://caihong19870228.blog.163.com/blog/static/123806238201052410394457/
http://blog.sina.com.cn/s/blog_627b25430100np11.html
http://hi.baidu.com/guan2ye/blog/item/040d03d293296e073af3cfb8.html
1，
查询各科成绩在80分以上的学生：表结构如下
name kc_name kc_score张三 语文 80李四 语文 70钟情 语文 75张三 数学 85李四 数学 88钟情 数学 79张三 英语 80李四 英语 55
【select name from stu group by name having min(kc_score)>=80】
【select distinct name from stu where name not in (select distinct name from stu where kc_score<=80)】
2，
riqi riliang riqi riliang leiliang
1 1 1 1 1
2 3 2 3 4
3 5 ====> 3 5 9
4 7 4 7 16
5 9 5 9 25
【select riqi,riliang,(select sum(riliang) from table t2 where t2.riliang<=t1.riliang) leiliang from table t1】
3，
怎么把这样一个表儿 year month amount 1991 1 1.1 1991 2 1.2 1991 3 1.3 1991 4 1.4 1992 1 2.1 1992 2 2.2 1992 3 2.3 1992 4 2.4 查成这样一个结果 year m1 m2 m3 m4 1991 1.1 1.2 1.3 1.4 1992 2.1 2.2 2.3 2.4
【select year, (select amount from aaa m where month=1 and m.year=aaa.year) as m1, (select amount from aaa m where month=2 and m.year=aaa.year) as m2, (select amount from aaa m where month=3 and m.year=aaa.year) as m3, (select amount from aaa m where month=4 and m.year=aaa.year) as m4 from aaa
group by year 】
==============================================================================
查询数据库前十个数据
oracle: select * from (select * from student order by id desc) where rownum <= 10 order by rownum asc mssql: select top 10 * from student order by id desc mysql: select * from student order by id desc limit 0,10
==============================================================================
1）复制表(只复制结构,源表名：a 新表名：b)
select * into b from a where 1<>1
2）拷贝表(拷贝数据,源表名：a 目标表名：b)
insert into b(a, b, c) select d,e,f from b;
3）显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
4）外连接查询(表名1：a(a,b,c) 表名2：b(c,d,f))
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
5）两张关联表，删除主表A中已经在副表B中没有的信息
delete from A where not exists ( select * from B where A.id=B.id )
============================================================================
--1列出emp表中各部门的部门号，最高工资，最低工资select deptno as 部门号,max(sal) as 最高工资,min(sal) as 最低工资 fromemp group by deptno;
--2 列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp wherejob='clerk' group by deptno;
--3 对于emp中最低工资小于2000的部门，列出job为'CLERK'的员工的部门号，最低工资，最高工资select b.deptno as 部门号,max(sal) as 最高工资,min(sal) as 最低工资 from emp as bwhere job='clerk' and (select min(sal)from emp as a where a.deptno=b.deptno)<2000 group byb.deptno;
--4 根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资select ename as 姓名,deptno as 部门号,sal as 工资 from emp order by deptno desc,sal asc;
--5 列出'buddy'所在部门中每个员工的姓名与部门号select b.ename as 姓名,b.deptno as 部门号 from emp as b where b.deptno=(select a.deptno from emp as a where a.ename='buddy');
--6 列出每个员工的姓名，工作，部门号，部门名select ename as 姓名,job as 工作,dept.deptno as 部门号,dept.dname as 部门名 from emp,deptwhere emp.deptno=dept.deptno;
--7列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名select ename as 姓名,job as 工作,dept.deptno as 部门号,dept.dname as 部门名 from emp,deptwhere emp.deptno=dept.deptno and job='clerk';
--8对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）select a.deptno as 部门号,a.ename as 员工,b.ename as 管理者 from emp as a,emp as b where a.mgr is not null and a.mgr=b.ename;
--9 对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作select a.deptno as 部门号,a.dname as 部门名,b.ename as 员工名,b.job as 工作 from dept as a,emp as b where a.deptno=b.deptno and b.job='clerk';
--10 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序select b.deptno as 部门号,b.ename as 姓名,b.sal as 工资 from emp as bwhere b.sal>(select avg(a.sal) from emp as a where a.deptno=b.deptno) order by b.deptno;
--11对于emp，列出各个部门中工资高于本部门平均工资的员工数和部门号，按部门号排序select a.deptno as 部门号,count(a.sal) as 员工数 from emp as awhere a.sal>(select avg(b.sal) from emp as b where a.deptno=b.deptno) group by a.deptno orderby a.deptno;
--12对于emp中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，平均工资，按部门号排序select count(a.empno) as 员工数,a.deptno as 部门号,avg(sal) as 平均工资from emp as a where (select count(c.empno) from emp as c where c.deptno=a.deptno andc.sal>(select avg(sal) from emp as b where c.deptno=b.deptno))>1group by a.deptno order by a.deptno;
--13对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资,(select count(b.ename) from emp as bwhere b.sal<a.sal) as 人数 from emp as awhere (select count(b.ename) from emp as b where b.sal<a.sal)>=5
