g_no g_name amt_tax rate tax disc_amttax disc_tax
PCIU3811254 936.00 0.06 52.98 936.00 52.98
PCIU3811254 运费 8107.20 0.06 458.90 8107.20 458.90
PCIU3811254 燃油附加费 4636.80 0.06 262.46 4636.80 262.46
PCIU3811254 佣金 -243.22 0.06 -13.77 -243.22 -13.77
PCIU3811254 安全附加费 64.80 0.06 3.67 64.80 3.67我想要得到如下的表格:
g_no g_name amt_tax rate tax disc_amttax disc_tax
PCIU3811254 936.00 0.06 52.98 0 0
PCIU3811254 运费 8107.20 0.06 458.90 243.22 13.77
PCIU3811254 燃油附加费 4636.80 0.06 262.46 0 0
PCIU3811254 安全附加费 64.80 0.06 3.67 0 0说明:佣金在折扣列(disc_amttax / disc_tax),运费行create table #t(g_no char(30),g_name char(30),amt_tax numeric(13,2),rate numeric(13,2),tax numeric(13,2),disc_amttax numeric(13,2),disc_tax numeric(13,2))insert into #t values('PCIU3811254','',936.00, 0.06,52.98,936,52.98)
insert into #t values('PCIU3811254','运费',8107.2, 0.06, 458.9, 8107.2, 458.9)
insert into #t values('PCIU3811254','燃油附加费',4636.8, 0.06,262.46,4636.8,262.46)
insert into #t values('PCIU3811254','佣金',-243.22, 0.06, -13.77, -243.22, -13.77)
insert into #t values('PCIU3811254','安全附加费',64.8, 0.06, 3.67, 64.8, 3.67)
PCIU3811254 936.00 0.06 52.98 936.00 52.98
PCIU3811254 运费 8107.20 0.06 458.90 8107.20 458.90
PCIU3811254 燃油附加费 4636.80 0.06 262.46 4636.80 262.46
PCIU3811254 佣金 -243.22 0.06 -13.77 -243.22 -13.77
PCIU3811254 安全附加费 64.80 0.06 3.67 64.80 3.67我想要得到如下的表格:
g_no g_name amt_tax rate tax disc_amttax disc_tax
PCIU3811254 936.00 0.06 52.98 0 0
PCIU3811254 运费 8107.20 0.06 458.90 243.22 13.77
PCIU3811254 燃油附加费 4636.80 0.06 262.46 0 0
PCIU3811254 安全附加费 64.80 0.06 3.67 0 0说明:佣金在折扣列(disc_amttax / disc_tax),运费行create table #t(g_no char(30),g_name char(30),amt_tax numeric(13,2),rate numeric(13,2),tax numeric(13,2),disc_amttax numeric(13,2),disc_tax numeric(13,2))insert into #t values('PCIU3811254','',936.00, 0.06,52.98,936,52.98)
insert into #t values('PCIU3811254','运费',8107.2, 0.06, 458.9, 8107.2, 458.9)
insert into #t values('PCIU3811254','燃油附加费',4636.8, 0.06,262.46,4636.8,262.46)
insert into #t values('PCIU3811254','佣金',-243.22, 0.06, -13.77, -243.22, -13.77)
insert into #t values('PCIU3811254','安全附加费',64.8, 0.06, 3.67, 64.8, 3.67)
from #t a inner join #s b
on a.g_no=b.g_no and a.g_name='运费'delete from #s where g_name='佣金'只能用这么老土的办法了,还有什么好的方式么?
update a set
a.disc_amttax=b.amt_tax,
a.disc_tax=b.tax
from #t a
inner join #t b
on a.g_no=b.g_no
and a.g_name='运费'
and b.g_name='佣金'delete from #t where g_name='佣金'