------------------------------------------------------------------------------
现有一表(A)结构如下:
bmid type 2005 2006 2007
----------------------------------------------
1 inMoney 30 31 32
1 outMoney 40 41 42
1 saveMoney 50 51 52 存在另一表(B),现在需要把A表中的数据插入到B表对应的列中;如果A表中不存在对应列的数据,则添0补上; bmid type 2003 2004 2005 2006 2007 2008 2009
----------------------------------------------------------------
1 inMoney 0 0 30 31 32 0 0
1 outMoney 0 0 40 41 42 0 0
1 saveMoney 0 0 50 51 52 0 0 谢谢大家,在线等;希望大家帮帮忙;保证结贴...
现有一表(A)结构如下:
bmid type 2005 2006 2007
----------------------------------------------
1 inMoney 30 31 32
1 outMoney 40 41 42
1 saveMoney 50 51 52 存在另一表(B),现在需要把A表中的数据插入到B表对应的列中;如果A表中不存在对应列的数据,则添0补上; bmid type 2003 2004 2005 2006 2007 2008 2009
----------------------------------------------------------------
1 inMoney 0 0 30 31 32 0 0
1 outMoney 0 0 40 41 42 0 0
1 saveMoney 0 0 50 51 52 0 0 谢谢大家,在线等;希望大家帮帮忙;保证结贴...
select bmid, [type], 0, 0, [2005], [2006], [2007], 0, 0 from A
select * ,0,0 from a
select bmid, [type], 0, 0, isnull([2005],0),isnull([2006],0),isnull([2007],0), 0, 0 from A
select bmid, [type], 0, 0, [2005], [2006], [2007], 0, 0 from A
这样是确定我本次查询出有2005,2006,2007这3个字段;假如我A表的字段(2005,2006,2007这样的年度字段)不确定呢?
比如我第一次查询出结果为
bmid type 2005
----------------------------------
1 inMoney 30
1 outMoney 40
1 saveMoney 50 第二次查询的结果可能为:
bmid type 2005 2007
----------------------------------
1 inMoney 30 32
1 outMoney 40 42
1 saveMoney 50 52对于这种情况,该如何处理,以便可以灵活地插入到B表;
insert myTest select 1,2005,30,40,50
union select 1,2006,31,41,51
union select 1,2007,32,42,52 select classid , type ,
sum(case nd when 2003 then val else 0 end) [2003],
sum(case nd when 2004 then val else 0 end) [2004],
sum(case nd when 2005 then val else 0 end) [2005],
sum(case nd when 2006 then val else 0 end) [2006],
sum(case nd when 2007 then val else 0 end) [2007],
sum(case nd when 2008 then val else 0 end) [2008],
sum(case nd when 2009 then val else 0 end) [2009],
sum(case nd when 2010 then val else 0 end) [2010],
sum(case nd when 2011 then val else 0 end) [2011],
sum(case nd when 2012 then val else 0 end) [2012]
from
(
select classid , type = 'inmoney' , nd , val = inmoney from mytest
union all
select classid , type = 'outMoney' , nd , val = outMoney from mytest
union all
select classid , type = 'saveMoney' , nd , val = saveMoney from mytest
) t
group by classid , typedrop table mytest
/*
classid type 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012
----------- --------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 inmoney 0 0 30 31 32 0 0 0 0 0
1 outMoney 0 0 40 41 42 0 0 0 0 0
1 saveMoney 0 0 50 51 52 0 0 0 0 0(所影响的行数为 3 行)
*/