/*创建测试数据*/
if object_id('tbTestA') is not null
drop table tbTestA
if object_id('tbTestB') is not null
drop table tbTestB
GO
create table tbTestA(编码 int, 费用类别 varchar(10), 费用 int)
insert tbTestA
select 23, 1, 4 union all
select 23, 43, 3 union all
select 23, 100, 7 union all
select 23, 76, 1 union all
select 23, 75, 3 union all
select 23, 22, 6 union all
select 25, 12, 54 union all
select 25, 4, 2 union all
select 25, 12, 8 union all
select 25, 76, 5 union all
select 25, 75, 4 union all
select 25, 22, 2create table tbTestB(费用名称 varchar(50), 费用类别 varchar(50))
insert tbTestB
select '书本费', '1,22,75,76' union all
select '文具费', '4,12,43,100'
GO
if object_id('tbTestA') is not null
drop table tbTestA
if object_id('tbTestB') is not null
drop table tbTestB
GO
create table tbTestA(编码 int, 费用类别 varchar(10), 费用 int)
insert tbTestA
select 23, 1, 4 union all
select 23, 43, 3 union all
select 23, 100, 7 union all
select 23, 76, 1 union all
select 23, 75, 3 union all
select 23, 22, 6 union all
select 25, 12, 54 union all
select 25, 4, 2 union all
select 25, 12, 8 union all
select 25, 76, 5 union all
select 25, 75, 4 union all
select 25, 22, 2create table tbTestB(费用名称 varchar(50), 费用类别 varchar(50))
insert tbTestB
select '书本费', '1,22,75,76' union all
select '文具费', '4,12,43,100'
GO
/*查询*/
select a.编码,b.费用名称,sum(a.费用) from tbTestA as a left join tbTestB as b
on charindex(',' + a.费用类别 + ',',',' + b.费用类别 + ',') > 0
group by a.编码,b.费用名称
order by 1/*清除测试环境*/
drop table tbTestA,tbTestB
/*结果
编码 费用名称 费用
-----------------------------------------------
23 书本费 14
23 文具费 10
25 书本费 11
25 文具费 64
*/
请楼主把代码自己整合一下吧。
drop table tablea
gocreate table tablea(编码 int,费用类别 varchar(10),费用 int)
insert into tablea (编码,费用类别,费用) values(23, '1' , 4)
insert into tablea (编码,费用类别,费用) values(23, '43' , 3)
insert into tablea (编码,费用类别,费用) values(23, '100', 7)
insert into tablea (编码,费用类别,费用) values(23, '76' , 1)
insert into tablea (编码,费用类别,费用) values(23, '75' , 3)
insert into tablea (编码,费用类别,费用) values(23, '22' , 6)
insert into tablea (编码,费用类别,费用) values(25, '12' , 54)
insert into tablea (编码,费用类别,费用) values(25, '4' , 2)
insert into tablea (编码,费用类别,费用) values(25, '12' , 8)
insert into tablea (编码,费用类别,费用) values(25, '76' , 5)
insert into tablea (编码,费用类别,费用) values(25, '75' , 4)
insert into tablea (编码,费用类别,费用) values(25, '22' , 2)
goif object_id('pubs..tableb') is not null
drop table tableb
gocreate table tableb(费用名称 varchar(10),费用类别 varchar(20))
insert into tableb(费用名称,费用类别) values('书本费', '1,22,75,76')
insert into tableb(费用名称,费用类别) values('文具费', '4,12,43,100')
goselect 编码 , 费用名称 , sum(费用) 费用 from
(
select a.编码, b.费用名称 , a.费用 from tablea a,tableb b where charindex(','+a.费用类别+',',','+b.费用类别+',')>0
) t
group by 编码 , 费用名称
drop table tablea ,tableb/*
编码 费用名称 费用
----------- ---------- -----------
23 书本费 14
25 书本费 11
23 文具费 10
25 文具费 64(所影响的行数为 4 行)
*/
drop table tablea
gocreate table tablea(编码 int,费用类别 varchar(10),费用 int)
insert into tablea (编码,费用类别,费用) values(23, '1' , 4)
insert into tablea (编码,费用类别,费用) values(23, '43' , 3)
insert into tablea (编码,费用类别,费用) values(23, '100', 7)
insert into tablea (编码,费用类别,费用) values(23, '76' , 1)
insert into tablea (编码,费用类别,费用) values(23, '75' , 3)
insert into tablea (编码,费用类别,费用) values(23, '22' , 6)
insert into tablea (编码,费用类别,费用) values(25, '12' , 54)
insert into tablea (编码,费用类别,费用) values(25, '4' , 2)
insert into tablea (编码,费用类别,费用) values(25, '12' , 8)
insert into tablea (编码,费用类别,费用) values(25, '76' , 5)
insert into tablea (编码,费用类别,费用) values(25, '75' , 4)
insert into tablea (编码,费用类别,费用) values(25, '22' , 2)
goif object_id('pubs..tableb') is not null
drop table tableb
gocreate table tableb(费用名称 varchar(10),费用类别 varchar(20))
insert into tableb(费用名称,费用类别) values('书本费', '1,22,75,76')
insert into tableb(费用名称,费用类别) values('文具费', '4,12,43,100')
goselect a.编码, b.费用名称 , sum(a.费用) 费用 from tablea a,tableb b where charindex(','+a.费用类别+',',','+b.费用类别+',')>0
group by 编码 , 费用名称drop table tablea ,tableb/*
编码 费用名称 费用
----------- ---------- -----------
23 书本费 14
25 书本费 11
23 文具费 10
25 文具费 64(所影响的行数为 4 行)
*/