如何取得一个科目的对方科目
小弟不才，为了取得这些科目的对方科目，伤透了脑筋，无奈之下请教CSDN的大神们帮帮我了，不多说贴上数据
id 是自增列 ino_id是凭证号 ccode 是科目代码 md 是借方金额 mc是贷方金额 ccode_equal是对方科目
一个凭证编号内如果借贷科目是一一对应的话还好做，可有些是一对多的关系我就很难做出如下效果了
还有如果贷方或者借方有多个重复科目，对方科目只能取一个不重复的代码
代码示例已经放出来了，还请大家帮帮我了。
id ino_id ccode md mc ccode_equal1 1 1001 10 0 11312 1 1131 0 10 10013 2 1001 100 0 1131,1130,11514 2 1131 0 40 10015 2 1130 0 50 10016 2 1151 0 10 10017 3 1001 100 0 1131,11218 3 1131 0 50 10019 3 1121 0 50 100110 4 1001 0 100 1131,112111 4 1131 50 0 100112 4 1131 30 0 100113 4 1121 20 0 1001
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([id] int,[ino_id] int,[ccode] int,[md] int,[mc] int,[ccode_equal] varchar(20))goinsert [test]select 1,1,1001,10,0,1131 union allselect 2,1,1131,0,10,1001 union allselect 3,2,1001,100,0,1131 union allselect 4,2,1131,0,40,1001 union allselect 5,2,1130,0,50,1001 union allselect 6,2,1151,0,10,1001 union allselect 7,3,1001,100,0,1131 union allselect 8,3,1131,0,50,1001 union allselect 9,3,1121,0,50,1001 union allselect 10,4,1001,0,100,1131 union allselect 11,4,1131,50,0,1001 union allselect 12,4,1131,30,0,1001 union allselect 13,4,1121,20,0,1001gowith tas(select px=ROW_NUMBER()over(partition by [ino_id] order by getdate()),* from test),mas(select px=row_number()over(partition by ino_id order by getdate()), ino_id, right(N.[ccode],len(N.[ccode])-CHARINDEX(',',N.[ccode])) [ccode_equal]from ( select distinct [ino_id] from test )Aouter apply(select distinct [ccode]= STUFF(REPLACE(REPLACE( (select distinct [ccode] from test N where ino_id = A.ino_id for xml auto ),'<N ccode="', ','), '"/>', ''), 1, 1, ''))N)select t.id,t.ino_id,t.ccode,t.md,t.mc,case when t.px=1 then m.ccode_equal else t.ccode_equal end as ccode_equalfrom tleft join mon t.px=m.px and t.ino_id=m.ino_id/*id ino_id ccode md mc ccode_equal-------------------------------------------------1 1 1001 10 0 11312 1 1131 0 10 10013 2 1001 100 0 1130,1131,11514 2 1131 0 40 10015 2 1130 0 50 10016 2 1151 0 10 10017 3 1001 100 0 1121,11318 3 1131 0 50 10019 3 1121 0 50 100110 4 1001 0 100 1121,113111 4 1131 50 0 100112 4 1131 30 0 100113 4 1121 20 0 1001*/
[解决办法]
一个语句搞定
SELECT id,ino_id,ccode,md,mc, STUFF(REPLACE(REPLACE((SELECT ccode FROM #t b WHERE b.ino_id=a.ino_id (CASE WHEN a.md=0 THEN b.mc ELSE b.md END)=0 FOR XML AUTO ),'<b ccode="', ','), '"/>', ''),1,1,'') ccode_equal FROM #t a
[解决办法]
sql2000用函数
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([id] int,[ino_id] int,[ccode] int,[md] int,[mc] int,[ccode_equal] varchar(14))insert [tb]select 1,1,1001,10,0,null union allselect 2,1,1131,0,10,null union allselect 3,2,1001,100,0,null union allselect 4,2,1131,0,40,null union allselect 5,2,1130,0,50,null union allselect 6,2,1151,0,10,null union allselect 7,3,1001,100,0,null union allselect 8,3,1131,0,50,null union allselect 9,3,1121,0,50,null union allselect 10,4,1001,0,100,null union allselect 11,4,1131,50,0,null union allselect 12,4,1131,30,0,null union allselect 13,4,1121,20,0,nullgo-->创建函数create function [dbo].[f_ccode](@ino_id int,@md int,@mc int)returns varchar(8000)asbegin declare @r varchar(8000)if @md=0 begin select @r=isnull(@r+',','')+ltrim(ccode) from (select distinct ccode from tb where ino_id = @ino_id and mc=0 ) tendelse begin select @r=isnull(@r+',','')+ltrim(ccode) from (select distinct ccode from tb where ino_id = @ino_id and md=0 ) t end return @rendgoselect id,ino_id,ccode,md,mc,dbo.f_ccode(ino_id,md,mc) as ccode_qual from tb/**id ino_id ccode md mc ccode_qual----------- ----------- ----------- ----------- ----------- -------------------------1 1 1001 10 0 11312 1 1131 0 10 10013 2 1001 100 0 1130,1131,11514 2 1131 0 40 10015 2 1130 0 50 10016 2 1151 0 10 10017 3 1001 100 0 1121,11318 3 1131 0 50 10019 3 1121 0 50 100110 4 1001 0 100 1121,113111 4 1131 50 0 100112 4 1131 30 0 100113 4 1121 20 0 1001(13 行受影响)**/