怎样消重这些重复数值?-CSDN论坛

⋅反序列化旧文件的问题。 ⋅见鬼！运行时 "传递给 LEFT 或 SUBSTRING 函数的长度参数无效。关键字 'FROM' 附近" ⋅大家都来挑战这个SQL难题 更多帖子 关注私信空间博客 3tzjq 本版专家分：0 结帖率 99.27%

测试表:



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了

0 2009-03-04 09:39:40

回复数 7 只看楼主引用举报楼主

⋅反序列化旧文件的问题。 ⋅见鬼！运行时 "传递给 LEFT 或 SUBSTRING 函数的长度参数无效。关键字 'FROM' 附近" ⋅大家都来挑战这个SQL难题 更多帖子 关注私信空间博客 3tzjq 本版专家分：0: 这里没汇总...数量的结果(有重复值):

这是汇总...数量的结果(数值翻倍):

此SQL该怎么改,才使不出现重复记录已正确汇总呢?

0 2009-03-04 09:46:40

只看TA 引用举报 #1 得分 0

3tzjq 本版专家分：0: 不好意思!上楼的图片放反了,下面那张才是"没汇总...数量的结果(有重复值)"

0 2009-03-04 09:47:58

只看TA 引用举报 #2 得分 0

⋅通知:尽最大努力让小F和晴天本月拿牌,本人这个月剩余几天不抢分. ⋅感谢管理员退还我被盗用的另外一千分.谢谢! ⋅感谢管理员退还我的可用分.不过仍有一个疑问. 更多帖子 关注私信空间博客 dawugui 本版专家分：440579 名人 2019年荣获名人称号 年度总版至少三次排名前十即授予名人勋章 榜眼 2008年总版技术专家分年内排行榜第二 进士 2010年总版技术专家分年内排行榜第四 2009年总版技术专家分年内排行榜第五 2007年总版技术专家分年内排行榜第六 金牌 2009年3月总版技术专家分月排行榜第一 2008年12月总版技术专家分月排行榜第一 2008年3月总版技术专家分月排行榜第一 2007年12月总版技术专家分月排行榜第一 2007年10月总版技术专家分月排行榜第一: 又是这么多表?友情帮顶.晚上有空慢慢看,

0 2009-03-04 09:49:26

只看TA 引用举报 #3 得分 20

⋅马丹的，可用分放CSDN没有利息的啊 ⋅分分啥时候能散完啊 ⋅好想一帖散完所有分 更多帖子 关注私信空间博客 快乐_石头 本版专家分：13130

--参考

--处理表重复记录(查询和删除)

/******************************************************************************************************************************************************

1、Num、Name相同的重复值记录,没有大小关系只保留一条

2、Name相同,ID有大小关系时,保留大或小其中一个记录

整理人：中国风(Roy)



日期:2008.06.06

******************************************************************************************************************************************************/



--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)



--> --> (Roy)生成測試數據

 

if not object_id('Tempdb..#T') is null

    drop table #T

Go

Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))

Insert #T

select 1,N'A',N'A1' union all

select 2,N'A',N'A2' union all

select 3,N'A',N'A3' union all

select 4,N'B',N'B1' union all

select 5,N'B',N'B2'

Go





--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2

方法1:

Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)



方法2:

select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID



方法3:

select * from #T a where ID=(select min(ID) from #T where Name=a.Name)



方法4:

select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 



方法5:

select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)



方法6:

select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0



方法7:

select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)



方法8:

select * from #T a where ID!>all(select ID from #T where Name=a.Name)



方法9(注:ID为唯一时可用):

select * from #T a where ID in(select min(ID) from #T group by Name)



--SQL2005:



方法10:

select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID



方法11:



select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1



生成结果:

/*

ID          Name Memo

----------- ---- ----

1           A    A1

4           B    B1



(2 行受影响)

*/





--II、Name相同ID最大的记录,与min相反:

方法1:

Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)



方法2:

select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID



方法3:

select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID



方法4:

select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 



方法5:

select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)



方法6:

select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0



方法7:

select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)



方法8:

select * from #T a where ID!<all(select ID from #T where Name=a.Name)



方法9(注:ID为唯一时可用):

select * from #T a where ID in(select max(ID) from #T group by Name)



--SQL2005:



方法10:

select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID



方法11:

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1



生成结果2:

/*

ID          Name Memo

----------- ---- ----

3           A    A3

5           B    B2



(2 行受影响)

*/







--2、删除重复记录有大小关系时,保留大或小其中一个记录





--> --> (Roy)生成測試數據



if not object_id('Tempdb..#T') is null

    drop table #T

Go

Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))

Insert #T

select 1,N'A',N'A1' union all

select 2,N'A',N'A2' union all

select 3,N'A',N'A3' union all

select 4,N'B',N'B1' union all

select 5,N'B',N'B2'

Go



--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条

方法1:

delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID<a.ID)



方法2:

delete a  from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null



方法3:

delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)



方法4(注:ID为唯一时可用):

delete a from #T a where ID not in(select min(ID)from #T group by Name)



方法5:

delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0



方法6:

delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)



方法7:

delete a from #T a where ID>any(select ID from #T where Name=a.Name)







select * from #T



生成结果:

/*

ID          Name Memo

----------- ---- ----

1           A    A1

4           B    B1



(2 行受影响)

*/





--II、Name相同ID保留最大的一条记录:



方法1:

delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID>a.ID)



方法2:

delete a  from #T a left join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null



方法3:

delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)



方法4(注:ID为唯一时可用):

delete a from #T a where ID not in(select max(ID)from #T group by Name)



方法5:

delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0



方法6:

delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID desc)



方法7:

delete a from #T a where ID<any(select ID from #T where Name=a.Name)





select * from #T

/*

ID          Name Memo

----------- ---- ----

3           A    A3

5           B    B2



(2 行受影响)

*/











--3、删除重复记录没有大小关系时，处理重复值





--> --> (Roy)生成測試數據

 

if not object_id('Tempdb..#T') is null

    drop table #T

Go

Create table #T([Num] int,[Name] nvarchar(1))

Insert #T

select 1,N'A' union all

select 1,N'A' union all

select 1,N'A' union all

select 2,N'B' union all

select 2,N'B'

Go



方法1:

if object_id('Tempdb..#') is not null

    drop table #

Select distinct * into # from #T--排除重复记录结果集生成临时表#



truncate table #T--清空表



insert #T select * from #    --把临时表#插入到表#T中



--查看结果

select * from #T



/*

Num         Name

----------- ----

1           A

2           B



(2 行受影响)

*/



--重新执行测试数据后用方法2

方法2:



alter table #T add ID int identity--新增标识列

go

delete a from  #T a where  exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录

go

alter table #T drop column ID--删除标识列



--查看结果

select * from #T



/*

Num         Name

----------- ----

1           A

2           B



(2 行受影响)



*/



--重新执行测试数据后用方法3

方法3:

declare Roy_Cursor cursor local for

select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1

declare @con int,@Num int,@Name nvarchar(1)

open Roy_Cursor

fetch next from Roy_Cursor into @con,@Num,@Name

while @@Fetch_status=0

begin 

    set rowcount @con;

    delete #T where Num=@Num and Name=@Name

    set rowcount 0;

    fetch next from Roy_Cursor into @con,@Num,@Name

end

close Roy_Cursor

deallocate Roy_Cursor



--查看结果

select * from #T

/*

Num         Name

----------- ----

1           A

2           B



(2 行受影响)

*/

0 2009-03-04 09:49:35

只看TA 引用举报 #4 得分 50

zzxap 本版专家分：1483 黄花 2009年4月 .NET技术大版内专家分月排行榜第二 2009年3月 .NET技术大版内专家分月排行榜第二 蓝花 2009年5月 .NET技术大版内专家分月排行榜第三: 链接的表有重复的数据就会出现这样的问题的，要保证链接表的行唯一

0 2009-03-04 10:04:30

只看TA 引用举报 #5 得分 10

⋅掐碎你的鸟蛋 ⋅如何将库中的全角中文字符转为半角 ⋅两数据量稍大的表关联，如何提效 更多帖子 关注私信空间博客 hrb2008 本版专家分：6509

想了半天好像就想到一种办法。

select



(select Qty from 库存表 where 库存表.id=a.id) as 初存 ,

(select SUM(入库.Qty) from 入库 where 入库.id=a.id and a.date1 = 入库.date1) AS 入库, 

(select SUM(发料.Qty) from 发料 where 发料.id=a.id and a.date1 = 发料.date1) AS 发料, 

(select SUM(退料.Qty) from 退料 where 退料.id=a.id and a.date1 = 退料.date1) AS 退料,

(select SUM(报废.Qty) from 报废 where 报废.id=a.id and a.date1 = 报废.date1) AS 报废,

A.date1 日期

-- 结存这应该有个算术计算吧

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

0 2009-03-04 10:15:34

只看TA 引用举报 #6 得分 20

xjwangbb 本版专家分：25: 有点不知所云你到底想要什么样的结果

0 2009-03-04 10:33:47

只看TA 引用举报 #7 得分 0

怎样消重这些重复数值? [问题点数：100分，结帖人3tzjq]