请教left outer join 写法

[marklr]

ruhuo 表结构 （入货表）
id theTypeName operation supplyPermoney goodsNum
1 汽水 入货 1.5 100
.....................

typeTable 表结构 （名称表）
id theTypeName
1 汽水
2 水杯

我想统计名称表里面所有品种各自的进货数量并且取最近一次进货价格作为单价，我如下写法：


SELECT t.theTypeName, a.calNum,b.supplyPermoney
FROM typetable t LEFT OUTER JOIN
(SELECT theTypeName, SUM(goodsnum) AS calNum
FROM ruhuo
WHERE operation = '入货' AND supplyPermoney > 0
GROUP BY theTypeName) a ON t.theTypeName = a.theTypeName
left outer join (select top 1 theTypeName,supplyPermoney from ruhuo where theTypeName=t.theTypeName and operation = '入货' AND supplyPermoney > 0 order by id desc) b on b.theTypeName=t.theTypeName

提示列前缀 ‘t’与查询中所有的表名或者名称不匹配

[黄_瓜]

--try
SELECT t.theTypeName, a.calNum,b.supplyPermoney
FROM typetable t LEFT OUTER JOIN
(SELECT theTypeName, SUM(goodsnum) AS calNum
FROM ruhuo
WHERE operation = '入货' AND supplyPermoney > 0
GROUP BY theTypeName) a ON t.theTypeName = a.theTypeName
left outer join (select top 1 theTypeName,supplyPermoney from ruhuo,typetable t where theTypeName=t.theTypeName and operation = '入货' AND supplyPermoney > 0 order by id desc) b on b.theTypeName=t.theTypeName

[SQL77]

SELECT t.theTypeName, a.calNum,b.supplyPermoney 
FROM typetable t LEFT OUTER JOIN 

          (SELECT theTypeName, SUM(goodsnum) AS calNum 
        FROM ruhuo 
        WHERE operation = '入货' AND supplyPermoney > 0 
        GROUP BY theTypeName) a 

ON t.theTypeName = a.theTypeName 

left outer join 

(select top 1 R.theTypeName,R.supplyPermoney from ruhuo R,typetable  T1 where R.theTypeName=t1.theTypeName and  R.operation = '入货' AND R.supplyPermoney > 0 order by R.id desc) b on 

b.theTypeName=t.theTypeName 

[老黎]

SELECT t.theTypeName, a.calNum,b.supplyPermoney
FROM typetable t
LEFT OUTER JOIN
(SELECT theTypeName, SUM(goodsnum) AS calNum
FROM ruhuo
WHERE operation = '入货' AND supplyPermoney > 0
GROUP BY theTypeName) a ON t.theTypeName = a.theTypeName
left outer join
(select top 1 theTypeName,supplyPermoney
from ruhuo
where theTypeName=t.theTypeName and operation = '入货'
AND supplyPermoney > 0 order by id desc) b on b.theTypeName=t.theTypeName

[老黎]

红色那里的t

引用 3 楼 kerafan 的回复:

SELECT t.theTypeName, a.calNum,b.supplyPermoney
FROM typetable t
    LEFT OUTER JOIN
        (SELECT theTypeName, SUM(goodsnum) AS calNum
        FROM ruhuo
        WHERE operation = '入货' AND supplyPermoney > 0
        GROUP BY theTypeName) a ON t.theTypeName = a.theTypeName
    left outer join
        (select top 1 theTypeName,supplyPermoney
        from ruhuo
        where theTypeName=t.theTypeName and  operation = '入货'
        AND supplyPermoney > 0 order by id desc) b on b.theTypeName=t.theTypeName

[marklr]

to ： Beirut 你的方法提示 theTypeName 不明确

to： SQL77 ，按你的方法可以运行，但是出来的结果 supplyPermoney都是 null，只有一条记录supplyPermoney有数字显示，但在几十万条进出数据里面不可能是这样的结果，请问是不是哪里有问题？

[fskjb01]

我想知道你怎么区分最近一次进货价格，好像没有进货时间列吧？？

[fskjb01]

哦，看清楚了通过 order by id desc。。。。。