求sql（救急）！！！
两个表，多对多关系！
表1 v1
id01 id02 id03 val name
101 201 301 123.00 aaaa
102 202 302 100.00 bbbb
102 202 302 56.00 eeee
103 203 303 145.00 cccc
100 205 306 56.00 kkkk
表2 v2
id01 id02 id03 val de
101 205 301 256.00 123.00
101 202 303 246.00 123.00
102 202 302 456.00 89.00
103 201 303 789.00 123.00
100 205 306 100.00 89.00
100 205 306 78.00 56.00
100 205 306 100.00 89.00
得到的结果： 除了金额以外都要显示，金额不能重复显示
id01 id02 id03 v1_val v1_name v2_val v2_de
102 202 302 100.00 bbbb 456.00 89.00
102 202 302 56.00 eeee
100 205 306 56.00 kkkk 200.00 178.00
100 205 306 kkkk 78.00 56.00
[解决办法]
通过临时表实现：
declare @v1 table(id01 int,id02 int, id03 int, val money , name varchar(5))
insert @v1 select 101, 201, 301, 123.00, 'aaaa '
insert @v1 select 102, 202, 302, 100.00, 'bbbb '
insert @v1 select 102, 202, 302, 56.00, 'eeee '
insert @v1 select 103, 203, 303, 145.00, 'cccc '
insert @v1 select 100, 205, 306, 56.00, 'kkkk '
declare @v2 table(id01 int, id02 int, id03 int, val money , de money)
insert @v2 select 101, 205, 301, 256.00, 123.00
insert @v2 select 101, 202, 303, 246.00, 123.00
insert @v2 select 102, 202, 302, 456.00, 89.00
insert @v2 select 103, 201, 303, 789.00, 123.00
insert @v2 select 100, 205, 306, 100.00, 89.00
insert @v2 select 100, 205, 306, 78.00, 56.00
insert @v2 select 100, 205, 306, 100.00, 89.00
select tmp1.id01,tmp1.id02,tmp1.id03,
[val]=tmp2.val,
[name]=name,
[v2_val]=tmp1.val,
de,
id=identity(int,1,1)
into # --临时表实现
from
(select id01,id02,id03,val=sum(val),de=sum(de)
from @v2 v2
where exists(select 1 from @v1 where
id01=v2.id01 and id02=v2.id02 and id03=v2.id03)
group by id01,id02,id03,val,de)tmp1
left join
(select *
from @v1 v1
where exists(select 1 from @v2 where
id01=v1.id01 and id02=v1.id02 and id03=v1.id03))tmp2
on
tmp1.id01=tmp2.id01 and tmp1.id02=tmp2.id02 and tmp1.id03=tmp2.id03
order by tmp1.id01 desc ,v2_val desc
select id01,id02,id03,
[val]=case when exists(select 1 from # where id01=a.id01 and id02=a.id02 and id03=a.id03 and name=a.name and id <a.id) then ' '
else rtrim(val) end,
[name]=case when exists(select 1 from # where id01=a.id01 and id02=a.id02 and id03=a.id03 and name=a.name and id <a.id) then ' '
else name end,
[v2_val]=case when exists(select 1 from # where id01=a.id01 and id02=a.id02 and id03=a.id03 and v2_val=a.v2_val and id <a.id) then ' '
else rtrim(v2_val) end,
[v2_de]=case when exists(select 1 from # where id01=a.id01 and id02=a.id02 and id03=a.id03 and de=a.de and id <a.id) then ' '
else rtrim(de) end
from # a
--drop table #
id01 id02 id03 val name v2_val v2_de
----------- ----------- ----------- ---------------------------------------- ----- ---------------------------------------- ----------------------------------------
102 202 302 100.00 bbbb 456.00 89.00
102 202 302 56.00 eeee
100 205 306 56.00 kkkk 200.00 178.00
100 205 306 78.00 56.00
(4 行受影响)
