面试之sql,大家进来看看怎么做？

Question

凭证明细表字段（主键为：日期、凭证字、凭证号、序号）如下：
日期      | 凭证字 | 凭证号 | 序号 | 借贷方向 | 发生金额 |
2006-9-13 | 转     | 1      | 1    | 借      | 100      |
2006-9-13 | 转     | 1      | 2    | 借      | 100      |
2006-9-13 | 转     | 1      | 3    | 贷      | 200      |
2006-9-14 | 付     | 1      | 1    | 借      | 300      |
2006-9-14 | 付     | 1      | 2    | 贷      | 300      |凭证表字段（主键为：日期、凭证字、凭证号）如下：
日期      |凭证字|凭证号|借金额|贷金额|
2006-9-13 |转    | 1   | ？   | ？   |
2006-9-14 |付    | 1   | ？   | ？   |另外，也可以看到，凭证明细表的（日期、凭证字、凭证号）是凭证表的外键。
现在需要使用SQL的UPDATE方法，来更新凭证表的【借金额】和【贷金额】值。
其值等于对就明细表的所有同借贷方向的记录的发生金额的合计。比如上表明细，更新完的结果如下;
日期      |凭证字|凭证号|借金额|贷金额|
2006-9-13 |转    | 1   | 200  | 200  |
2006-9-14 |付    | 1   | 200  | 300  |
请写出SQL语句以上是我在面试中遇到真实试题，大家出出主意吧！！

Answer 1

还有一题：
数据库科目表如下结构，科目号具有匹配关系，其中白色背景为记录已知值，红色字段值为空，需要你使用一系列SQL语句（注意，不是1句，没有人可以一句搞定吧？！）来恢复其中的值。
科目号   | 科目名称 | 上级科目号 | 级别 | 是否叶子|
1001     | 现金     |           | 1    | 1       |
1002     | 银行存款 |           | 1    | 0       |
100201   | 工行存款 | 1002      | 2    | 0       |
10020101 |支行1存款 | 100201    | 3    | 1       |
10020102 |支行2存款 | 100201    | 3    | 1       |
100202   |建行存款  | 1002      | 2    | 1       |
1009     |外币存款  |           | 1    | 1       |

Answer 2

好像是这样，用中文做表名字段名真爽啊：
mysql> select * from `凭证表`;
+-----------+--------+--------+--------+--------+
| 日期      | 凭证字 | 凭证号 | 借金额 | 贷金额 |
+-----------+--------+--------+--------+--------+
| 2006-9-13 | 转     | 1      | ？     | ？     |
| 2006-9-14 | 付     | 1      | ？     | ？     |
+-----------+--------+--------+--------+--------+
2 rows in set (0.00 sec)mysql> select * from `凭证明细表`;
+------------+--------+--------+------+----------+----------+
| 日期
日期 | 凭证字 | 凭证号 | 序号 | 借贷方向 | 发生金额 |
+------------+--------+--------+------+----------+----------+
| 2006-9-13  | 转     | 1      | 1    | 借       | 100      |
| 2006-9-13  | 转     | 1      | 2    | 借       | 100      |
| 2006-9-13  | 转     | 1      | 3    | 贷       | 200      |
| 2006-9-14  | 付     | 1      | 1    | 借       | 300      |
| 2006-9-14  | 付     | 1      | 2    | 贷       | 300      |
+------------+--------+--------+------+----------+----------+
5 rows in set (0.00 sec)mysql> update `凭证表` as a
    -> set `借金额` = (select sum(`发生金额`) from `凭证明细表` as b where a.`日
期` = b.`日期` and `借贷方向` = '借'),
    ->     `贷金额` = (select sum(`发生金额`) from `凭证明细表` as b where a.`日
期` = b.`日期` and `借贷方向` = '贷');
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0mysql> select * from `凭证表`;
+-----------+--------+--------+--------+--------+          ;
| 日期      | 凭证字 | 凭证号 | 借金额 | 贷金额 |
+-----------+--------+--------+--------+--------+
| 2006-9-13 | 转     | 1      | 200    | 200    |
| 2006-9-14 | 付     | 1      | 300    | 300    |
+-----------+--------+--------+--------+--------+
2 rows in set (0.00 sec)

Answer 3

update 凭证表 a inner join ( 
select 日期,凭证字,凭证号,sum(if(借贷方向='借',发生金额,0)) as 借金额,sum(if(借贷方向='贷',发生金额,0)) as  贷金额
from 凭证明细表
group by 日期,凭证字,凭证号
) b on a.日期=b.日期 and a.凭证字=b.凭证字 and a.凭证号=b.凭证号
set a.借金额=b.借金额,a.贷金额=b.贷金额

Answer 4

请做第二题：数据库科目表如下结构，科目号具有匹配关系，其中白色背景为记录已知值，红色字段值为空，需要你使用一系列SQL语句（注意，不是1句，没有人可以一句搞定吧？！）来恢复其中的值。科目号    | 科目名称 | 上级科目号 | 级别 | 是否叶子|
1001     | 现金     |           | 1   | 1 |
1002     | 银行存款 |           | 1   | 0 |
100201   | 工行存款 | 1002      | 2   | 0 |
10020101 |支行1存款 | 100201    | 3   | 1 |
10020102 |支行2存款 | 100201    | 3   | 1 |
100202   |建行存款  | 1002      | 2   | 1 |
1009     |外币存款  |           | 1   | 1 |

Answer 5

说真心话，我其实不喜欢中文表名和字段名，哈哈。
第二题真做起来发现没有想象中的难，如果使用临时表的话会简单一些，这里考虑不用临时表的做法，另外这个只是 select 语句，如果 lz 有需要 update 原表的话，可以参考改为 update 语句。
mysql> select * from tree;
+----------+-----------+--------+-------+------+
| id       | name      | parent | level | leaf |
+----------+-----------+--------+-------+------+
|     1001 | 现金      |   NULL |  NULL | NULL |
|     1002 | 银行存款  |   NULL |  NULL | NULL |
|     1009 | 外币存款  |   NULL |  NULL | NULL |
|   100201 | 工行存款  |   NULL |  NULL | NULL |
|   100202 | 建行存款  |   NULL |  NULL | NULL |
| 10020101 | 支行1存款 |   NULL |  NULL | NULL |
| 10020102 | 支行2存款 |   NULL |  NULL | NULL |
+----------+-----------+--------+-------+------+
7 rows in set (0.00 sec)mysql> set @total_parent := "";
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> select id, name, if(new_parent = 0, null, new_parent) as parent, sum(if(n
ew_parent <> 0, 1, 0)) + 1 as level, if(locate(id, max(total_parent)), 0, 1) as
leaf
    -> from
    -> (
    ->     select t1.id, t1.name, @parent := (if(t1.id rlike concat("^", t2.id),
 t2.id, 0)) as new_parent, @total_parent := if(@parent <> 0, concat(@total_paren
t, ",", @parent), @total_parent)  as total_parent
    ->     from
    ->          tree as t1 join tree as t2
    ->          on t1.id <> t2.id
    ->          order by length(new_parent) desc
    ->     ) as t
    -> group by id;
+----------+-----------+--------+-------+------+
| id       | name      | parent | level | leaf |
+----------+-----------+--------+-------+------+
|     1001 | 现金      |   NULL |     1 |    1 |
|     1002 | 银行存款  |   NULL |     1 |    0 |
|     1009 | 外币存款  |   NULL |     1 |    1 |
|   100201 | 工行存款  |   1002 |     2 |    0 |
|   100202 | 建行存款  |   1002 |     2 |    1 |
| 10020101 | 支行1存款 | 100201 |     3 |    1 |
| 10020102 | 支行2存款 | 100201 |     3 |    1 |
+----------+-----------+--------+-------+------+
7 rows in set (0.03 sec)

Answer 6

好吧，满足一下中文控的中文标题欲望：
mysql> select id as '科目号', name as '科目名称', if(new_parent = 0, null, new_p
arent) as '上级科目号', sum(if(new_parent <> 0, 1, 0)) + 1 as '级别', if(locate(
id, max(total_parent)), 0, 1) as '是否叶子'
    -> from
    -> (
    ->     select t1.id, t1.name, @parent := (if(t1.id rlike concat("^", t2.id),
 t2.id, 0)) as new_parent, @total_parent := if(@parent <> 0, concat(@total_paren
t, ",", @parent), @total_parent)  as total_parent
    ->     from
    ->          tree as t1 join tree as t2
    ->          on t1.id <> t2.id
    ->          order by length(new_parent) desc
    ->     ) as t
    -> group by id;
+----------+-----------+------------+------+----------+
| 科目号   | 科目名称  | 上级科目号 | 级别 | 是否叶子 |
+----------+-----------+------------+------+----------+
|     1001 | 现金      |       NULL |    1 |        1 |
|     1002 | 银行存款  |       NULL |    1 |        0 |
|     1009 | 外币存款  |       NULL |    1 |        1 |
|   100201 | 工行存款  |       1002 |    2 |        0 |
|   100202 | 建行存款  |       1002 |    2 |        1 |
| 10020101 | 支行1存款 |     100201 |    3 |        1 |
| 10020102 | 支行2存款 |     100201 |    3 |        1 |
+----------+-----------+------------+------+----------+
7 rows in set (0.02 sec)

Answer 7

发觉脑子愈发不好使了。
mysql> select * from C;
+----------+---------------+--------+-------+------+
| id       | name          | parent | level | leaf |
+----------+---------------+--------+-------+------+
|     1001 | 现金        |   NULL |  NULL | NULL |
|     1002 | 银行存款  |   NULL |  NULL | NULL |
|     1009 | 外币存款  |   NULL |  NULL | NULL |
|   100201 | 工行存款  |   NULL |  NULL | NULL |
|   100202 | 建行存款  |   NULL |  NULL | NULL |
| 10020101 | 支行1存款 |   NULL |  NULL | NULL |
| 10020102 | 支行2存款 |   NULL |  NULL | NULL |
+----------+---------------+--------+-------+------+
7 rows in set (0.00 sec)mysql> update C a set a.level=floor((length(a.id) - 4)/2 + 1),a.parent=left(a.id,if(length(a.id)-2 = 2,0,length(a.id)-2 ));
Query OK, 7 rows affected, 3 warnings (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0mysql> update C set leaf=1;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0mysql> update C b inner join (select a.* from C a inner join C b on b.id=concat(a.id,"01")) T on b.id=T.id set b.leaf=0 where 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0mysql> select * from C;
+----------+---------------+--------+-------+------+
| id       | name          | parent | level | leaf |
+----------+---------------+--------+-------+------+
|     1001 | 现金        |      0 |     1 |    1 |
|     1002 | 银行存款  |      0 |     1 |    0 |
|     1009 | 外币存款  |      0 |     1 |    1 |
|   100201 | 工行存款  |   1002 |     2 |    0 |
|   100202 | 建行存款  |   1002 |     2 |    1 |
| 10020101 | 支行1存款 | 100201 |     3 |    1 |
| 10020102 | 支行2存款 | 100201 |     3 |    1 |
+----------+---------------+--------+-------+------+
7 rows in set (0.00 sec)