数据库sql

1、同一张表中，查询出某个字段的sum和后形成虚表，再使用连接查询进行汇总：

下例为：在manhour_summary中，查询出某员工的加班工时总和、请假工时总和、总可用工时（加班工时+请假工时）

select m.name,sum(m.vacation_time) as vacationTime,l.leaveTotaltime,w.workOvertimeHour from manhour_summary m
  left join (select m.name,sum(m.vacation_time) as workOvertimeHour FROM manhour_summary m where vacation_time>=0 group by m.name) as w on m.name = w.name
  left join (select m.name,sum(m.vacation_time) as leaveTotaltime FROM manhour_summary m where vacation_time<0 group by m.name) as l on m.name = l.name
group by m.name

2、union all / union

union all /union  （联合）将两个或者多个结果集合并。在使用时，两个结果集要有相同的列（也可别名相同），并且字段类型需要一致。

union 和union all 的区别：

union会去除结果集中重复的部分，相当于进行一个distinct（去重），并且union 会自带排序功能；

union all 会不管是否重复，都会将结果合并在一起输出，没有排序功能，只是结果集的堆叠输出。

例：

tableA
id　　name　　score
1　　　a　　　　80
2　　   b　　　　79
3　　   c　　  　 68

tableB
id　　name　　score
1　　   d　　　　48
2　　　e　　　　23
3　　　c　　　　86

使用union时，

select id,name from tableA
union
select id,name from tableB
结果如下（去重复且排序）：
id name
1 a
1 d
2 b
2 e
3 c

使用union all 时，

select id,name from tableA
union all
select id,name from tableB
结果如下（不去重复且未排序）：
id name
1 a
2 b
3 c
1 d
2 e
3 c

接上例，在查询出汇总工时，点击明细查询时，查询出所有的加班及请假工时就可用union all 

3、Left join ...group by

之前也一直认为在select语句中使用子查询，感觉会对表进多次扫描，会影响速度。
但是在最近的测试中，经过了多次测试发现当数据量大，并且显示的字段也较多时，
使用子查询的方式在速度上明显要优于使用join后group的方式。

而且group后每多一个字段，耗时就会更长。

1）采用join方式
select d.dname,count(*)
from scott.emp e, scott.dept d
where e.deptno = d.deptno
group by d.dname;

2）采用子查询方式
select (select d.dname from scott.dept d where d.deptno = e.deptno) dname,
       count(*)
from scott.emp e
group by e.deptno;

从上面两个执行计划可以很容易看出来，方式2执行速度更快。
上面的查询中涉及到的两个表数据量很小，上面的查询要求是非常简单的，实际开发中可能会涉及两个或多个数据量很大的表，并且查询显示字段也会很多，
比如实际数据中部门表可能有部门名称，部门编码，部门简称，部门类型，部门所属上级组织等等等等。
这样一来，对于方式二就需要写很多个子查询，比如：
select (select d.dname from scott.dept d where d.deptno = e.deptno) dname,
         (select d.dcode from scott.dept d where d.deptno = e.deptno) dcode,
         (select d.dshortname from scott.dept d where d.deptno = e.deptno) dshortname,
         ……
       count(*)
from scott.emp e
group by e.deptno;
而方法一，可能只需要在group by后面多加几个字段即可，如下：
select d.dname,dcode, dshortname,……
         count(*)
from scott.emp e, scott.dept d
where e.deptno = d.deptno
group by d.dname,dcode, dshortname,……;
相对于sql长度来说，可能方式一更简洁。
所以实际开发中，我们大都会选择方式一（即用join连接)。我之前也一直认为在select语句中使用子查询，感觉会对表进多次扫描，会影响速度。
但是在最近的测试中，经过了多次测试发现当数据量大，并且显示的字段也较多时，
使用子查询的方式在速度上明显要优于使用join后group的方式。

而且group后每多一个字段，耗时就会更长。

有以下两点建议：
一：对于可以不用left join的我们尽量不用，这样会使group by后的字段达到最小。
二：有些时候必须用left join的，比如要对表进行过滤时，可能要对表先进行关联过滤，再group by
这种情况下，不一定就是group by后的字段越少越好了。而是要对关联字段全部group by。
我做过的一次实验就是对3个表进行关联，比如A表有b_id是B表的主键，有c_id是C表的主键，
当我使用group by a.b_id,a.c_id时，发现执行计划先对a,b表进行hash join，然后group by，再和c表进行hash join，然后又一次group by；
当我使用group by a.b_id,a.c_id,b.b_id,c.c_id时 发现执行计划是对a,b表进行hash join，再和c表进行hash join，最后一起group by；
比较一下发现后一种方式更快。

由此可见，group by是一种很耗时的操作。我们在实际开发中应该尽量斟酌使用。

转自http://www.itpub.net/thread-1895545-1-1.html

4、IFNULL(expr1,expr2)

如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。

IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。

SELECT a.name,a.employee_no as employeeNo,ifnull(t2.leaveTotaltime,0) as leaveTotaltime,ifnull(t1.workOvertimeHour,0) as workOvertimeHour,(ifnull(t1.workOvertimeHour,0)+ifnull(t2.leaveTotaltime,0)) as vacationTime,t3.graduationTime,t3.entryDate,t3.isLaborContract
         FROM staff_info a
         left join (select sum(b.work_overtime_hour) as workOvertimeHour,b.name from work_overtime_hour b group by b.name) t1 on a.name=t1.name 
         left join (select sum(-c.leave_totaltime) as leaveTotaltime ,c.name from leave_detail c  group by c.name) t2 on a.name=t2.name
         left join (select s.name,employee_no as employeeNo,graduation_time as graduationTime,entry_date as entryDate,is_labor_contract as isLaborContract from staff_info s left join leave_detail l on s.name = l.name and s.employee_no = l.job_number group by name) t3 on a.name = t3.name