求SQL

Question

说明: 1.单张订购单可以多次验收 
2.每一个Table都有一个id来对应(每个Table第一个栏位), 
3.主档与明细档是1对多的关联 
4.以下的SQL Statement不可用cursor,要一个SELECT完成(可以多层Select) 
              主要Table如下: 
    供应商(q_venderm)  = sup_id + sup_nm 
品名(q_prod) = prod_id + prod_nm(品名) + unit(单位) 
厂别(ac_factm)= fact_id + fact_no(厂别编号) + fact_nm 
订购单主档(q_orderm) = ordm_id + ord_no(订购单号) + vou_date(订购日) + fact_id + sup_id 
订购单明细档(q_orderd) = ordd_id + ordm_id + prod_id + qty(数量) + price (单价) 
验收单主档(q_verifym) = verifym_id + verify_no(验收单号) + vou_date(验收日) + fact_id + sup_id 
验收单明细档(q_verifyd) = verifyd_id + verifym_id + ordd_id + prod_id + qty (验收量)     请问: 
1. 找出验收次数超过3次的订购单号(ord_no)? 2. 找出订购单B200210012 最大总额与最小总额之间的差异数? 3. 找出订购单总和 > 全部订购单总和的平均值的订购单号 4. 找出尚未验收完的订购单中货品总额的总和? 5. 找出至目前为止差异1个月(30天计) 尚未验收h的订购单 

Answer 1

作业题
1、 select a.购单号 from (select count(购单号) as cnt,购单号 from q_orderm) a where a.cnt>3

Answer 2

--由于不了解各表之间的关系,下面的sql都是凭猜测写的,有很多错误之处请指教 
1. 找出验收次数超过3次的订购单号(ord_no)? 
select a.ord_no,count(*) 
from q_orderm a 
    inner join q_verifym b on a.ordm_id = b.verifym_id 
group by a.ord_no 
having count(*) > 3 2. 找出订购单B200210012 最大总额与最小总额之间的差异数? 
select max(a.qty * a.price) - min(a.qty * a.price) 
from q_orderd a 
    inner join q_orderm b on a.ordm_id = b.ordm_id 
where b.ord_no = 'B200210012' 3. 找出订购单总和 > 全部订购单总和的平均值的订购单号 
select 
from q_orderm a inner join 
(select ordm_id,sum( qty * price) as 'sum',sum(sum(qty * price))/count(distinct ordm_id) as 'avg' 
from q_orderd 
group by ordm_id) b on a.ordm_id = b.ordm_id 
where b.sum > b.avg 4. 找出尚未验收完的订购单中货品总额的总和? 
select sum(sum) 
from 
( 
select c.ordm_id,sum(qty * price) as 'sum' 
from q_orderd c inner join 
(select a.ordm_id 
from q_orderm a 
where not exist (select 1 from q_verifym b where b.verifym_id = a.ordm_id)) d on c.ordm_id = d.ordm_id 
) e 5. 找出至目前为止差异1个月(30天计) 尚未验收h的订购单 
select a.ord_no 
from q_orderm a 
where datediff(dd,a.vou_date,getdate()) = 30 
and not exist (select 1 from q_verifym b where b.verifym_id = a.ordm_id)

Answer 3

ＴＯ sweetweiwei 错误
ｔｋｓ

Answer 4

找出验收次数超过3次的订购单号(ord_no)?
select ord_no  from q_orderd a , q_verifyd b,q_orderm c 
where a.ordd_id=b.ordd_id and a.ordm_id=c.ordm_id 
group by ord_no 
having count(*)>3 ;找出订购单B200210012 最大总额与最小总额之间的差异数?
          select max(a.qty * a.price) - min(a.qty * a.price)  tfrom q_orderd a ,q_orderm bwhere b.ord_no = 'B200210012' and a.ordm_id=b.ordm_id ;找出订购单总和 > 全部订购单总和的平均值的订购单号
  select  b.ord_no  
from q_orderm b,(SELECT ordm_id,sum(price)*sum(qty) FROM q_orderdGROUP BY ordm_idHAVING sum(price)*sum(qty)>(SELECT AVG(price)*AVG(qty) FROM q_orderd) )awhere b.ordm_id=a.ordm_id ;找出尚未验收完的订购单中货品总额的总和?
select sum(d.qty * d.price)from q_orderm e,q_orderd dwhere  e.ordm_id=d.ordm_id and e.ord_no not in(select a.ord_no from q_orderm a,q_orderd b,q_verifyd cwhere a.ordm_id=b.ordm_id and b.ordd_id=c.ordd_id) ;找出至目前为止差异1个月(30天计) 尚未验收h的订购单
select d.ord_no from q_orderm dwhere d.ord_no not in(select a.ord_no from q_orderm a,q_orderd b,q_verifyd cwhere a.ordm_id=b.ordm_id and b.ordd_id=c.ordd_id) and 
sysdate-d.vou_date>30 ;