CREATE PROCEDURE prg_cbhs (@ldt_start datetime,@ldt_end datetime)
AS
select f_billid =a.f_billid, ----------客户订单号
f_llbillid =b.f_billid ------ 生产指今单号
into #table1
from t_sellorder a ,t_task b
where a.f_billid =b.f_planno and a.f_date between @ldt_start and @ldt_end
select f_billid =a.f_billid,
f_llbillid =a.f_llbillid,
f_xsje =sum(b.f_xsje),
f_jhcb =sum(b.f_jhcb),
f_sjcb =sum(b.f_sjcb)
from
#table1 a,
(
select f_billid =d.f_billid,
f_xsje =sum(d.f_discountmoney), -----------订单金额
f_jhcb =0,
f_sjcb =0
from t_sellorderdetail d ,#table1 s
where s.f_billid =d.f_billid
group by d.f_billidunion all
select f_planno,
f_xsje =0,
f_jhcb = sum(f_jhcb),
f_sjcb=0
from(
select m.f_planno,
f_jhcb =(select f_baseprice from t_product where t_product.f_id = d.f_goodsno), --------------生产指令单金额
f_sjcb =0
from t_task m, t_taskdetail d ,#table1 s
-- (select f_baseprice from t_product where t_product.f_id = d.f_goodsno) p
where m.f_billid =d.f_billid and s.f_llbillid =m.f_billid
)a group by f_plannounion all ------------领料单--退料
select f_billid =f_linkbill,
f_xsje =0,
f_jhcb =0,
f_sjcb =sum(f_sjcb)
from(
select d.f_linkbill,
f_sjcb =(select f_baseprice from t_product where t_product.f_id = d.f_goodsno)
from t_drawgoods m ,t_drawgoodsdetail d,#table1 s
where m.f_billid =d.f_billid and s.f_llbillid =d.f_linkbill
)a group by f_linkbillunion all --------------补料单
select f_billid =f_linkbill,
f_xsje =0,
f_jhcb =0,
f_sjcb=sum(f_sjcb)
from(
select d.f_linkbill,
f_sjcb =(select f_baseprice from t_product where t_product.f_id = d.f_goodsno)
from t_addgoods m ,t_addgoodsdetail d,#table1 s
where m.f_billid =d.f_billid and s.f_llbillid =d.f_linkbill
)a group by f_linkbill
) bgroup by a.f_billid,a.f_llbillid
GO
AS
select f_billid =a.f_billid, ----------客户订单号
f_llbillid =b.f_billid ------ 生产指今单号
into #table1
from t_sellorder a ,t_task b
where a.f_billid =b.f_planno and a.f_date between @ldt_start and @ldt_end
select f_billid =a.f_billid,
f_llbillid =a.f_llbillid,
f_xsje =sum(b.f_xsje),
f_jhcb =sum(b.f_jhcb),
f_sjcb =sum(b.f_sjcb)
from
#table1 a,
(
select f_billid =d.f_billid,
f_xsje =sum(d.f_discountmoney), -----------订单金额
f_jhcb =0,
f_sjcb =0
from t_sellorderdetail d ,#table1 s
where s.f_billid =d.f_billid
group by d.f_billidunion all
select f_planno,
f_xsje =0,
f_jhcb = sum(f_jhcb),
f_sjcb=0
from(
select m.f_planno,
f_jhcb =(select f_baseprice from t_product where t_product.f_id = d.f_goodsno), --------------生产指令单金额
f_sjcb =0
from t_task m, t_taskdetail d ,#table1 s
-- (select f_baseprice from t_product where t_product.f_id = d.f_goodsno) p
where m.f_billid =d.f_billid and s.f_llbillid =m.f_billid
)a group by f_plannounion all ------------领料单--退料
select f_billid =f_linkbill,
f_xsje =0,
f_jhcb =0,
f_sjcb =sum(f_sjcb)
from(
select d.f_linkbill,
f_sjcb =(select f_baseprice from t_product where t_product.f_id = d.f_goodsno)
from t_drawgoods m ,t_drawgoodsdetail d,#table1 s
where m.f_billid =d.f_billid and s.f_llbillid =d.f_linkbill
)a group by f_linkbillunion all --------------补料单
select f_billid =f_linkbill,
f_xsje =0,
f_jhcb =0,
f_sjcb=sum(f_sjcb)
from(
select d.f_linkbill,
f_sjcb =(select f_baseprice from t_product where t_product.f_id = d.f_goodsno)
from t_addgoods m ,t_addgoodsdetail d,#table1 s
where m.f_billid =d.f_billid and s.f_llbillid =d.f_linkbill
)a group by f_linkbill
) bgroup by a.f_billid,a.f_llbillid
GO
解决方案 »
- 语句优化
- 一个基本的sql查询
- 问一个关于SQL Server 2005安装失败的问题
- 导csv文件进数据库时报The OLE DB provider "MSDASQL" for linked server "(null)"
- 还原数据库失败 不知道什么原因
- 我设计的一张表中有S_time这个字段,现在要求输入两个不同的时间,找出在这两个时间段中共有多少条记录,不知道该怎么做了,因为这两个时间段
- 请教:如何删除一个表中的记录,对编号相同的只保留其中某个字段的最大值得那一条
- 在基础区贴的贴子,但是不知道怎么解决,特在这里再请教
- 能否用SQL语句来动态修改数据库对象的权限?
- 一个存储过程问题。急!!!!!!!!!!!!!!!!!
- [幼稚]Windows XP能不能安装SQL Server2000?
- 如何建立这样的表级约束?
f_xsje =0,
f_price,
f_jhcb =sum(d.f_qty * p.f_baseprice ), --------------生产指令单金额
f_sjcb =0
from t_task m, t_taskdetail d ,#table1 s,
(select f_baseprice,f_id from t_product a, t_taskdetail d where a.f_id =d.f_goodsno) p
where m.f_billid =d.f_billid and s.f_llbillid =m.f_billid
group by m.f_planno