SQL 面试题答案

题目1 ：

SELECT dep.d_name,result.cou from depts dep,
(SELECT count(bm.book_id) cou, dm.d_id dId from bookmap bm,depmap dm 
where bm.a_id = dm.a_id group by dm.d_id) result

where dep.d_id = result.dId

 题目2：

    我一开始是这样写的，可惜错了，pact的值不对

SELECT plan.id,plan.plan,plan.model,plan.corp_code,plan.plan_num,plan.prixis,result.pact,result.amount 
FROM wu_plan plan,
     (SELECT plan,pact,MIN(amount) amount FROM wu_bom  group by plan) result
WHERE plan.plan = result.plan and plan.plan_num>plan.prixis

 后来又改成这样，结果是正确的，就是很繁琐

SELECT plan.id,plan.plan,plan.model,plan.corp_code,plan.plan_num,plan.prixis,result.pact,result.amount 
FROM wu_plan plan,
     (SELECT bom.plan,bom.pact ,res.amount from wu_bom bom,(
SELECT plan,MIN(amount) amount FROM wu_bom group by plan) res
WHERE bom.plan = res.plan and bom.amount = res.amount) result
WHERE plan.plan = result.plan and plan.plan_num>plan.prixis

 所以如果你有好的方法的话，欢迎留言给我，谢谢！

 

题目3：

SELECT t.teacher_id,
       SUM(if(t.week_num=1,is_class,0)) as '星期一',
       SUM(if(t.week_num=2,is_class,0)) as '星期二',
       SUM(if(t.week_num=3,is_class,0)) as '星期三'
 FROM t_class t GROUP BY t.teacher_id

 欢迎指正，谢谢！