22,209
社区成员
发帖
与我相关
我的任务
分享关于多表合并连接查询，如何去掉重复的结果？
本人上午所发也是关于本问题的帖子已经结帐。
表结构已经在附件中说明，想要的结果里面也有描述，望高手帮忙解决！
谢谢！在线等结果。
若下午能够解决，再奉50分
表结构已经在附件中说明，想要的结果里面也有描述，望高手帮忙解决！
谢谢！在线等结果。
若下午能够解决，再奉50分
...全文
请发表友善的回复…
发表回复
rideyou 2007-11-13
- 打赏
- 举报
/*
Limpire
昨夜小楼
发表于：2007-11-13 12:50:295楼 得分:0
想要的结果不能仅仅简单的通过ihcode联接
*/
可是这两个表只有这个字段是关键字，能进行联接
Limpire
昨夜小楼
发表于：2007-11-13 12:50:295楼 得分:0
想要的结果不能仅仅简单的通过ihcode联接
*/
可是这两个表只有这个字段是关键字，能进行联接
Limpire 2007-11-13
- 打赏
- 举报
--原始数据：@A
declare @A table(ihcode int,hcode varchar(8),jsrq datetime)
insert @A
select 8,'P-061003','2006-12-31' union all
select 8,'P-061003','2007-11-9' union all
select 15,'P-061008','2006-5-31' union all
select 20,'P-061006','2006-8-14' union all
select 30,'P-061014','2007-1-16'
--原始数据：@B
declare @B table(ihcode int,hcode varchar(8),idx int,jstype int,jgtype varchar(2),zl money,je money,bz char(1))
insert @B
select 8,'P-061003',100,1,'00',350.00,-32.00,'' union all
select 8,'P-061003',300,1,'00',20.00,-18.00,'' union all
select 8,'P-061003',200,1,'02',50.00,-18.00,'' union all
select 30,'P-061014',100,2,'01',13149.81,12.84,'' union all
select 30,'P-061014',100,3,'01',400.85,12.84,'' union all
select 30,'P-061014',100,1,'00',500.00,12.91,'' union all
select 30,'P-061014',200,1,'01',1900.04,12.9,''
-- SQL 2005
select a.ihcode,a.hcode,a.jsrq,b.jgtype,b.zl,b.je,bz from
(select id=row_number() over (partition by ihcode order by ihcode),* from @A) as a
right join
(select id=row_number() over (partition by ihcode order by ihcode),* from @B where jstype=1) as b
on a.id=b.id and a.ihcode=b.ihcode
/*
ihcode hcode jsrq jgtype zl je bz
----------- -------- ----------------------- ------ --------------------- --------------------- ----
8 P-061003 2006-12-31 00:00:00.000 00 350.00 -32.00
8 P-061003 2007-11-09 00:00:00.000 00 20.00 -18.00
NULL NULL NULL 02 50.00 -18.00
30 P-061014 2007-01-16 00:00:00.000 00 500.00 12.91
NULL NULL NULL 01 1900.04 12.90
*/
Limpire 2007-11-13
- 打赏
- 举报
想要的结果不能仅仅简单的通过ihcode联接
rideyou 2007-11-13
- 打赏
- 举报
呵呵，是很麻烦
本人是搞ERP二次开发的，给客户做统计分析时，查询出来的数据很多都是重复的，一直没有办法解决
望高手给予指导！
本人是搞ERP二次开发的，给客户做统计分析时，查询出来的数据很多都是重复的，一直没有办法解决
望高手给予指导！
dawugui 2007-11-13
- 打赏
- 举报
既然这么麻烦，那不如用程序写了。
rideyou 2007-11-13
- 打赏
- 举报
dawugui
潇洒老乌龟
发表于：2007-11-13 11:48:131楼 得分:0
也不知道我那个写法是否就是你要的结果?
我上个帖子说的问题过于简单，因此实现起来并不难，但我的实际问题却非常复杂，我在附件中描述的问题还不包括我查询时要输入的查询条件（就是根据表A中的hcode和jsrq）进行查询。
嘿嘿，希望高手能帮忙分析解决。
潇洒老乌龟
发表于：2007-11-13 11:48:131楼 得分:0
也不知道我那个写法是否就是你要的结果?
我上个帖子说的问题过于简单，因此实现起来并不难，但我的实际问题却非常复杂，我在附件中描述的问题还不包括我查询时要输入的查询条件（就是根据表A中的hcode和jsrq）进行查询。
嘿嘿，希望高手能帮忙分析解决。
dawugui 2007-11-13
- 打赏
- 举报
也不知道我那个写法是否就是你要的结果?
Limpire 2007-11-13
- 打赏
- 举报
declare @A table(id int,cn int,ihcode int,hcode varchar(8),jsrq datetime)
insert @A
select null,null,8,'P-061003','2006-12-31' union all
select null,null,8,'P-061003','2007-11-9' union all
select null,null,15,'P-061008','2006-5-31' union all
select null,null,20,'P-061006','2006-8-14' union all
select null,null,30,'P-061014','2007-1-16'
declare @B table(id int,cn int,ihcode int,hcode varchar(8),idx int,jstype int,jgtype varchar(2),zl money,je money,bz char(1))
insert @B
select null,null,8,'P-061003',100,1,'00',350.00,-32.00,'' union all
select null,null,8,'P-061003',300,1,'00',20.00,-18.00,'' union all
select null,null,8,'P-061003',200,1,'02',50.00,-18.00,'' union all
select null,null,30,'P-061014',100,2,'01',13149.81,12.84,'' union all
select null,null,30,'P-061014',100,3,'01',400.85,12.84,'' union all
select null,null,30,'P-061014',100,1,'00',500.00,12.91,'' union all
select null,null,30,'P-061014',200,1,'01',1900.04,12.9,''
declare @id int,@ihcode int
select @id=0,@ihcode=null
update @A set id=@id,@id=case when ihcode=@ihcode then @id+1 else 1 end,@ihcode=ihcode
update @A set cn=(select count(1) from @A where ihcode=b.ihcode) from @A b
select @id=0,@ihcode=null
update @B set id=@id,@id=case when ihcode=@ihcode then @id+1 else 1 end,@ihcode=ihcode where jstype=1
update @B set cn=(select count(1) from @B where ihcode=b.ihcode and jstype=1) from @B b where jstype=1
select
a.ihcode,a.hcode,a.jsrq,b.jgtype,b.zl,b.je,bz
from
@A a right join @B b on a.id=b.id and a.ihcode=b.ihcode and a.cn<=b.cn
where b.id is not null
union all
select
a.ihcode,a.hcode,a.jsrq,b.jgtype,b.zl,b.je,bz
from
@A a left join @B b on a.id=b.id and a.ihcode=b.ihcode and a.cn>b.cn
where b.id is not null
Limpire 2007-11-13
- 打赏
- 举报
/*
SQL 2000
简单起见，直接加 id 列：
*/
declare @A table(id int,ihcode int,hcode varchar(8),jsrq datetime)
insert @A
select null,8,'P-061003','2006-12-31' union all
select null,8,'P-061003','2007-11-9' union all
select null,15,'P-061008','2006-5-31' union all
select null,20,'P-061006','2006-8-14' union all
select null,30,'P-061014','2007-1-16'
declare @B table(id int,ihcode int,hcode varchar(8),idx int,jstype int,jgtype varchar(2),zl money,je money,bz char(1))
insert @B
select null,8,'P-061003',100,1,'00',350.00,-32.00,'' union all
select null,8,'P-061003',300,1,'00',20.00,-18.00,'' union all
select null,8,'P-061003',200,1,'02',50.00,-18.00,'' union all
select null,30,'P-061014',100,2,'01',13149.81,12.84,'' union all
select null,30,'P-061014',100,3,'01',400.85,12.84,'' union all
select null,30,'P-061014',100,1,'00',500.00,12.91,'' union all
select null,30,'P-061014',200,1,'01',1900.04,12.9,''
declare @id int,@ihcode int
select @id=0,@ihcode=null
update @A set id=@id,@id=case when ihcode=@ihcode then @id+1 else 1 end,@ihcode=ihcode
select @id=0,@ihcode=null
update @B set id=@id,@id=case when ihcode=@ihcode then @id+1 else 1 end,@ihcode=ihcode where jstype=1
select
a.ihcode,a.hcode,a.jsrq,b.jgtype,b.zl,b.je,bz
from
@A a right join @B b on a.id=b.id and a.ihcode=b.ihcode
where b.id is not null
/*
ihcode hcode jsrq jgtype zl je bz
----------- -------- ----------------------- ------ --------------------- --------------------- ----
8 P-061003 2006-12-31 00:00:00.000 00 350.00 -32.00
8 P-061003 2007-11-09 00:00:00.000 00 20.00 -18.00
NULL NULL NULL 02 50.00 -18.00
30 P-061014 2007-01-16 00:00:00.000 00 500.00 12.91
NULL NULL NULL 01 1900.04 12.90
*/
rideyou 2007-11-13
- 打赏
- 举报
回复6楼的大哥，您这是在SQL2005中做的，我在SQL2k中提示'row_number' 不是可以识别的 函数名。
Limpire 2007-11-13
- 打赏
- 举报
--原始数据：@A
declare @A table(ihcode int,hcode varchar(8),jsrq datetime)
insert @A
select 8,'P-061003','2006-12-31' union all
select 8,'P-061003','2007-11-9' union all
select 15,'P-061008','2006-5-31' union all
select 20,'P-061006','2006-8-14' union all
select 30,'P-061014','2007-1-16'
--原始数据：@B
declare @B table(ihcode int,hcode varchar(8),idx int,jstype int,jgtype varchar(2),zl money,je money,bz char(1))
insert @B
select 8,'P-061003',100,1,'00',350.00,-32.00,'' union all
select 8,'P-061003',300,1,'00',20.00,-18.00,'' union all
select 8,'P-061003',200,1,'02',50.00,-18.00,'' union all
select 30,'P-061014',100,2,'01',13149.81,12.84,'' union all
select 30,'P-061014',100,3,'01',400.85,12.84,'' union all
select 30,'P-061014',100,1,'00',500.00,12.91,'' union all
select 30,'P-061014',200,1,'01',1900.04,12.9,''
/*
2000的就不写了，通过临时表或其它方法，构造下面两个结果集，再联接：
*/
select id=row_number() over (partition by ihcode order by ihcode),* from @A
/*
id ihcode hcode jsrq
-------------------- ----------- -------- -----------------------
1 8 P-061003 2006-12-31 00:00:00.000
2 8 P-061003 2007-11-09 00:00:00.000
1 15 P-061008 2006-05-31 00:00:00.000
1 20 P-061006 2006-08-14 00:00:00.000
1 30 P-061014 2007-01-16 00:00:00.000
*/
select id=row_number() over (partition by ihcode order by ihcode),* from @B where jstype=1
/*
id ihcode hcode idx jstype jgtype zl je bz
-------------------- ----------- -------- ----------- ----------- ------ --------------------- --------------------- ----
1 8 P-061003 100 1 00 350.00 -32.00
2 8 P-061003 300 1 00 20.00 -18.00
3 8 P-061003 200 1 02 50.00 -18.00
1 30 P-061014 100 1 00 500.00 12.91
2 30 P-061014 200 1 01 1900.04 12.90
*/
