怎样消重这些重复数值?
测试表:
SQL问题语句(如果日期相同,就有重复记录):
但如果进行汇总,其中重复数据就翻倍了:
其中M01的"退料","报废"实际是0.22 和 0.12 ,由于有两行数据,结果成了0.44 和 0.24了
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '库存表')
DROP Table 库存表
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '入库')
DROP Table 入库
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '发料')
DROP Table 发料
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '退料')
DROP Table 退料
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '报废')
DROP Table 报废
GO
create table 基础表
(ID varchar(10),MType varchar(15),Unit varchar(10))
insert into 基础表 select 'M01','CAP','K'
insert into 基础表 select 'M02','RES','PCS'
create table 库存表
(ID varchar(10),Qty numeric(15,4))--Qty 为结存量
insert into 库存表 select 'M01',20
insert into 库存表 select 'M02',1500
create table 入库
(ID varchar(10),Qty numeric(15,4),date1 datetime)
insert into 入库 select 'M01',1.5, '2008-5-15'
insert into 入库 select 'M01',5.5, '2008-5-15'
insert into 入库 select 'M01',2.5, '2008-5-16'
insert into 入库 select 'M02',500, '2008-5-15'
insert into 入库 select 'M01',5.5, '2008-8-05'
insert into 入库 select 'M02',2500, '2008-5-05'
create table 发料
(ID varchar(10),Qty numeric(15,4),date1 datetime)
insert into 发料 select 'M01',2.5,'2008-6-10'
insert into 发料 select 'M01',1 ,'2008-5-15'
insert into 发料 select 'M02',1500, '2008-6-10'
create table 退料
(ID varchar(10),Qty numeric(15,4),date1 datetime)
insert into 退料 select 'M01',0.22 ,'2008-5-15'
insert into 退料 select 'M01',0.05 ,'2008-7-15'
insert into 退料 select 'M02',50 ,'2008-6-15'
create table 报废
(ID varchar(10),Qty numeric(15,4),date1 datetime)
insert into 报废 select 'M01',0.05, '2008-7-20'
insert into 报废 select 'M01',0.12, '2008-5-15'
insert into 报废 select 'M02',20, '2008-7-20'
SQL问题语句(如果日期相同,就有重复记录):
select A.ID AS 物料编码, 库存表.Qty 初存,入库.Qty AS 入库, 发料.Qty AS 发料,退料.Qty AS 退料,报废.Qty AS 报废,A.date1 日期,库存表.Qty 结存
INTO #TEMP
from(
select ID, date1 from 入库
union
select ID, date1 from 发料
union
select ID, date1 from 退料
union
select ID, date1 from 报废
union
select ID ,NULL FROM 库存表)A
LEFT JOIN 入库 ON A.ID=入库.ID and A.date1=入库.date1
LEFT JOIN 发料 ON A.ID=发料.ID and A.date1=发料.date1
LEFT JOIN 退料 ON A.ID=退料.ID and A.date1=退料.date1
LEFT JOIN 报废 ON A.ID=报废.ID and A.date1=报废.date1
LEFT JOIN 库存表 ON A.ID=库存表.ID
order by A.ID,A.date1 ASC
但如果进行汇总,其中重复数据就翻倍了:
select A.ID AS 物料编码, 库存表.Qty 初存,SUM(入库.Qty) AS 入库, SUM(发料.Qty)AS 发料,SUM(退料.Qty)AS 退料,SUM(报废.Qty)AS 报废,A.date1 日期,库存表.Qty 结存
INTO #TEMP
from(
select ID, date1 from 入库
union
select ID, date1 from 发料
union
select ID, date1 from 退料
union
select ID, date1 from 报废
union
select ID ,NULL FROM 库存表)A
LEFT JOIN 入库 ON A.ID=入库.ID and A.date1=入库.date1
LEFT JOIN 发料 ON A.ID=发料.ID and A.date1=发料.date1
LEFT JOIN 退料 ON A.ID=退料.ID and A.date1=退料.date1
LEFT JOIN 报废 ON A.ID=报废.ID and A.date1=报废.date1
LEFT JOIN 库存表 ON A.ID=库存表.ID
group by A.ID,库存表.Qty,A.date1
order by A.ID,A.date1 ASC
declare @InNum numeric(15,4) ,@InitNum numeric(15,4) ,@物料编码 varchar(10),@入库 numeric(15,4),@初存 numeric(15,4),
@发料 numeric(15,4),@退料 numeric(15,4),@报废 numeric(15,4),@结存 numeric(15,4),@日期 datetime,@D datetime
Declare My_cur Cursor
for
Select 物料编码,初存,入库, 发料,退料,报废 ,结存,日期 from #TEMP
SET @InNum=0
SET @InitNum=0
SET @D = NULL
Open My_cur
Fetch Next From My_cur
Into @物料编码,@初存,@入库,@发料,@退料,@报废,@结存,@日期
While(@@Fetch_Status=0)
begin
update #TEMP Set 初存 = CASE WHEN @D IS NULL THEN 结存 ELSE @InitNum END ,结存=isnull(@InNum,0)+ISNULL(@入库,0)-ISNULL(@发料,0)+ISNULL(@退料,0)-ISNULL(@报废,0)+isnull(@结存,0)
where 物料编码=@物料编码 AND 日期=@日期 and 日期 is not null
Select @InitNum = 结存 From #TEMP Where 物料编码=@物料编码 AND 日期=@日期 and 日期 is not null
Select @InNum=isnull(@InNum,0)+ISNULL(@入库,0)-ISNULL(@发料,0)+ISNULL(@退料,0)-ISNULL(@报废,0)
Select @D=@日期
Fetch Next From My_cur Into @物料编码,@初存,@入库,@发料,@退料,@报废,@结存,@日期
end
Close My_cur
Deallocate My_cur
SELECT * FROM #TEMP
drop table #TEMP
其中M01的"退料","报废"实际是0.22 和 0.12 ,由于有两行数据,结果成了0.44 和 0.24了
...全文


疑难问题
