SQL外部链接查询求解

来源：百度知道 编辑：UC知道 时间：2024/06/15 14:18:10
一个腐朽的数据库，常常出错，厂里又不出钱换，请求这个查询找错

表inv_stock_detail是库存总账，其中mtd_rece_qty和mtd_rece_amt是当月入库数量和总金额，item_code是库品代码，inv_trans_detail是进出库流水账表，qty是流水数量，amt是流水金额，流水可能对同一个库品有多进出库记录。inv_trans_detail.trans_type=$pur是入库。inv_trans_detail.warehouse=2是代表2号库。

查询语句：
select a.item_code,b.mtd_rece_qty,b.mtd_rece_amt,sum(a.qty), sum(a.amt) from inv_stock_detail b
right outer join inv_trans_detail a
on item_code=(SELECT item_code FROM inv_trans_detail
WHERE a.warehouse='2' and trans_type='$pur' group by item_code)

现在目的是以流水表为准列出库存总账表中与之对应的所有库品，为了查找没有的或者账目出错的。以上语句提示列名：item_code不明确。。。。我无解了。求大师解。。。。

item_code不明确 意思就是数据库没看明白你写得是哪个表的这个item_code

改一下就好了

select a.item_code,b.mtd_rece_qty,b.mtd_rece_amt,sum(a.qty), sum(a.amt) from inv_stock_detail b
right outer join inv_trans_detail a
on a.item_code=(SELECT item_code FROM inv_trans_detail
WHERE a.warehouse='2' and trans_type='$pur' group by item_code)

select a.item_code,b.mtd_rece_qty,b.mtd_rece_amt,sum(a.qty), sum(a.amt) from inv_stock_detail b
right outer join
(SELECT item_code FROM inv_trans_detail
WHERE a.warehouse='2' and trans_type='$pur' group by item_code) as a
on item_code=a.item_code

把外面条件的查询 作为表表达式看看

select a.item_code,b.mtd_rece_qty,b.mtd_rece_amt,sum(a.qty), sum(a.amt) from inv_stock_detail b
right outer join inv_trans_detail a
on item_code=(SELECT a.item_code FROM inv_trans_detail a
WHERE a.warehouse='2' and trans_type='$pur' group by item_code)