一个SQL语句问题,在线请求帮助?急!!!

lp370480 2008-07-03 02:09:20
我是意图是想:
我在VB中用到一个查询呆滞料的问题
我一个出库表(chuku),一个入库表(ruku)里面分别有“料号”,“出|入库数量”“出|入库时间”
我要按料号查询:某一料号入库总和减去该料号出库总和大于10并且该料号的最后入库日期减去最后出库日期大于100天的记录
我当时是这样实现的:先找出每一个料号的入库减出库所得的“库存”
然后找出最后的入库时间和最后的出库时间
然后用IF做一个判断,当“库存”大于10的时候根据其料号去查找该料号并且该料号的最后入库日期减去最后出库日期大于100天的记录
我编写了一个SQL语句:
SELECT A.料号 as 料号,(B.出库总量-A.入库总量) as 库存 from (select 料号,max(出库时间) as 最晚出库日期,sum(出库数量) as 出库总量 From chuku GROUP BY 料号) as A inner join (select 料号,max(入库时间) as 最晚入库日期,sum(入库数量) as 入库总量 from ruku group by 料号) as B on A.料号 = B.料号 having (B.入库总量-A.出库总量)>10 and datediff(d,B.最晚入库日期,A.最晚出库日期)>100
但我用查询分析器总是报错:“列名 '出库总量' 无效。”;“列名 '入库总量' 无效。”我把(B.入库总量-A.出库总量)>10 换成(B.sum(入库数量)-A.sum(出库数量))>10也是一样。
但我用这条SQL语句就是正确的:SELECT (B.入库总量-A.出库总量) as 库存 from (select 料号,sum(出库数量) as 出库总量 From chuku GROUP BY 料号) as A inner join (select 料号,sum(入库数量) as 入库总量 from ruku group by 料号) as B on A.料号 = B.料号

望高手能帮我解决一下,也可以根据我的需求重写一套VB+SQL代码帮我实现上述要求,感恩载德!!!
...全文
134 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
jone_h 2008-07-03

select
MaterialID=isnull(a.MaterialID,b.MaterialID),a.total_A_Num - b.total_B_Num as remainNum
from
(select MaterialID,sum(num) total_A_Num from ruku group by MaterialID)a
full join
(select MaterialID,sum(num) total_B_Num from chuku group by MaterialID)b on a.MaterialID=b.MaterialID

where (a.total_A_Num - b.total_B_Num) > 10 and a.MaterialID in
(
select MaterialID from (
select r.MaterialID, datediff(d,Max(c.chukuDate),Max(r.rukuDate)) as Days from ruku as r join chuku c
on r.MaterialID=c.MaterialID
group by r.MaterialID) a where Days>100
)
回复
fgh63 2008-07-03
select
MaterialID=isnull(a.MaterialID,b.MaterialID),a.total_A_Num - b.total_B_Num as remainNum
from
(select MaterialID,sum(num) total_A_Num from ruku group by MaterialID)a
full join
(select MaterialID,sum(num) total_B_Num from chuku group by MaterialID)b on a.MaterialID=b.MaterialID

where (a.total_A_Num - b.total_B_Num) > 10 and a.MaterialID in
(
select MaterialID from (
select r.MaterialID, datediff(d,Max(c.chukuDate),Max(r.rukuDate)) as Days from ruku as r join chuku c
on r.MaterialID=c.MaterialID
group by r.MaterialID) a where Days>100
)
回复
fgh63 2008-07-03
select
MaterialID=isnull(a.MaterialID,b.MaterialID),a.total_A_Num - b.total_B_Num as remainNum
from
(select MaterialID,sum(num) total_A_Num from ruku group by MaterialID)a
full join
(select MaterialID,sum(num) total_B_Num from chuku group by MaterialID)b on a.MaterialID=b.MaterialID

where (a.total_A_Num - b.total_B_Num) > 10 and a.MaterialID in
(
select MaterialID from (
select r.MaterialID, datediff(d,Max(c.chukuDate),Max(r.rukuDate)) as Days from ruku as r join chuku c
on r.MaterialID=c.MaterialID
group by r.MaterialID) a where Days>100
)
回复
linguojin11 2008-07-03
[Quote=引用 3 楼 exinke 的回复:]
having 后面要跟着sum() avg()这样的函数吧 看你的应该用where
[/Quote]
回复
exinke 2008-07-03
having 后面要跟着sum() avg()这样的函数吧 看你的应该用where
回复
lgxyz 2008-07-03
on A.料号 = B.料号 having (B.入库总量-A.出库总量)>10 and datediff(d,B.最晚入库日期,A.最晚出库日期)>100

这句问题吧
回复
-狙击手- 2008-07-03
好乱

回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2008-07-03 02:09
社区公告
暂无公告