dawugui
潇洒老乌龟 发表于:2007-11-13 11:48:131楼 得分:0
也不知道我那个写法是否就是你要的结果?
我上个帖子说的问题过于简单,因此实现起来并不难,但我的实际问题却非常复杂,我在附件中描述的问题还不包括我查询时要输入的查询条件(就是根据表A中的hcode和jsrq)进行查询。嘿嘿,希望高手能帮忙分析解决。
潇洒老乌龟 发表于:2007-11-13 11:48:131楼 得分:0
也不知道我那个写法是否就是你要的结果?
我上个帖子说的问题过于简单,因此实现起来并不难,但我的实际问题却非常复杂,我在附件中描述的问题还不包括我查询时要输入的查询条件(就是根据表A中的hcode和jsrq)进行查询。嘿嘿,希望高手能帮忙分析解决。
本人是搞ERP二次开发的,给客户做统计分析时,查询出来的数据很多都是重复的,一直没有办法解决
望高手给予指导!
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 12:50:295楼 得分:0
想要的结果不能仅仅简单的通过ihcode联接 */可是这两个表只有这个字段是关键字,能进行联接
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
*/
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 intselect @id=0,@ihcode=null
update @A set id=@id,@id=case when ihcode=@ihcode then @id+1 else 1 end,@ihcode=ihcodeselect @id=0,@ihcode=null
update @B set id=@id,@id=case when ihcode=@ihcode then @id+1 else 1 end,@ihcode=ihcode where jstype=1select
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
*/
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 intselect @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 bselect @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=1select
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 nullunion allselect
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