我有6个表如下
T1
DM J1
A111 20.00
A114 40.00
A117 30.00
T2
DM J2
A112 35.00
A113 66.00
A114 40.00T3
DM J3
A115 77.00
A116 73.00
A117 30.00
A230 71.00T4
DM J4
A117 30.00
A118 63.00
A230 71.00
A231 88.00
A325 93.00T5
DM J5
A118 63.00
A231 88.00T6
DM J6
A113 66.00
A115 77.00
A118 63.00
A325 93.00
要得到TZ (TZ已建好) 格式和结果如下;
DM J1 J2 J3 J4 J5 J6 JZ
A111 20.00 0.00 0.00 0.00 0.00 0.00 20.00
A112 0.00 35.00 0.00 0.00 0.00 0.00 35.00
A113 0.00 66.00 0.00 0.00 0.00 66.00 132.00
A114 40.00 40.00 0.00 0.00 0.00 0.00 80.00
A115 0.00 0.00 77.00 0.00 0.00 77.00 154.00
A116 0.00 0.00 73.00 0.00 0.00 0.00 73.00
A117 30.00 0.00 30.00 30.00 0.00 0.00 90.00
A118 0.00 0.00 0.00 63.00 63.00 63.00 189.00
A230 0.00 0.00 71.00 71.00 0.00 0.00 142.00
A231 0.00 0.00 0.00 88.00 88.00 0.00 176.00
A325 0.00 0.00 0.00 93.00 0.00 93.00 186.00
合计: 90.00 141.00 251.00 345.00 151.00 299.00 1277.00求SQL SERVER语句
T1
DM J1
A111 20.00
A114 40.00
A117 30.00
T2
DM J2
A112 35.00
A113 66.00
A114 40.00T3
DM J3
A115 77.00
A116 73.00
A117 30.00
A230 71.00T4
DM J4
A117 30.00
A118 63.00
A230 71.00
A231 88.00
A325 93.00T5
DM J5
A118 63.00
A231 88.00T6
DM J6
A113 66.00
A115 77.00
A118 63.00
A325 93.00
要得到TZ (TZ已建好) 格式和结果如下;
DM J1 J2 J3 J4 J5 J6 JZ
A111 20.00 0.00 0.00 0.00 0.00 0.00 20.00
A112 0.00 35.00 0.00 0.00 0.00 0.00 35.00
A113 0.00 66.00 0.00 0.00 0.00 66.00 132.00
A114 40.00 40.00 0.00 0.00 0.00 0.00 80.00
A115 0.00 0.00 77.00 0.00 0.00 77.00 154.00
A116 0.00 0.00 73.00 0.00 0.00 0.00 73.00
A117 30.00 0.00 30.00 30.00 0.00 0.00 90.00
A118 0.00 0.00 0.00 63.00 63.00 63.00 189.00
A230 0.00 0.00 71.00 71.00 0.00 0.00 142.00
A231 0.00 0.00 0.00 88.00 88.00 0.00 176.00
A325 0.00 0.00 0.00 93.00 0.00 93.00 186.00
合计: 90.00 141.00 251.00 345.00 151.00 299.00 1277.00求SQL SERVER语句
declare @t1 table(DM char(4),J1 decimal(10,2))
declare @t2 table(DM char(4),J2 decimal(10,2))
declare @t3 table(DM char(4),J3 decimal(10,2))
declare @t4 table(DM char(4),J4 decimal(10,2))
declare @t5 table(DM char(4),J5 decimal(10,2))
declare @t6 table(DM char(4),J6 decimal(10,2))insert @t1 select 'A111','20.00'
union all select 'A114','40.00'
union all select 'A117','30.00'
insert @t2 select 'A112','35.00'
union all select 'A113','66.00'
union all select 'A114','40.00'
insert @t3 select 'A115','77.00'
union all select 'A116','73.00'
union all select 'A117','30.00'
union all select 'A230','71.00'insert @t4 select 'A117','30.00'
union all select 'A118','63.00'
union all select 'A230','71.00'
union all select 'A231','88.00'
union all select 'A325','93.00'
insert @t5 select 'A118','63.00'
union all select 'A231','88.00'insert @t1 select 'A113','66.00'
union all select 'A115','77.00'
union all select 'A118','63.00'
union all select 'A325','93.00'select
DM = case when DM is null then '合计' else DM end,
J1 = sum(case f when 1 then j else 0 end),
J2 = sum(case f when 2 then j else 0 end),
J3 = sum(case f when 3 then j else 0 end),
J4 = sum(case f when 4 then j else 0 end),
J5 = sum(case f when 5 then j else 0 end),
J6 = sum(case f when 6 then j else 0 end),
JZ = sum(j)
from(
select DM,J1 as j,1 as f
from @t1
union all
select DM,J2 as j,2 as f
from @t2
union all
select DM,J3 as j,3 as f
from @t3
union all
select DM,J4 as j,4 as f
from @t4
union all
select DM,J5 as j,5 as f
from @t5
union all
select DM,J6 as j,6 as f
from @t6)a
group by DM
with rollup
在编写操作数据的代码一般为:
mygrade.rsdk1 "select * from tz" 等
上述语句一般较短,书写容易,但要将上述代码放入一行书写,明显不行,用VB的换行符换行书写SQL又不认,如何解决!!!