select c.rq,getdate(),a.shl as '库存数量 ',b.shl as '入库原数量 ', sum(d.chkshl) from sphwph a,ywmxk b,cwk c,splsk d where a.spid=b.spid and b.djbh like 'JHA% ' and b.djbh=c.djbh and a.pihao=b.pihao and b.spid=d.spid and b.pihao =d.pihao and a.shl> 0 and (getdate()-c.rq)> =90 and a.hw= 'HWI00000001 ' group by c.rq,a.shl,b.shl,d.chkshl having(sum(d.chkshl) <=0.2*b.shl ) 这样查出来的数据有问题,重复了好多数据入库日期 当前日期 库存 入库 出库 2007-06-30 2007-11-20 10:57:56.140 1.00 1.00 .00 2007-06-30 2007-11-20 10:57:56.140 2.00 2.00 .00 2007-06-30 2007-11-20 10:57:56.140 2.00 77.00 .00 2007-06-30 2007-11-20 10:57:56.140 2.00 77.00 5.00 2007-06-30 2007-11-20 10:57:56.140 2.00 77.00 3.00 2007-06-30 2007-11-20 10:57:56.140 2.00 77.00 15.00 2007-06-30 2007-11-20 10:57:56.140 2.00 77.00 8.00 2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 -44.00 2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 .00 2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 20.00 2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 24.00 2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 10.00 2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 24.00 2007-06-30 2007-11-20 10:57:56.140 2.00 198.00 .00
我原来是这样写的,可是这样可能存在库存数量是有多次入库的累加 思路:现在库存数量占第一次入库数量的百分八十以上 其中 sphwph 是库存表,ywmxk, cwk是入库表 一个明细,一个汇总的按spid 商品ID pihao批号关联 这样查出的记录有两百多条 select a.shl as '库存数量', c.rq,getdate(),b.shl as '入库原数量',(b.shl-a.shl)as '销售数量' from sphwph a,ywmxk b,cwk c where a.spid=b.spid and b.djbh like 'JHA%' and b.djbh=c.djbh and a.pihao=b.pihao and a.shl>=0.8*b.shl and a.shl>0 and (getdate()-c.rq)>=90 and a.hw='HWI00000001'60.00 2007-06-30 2007-11-20 11:19:39.530 60.00 .00 300.00 2007-06-30 2007-11-20 11:19:39.530 300.00 .00 24.00 2007-06-30 2007-11-20 11:19:39.530 30.00 6.00 240.00 2007-06-30 2007-11-20 11:19:39.530 240.00 .00 300.00 2007-06-30 2007-11-20 11:19:39.530 300.00 .00 230.00 2007-06-30 2007-11-20 11:19:39.530 230.00 .00 90.00 2007-06-30 2007-11-20 11:19:39.530 90.00 .00 700.00 2007-06-30 2007-11-20 11:19:39.530 -1100.00 -1800.00 935.00 2007-06-30 2007-11-20 11:19:39.530 1165.00 230.00 `````````````````````````````````````````````````````````````````现在用一另一种思路,总的销售量小于等于入库数量的百分八十出库表splsk 入库表ywmxk 还要关系库存 sphwph 那要不就,刚好是现在库存数量+销售总数量=原煤入库数量 这样要怎么写比较好,你帮我看下,谢谢你第一次回答我的那个就是按销售数量小于百分二十的,要怎么 重新改下
你這樣好像只能求出总销售数量要小于入库数量的百分二十的數據吧select xxx.* ( SELECT c.rq, getdate(), a.shl as '库 存数量 ', b.shl as '入库 原数量 ', sum(d.chkshl), count(*) as rowcnt FROM sphwph a, ywmxk b, cwk c, splsk d WHERE a.spid=b.spid AND b.djbh like 'JHA% ' AND b.djbh=c.djbh AND a.pihao=b.pihao AND b.spid=d.spid AND b.pihao =d.pihao AND a.shl> 0 AND (getdate()-c.rq)> =90 AND a.hw= 'HWI00000001 ' GROUP BY c.rq, a.shl, b.shl, d.chkshl HAVING (sum(d.chkshl) <=0.2*b.shl ) ) xxx where xxx.rowcnt = 1
sum(d.chkshl) from sphwph a,ywmxk b,cwk c,splsk d
where a.spid=b.spid and b.djbh like 'JHA% ' and b.djbh=c.djbh
and a.pihao=b.pihao and b.spid=d.spid and
b.pihao =d.pihao and a.shl> 0 and (getdate()-c.rq)> =90
and a.hw= 'HWI00000001 '
group by c.rq,a.shl,b.shl,d.chkshl
having(sum(d.chkshl) <=0.2*b.shl )
这样查出来的数据有问题,重复了好多数据入库日期 当前日期 库存 入库 出库
2007-06-30 2007-11-20 10:57:56.140 1.00 1.00 .00
2007-06-30 2007-11-20 10:57:56.140 2.00 2.00 .00
2007-06-30 2007-11-20 10:57:56.140 2.00 77.00 .00
2007-06-30 2007-11-20 10:57:56.140 2.00 77.00 5.00
2007-06-30 2007-11-20 10:57:56.140 2.00 77.00 3.00
2007-06-30 2007-11-20 10:57:56.140 2.00 77.00 15.00
2007-06-30 2007-11-20 10:57:56.140 2.00 77.00 8.00
2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 -44.00
2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 .00
2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 20.00
2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 24.00
2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 10.00
2007-06-30 2007-11-20 10:57:56.140 2.00 136.00 24.00
2007-06-30 2007-11-20 10:57:56.140 2.00 198.00 .00
思路:现在库存数量占第一次入库数量的百分八十以上
其中 sphwph 是库存表,ywmxk, cwk是入库表 一个明细,一个汇总的按spid 商品ID pihao批号关联
这样查出的记录有两百多条
select a.shl as '库存数量', c.rq,getdate(),b.shl as '入库原数量',(b.shl-a.shl)as '销售数量'
from sphwph a,ywmxk b,cwk c
where a.spid=b.spid and b.djbh like 'JHA%' and b.djbh=c.djbh
and a.pihao=b.pihao and a.shl>=0.8*b.shl
and a.shl>0 and (getdate()-c.rq)>=90 and a.hw='HWI00000001'60.00 2007-06-30 2007-11-20 11:19:39.530 60.00 .00
300.00 2007-06-30 2007-11-20 11:19:39.530 300.00 .00
24.00 2007-06-30 2007-11-20 11:19:39.530 30.00 6.00
240.00 2007-06-30 2007-11-20 11:19:39.530 240.00 .00
300.00 2007-06-30 2007-11-20 11:19:39.530 300.00 .00
230.00 2007-06-30 2007-11-20 11:19:39.530 230.00 .00
90.00 2007-06-30 2007-11-20 11:19:39.530 90.00 .00
700.00 2007-06-30 2007-11-20 11:19:39.530 -1100.00 -1800.00
935.00 2007-06-30 2007-11-20 11:19:39.530 1165.00 230.00
`````````````````````````````````````````````````````````````````现在用一另一种思路,总的销售量小于等于入库数量的百分八十出库表splsk 入库表ywmxk 还要关系库存 sphwph 那要不就,刚好是现在库存数量+销售总数量=原煤入库数量
这样要怎么写比较好,你帮我看下,谢谢你第一次回答我的那个就是按销售数量小于百分二十的,要怎么 重新改下
xxx.*
(
SELECT c.rq,
getdate(),
a.shl as '库 存数量 ',
b.shl as '入库 原数量 ',
sum(d.chkshl),
count(*) as rowcnt
FROM
sphwph a,
ywmxk b,
cwk c,
splsk d
WHERE
a.spid=b.spid
AND b.djbh like 'JHA% '
AND b.djbh=c.djbh
AND a.pihao=b.pihao
AND b.spid=d.spid
AND b.pihao =d.pihao
AND a.shl> 0
AND (getdate()-c.rq)> =90
AND a.hw= 'HWI00000001 '
GROUP BY
c.rq,
a.shl,
b.shl,
d.chkshl
HAVING
(sum(d.chkshl) <=0.2*b.shl )
) xxx
where
xxx.rowcnt = 1
7楼你的答案是什么意思,相当于子查询,然后这个xxx.rowcnt = 1是?
就是回答你这个的...