简单分组语句问题，在线等待

[laurecn]

begin tran
create table ly1
(
ID int,num int,code int
)
go
insert into ly1 values(1,2,1)
insert into ly1 values(2,2,1)
insert into ly1 values(3,2,2)
insert into ly1 values(4,2,2)
insert into ly1 values(5,2,3)
insert into ly1 values(6,2,4)
create table ly2
(
code int,num int,p int
)
go
insert into ly2 values(1,3,1)
insert into ly2 values(2,5,1)
insert into ly2 values(3,2,1)
insert into ly2 values(4,3,1)
go
--下面部分要实现问题，但是结果错误，请问如何处理呢
select b.code
from ly1 a,ly2 b
where a.code=b.code
group by b.code
having sum(a.num)<sum(b.num)
go

rollback tran

表ly1中存储有各种物品的库存明细，code是物品编号，num是数量；ly2中存储需要从仓库中领取的纪录信息，code是物品编号，num是需要领取数量；
现在要取出来库存不够的物品编号列表，如何处理？不希望是游标处理（以前好像写过但忘记了，求助）

[老宛]

你想要得到什么结果？

[青团子]

查询出来的结果不就是库存不够物品编号列表么？

[青团子]

select b.code
from (select Sum(num) num,code From ly1 group by code) a,ly2 b
where a.code=b.code
group by b.code
having sum(a.num)<sum(b.num)

[laurecn]

要取出来库存不够的物品编号列表，如何处理？
取出来的结果不正确，可以在sql中试验一下

[老宛]

试试这个：
select b.code
from (select code,sum(num)as num from ly1 group by code) a,(select code,sum(num) as num from ly2 group by code) b
where a.code=b.code and a.num<b.num

[老宛]

结果为2和4，应该是对的吧

[laurecn]

Cityfire(青团子) 结果对了，先谢谢你
同时我还要取出来 库存总数量呢

[青团子]

select b.code,sum(a.num) InNum,sum(b.num) OutNum
from (select Sum(num) num,code From ly1 group by code) a,ly2 b
where a.code=b.code
group by b.code
having sum(a.num)<sum(b.num)