表fitem结构及数据如下:
citem_class,citem_name,ctable
"99","成本对象","fitemss99"
"00","在建工程","fitemss00"
.....
其中,citem_class为项目大类编号,ctable为项目表的名称(不固定),例如citem_class为‘99’的大类在fitemss99中存储数据如下:
citemcode,citemname
"01","磷酸二钠片"
"02","盐酸钠片"
"03","复方丹参注射液"
.....
想实现如下效果:
citem_class,citem_name,citemcode,citemname
"99","成本对象","01","磷酸二钠片"
"99","成本对象","02","盐酸钠片"
"99","成本对象","03","复方丹参注射液"
"00","在建工程","01",….
……
SQL语句如何写?
citem_class,citem_name,ctable
"99","成本对象","fitemss99"
"00","在建工程","fitemss00"
.....
其中,citem_class为项目大类编号,ctable为项目表的名称(不固定),例如citem_class为‘99’的大类在fitemss99中存储数据如下:
citemcode,citemname
"01","磷酸二钠片"
"02","盐酸钠片"
"03","复方丹参注射液"
.....
想实现如下效果:
citem_class,citem_name,citemcode,citemname
"99","成本对象","01","磷酸二钠片"
"99","成本对象","02","盐酸钠片"
"99","成本对象","03","复方丹参注射液"
"00","在建工程","01",….
……
SQL语句如何写?
declare @a table (citem_class varchar(4),citem_name varchar(10),ctable varchar(11))
insert into @a
select '"99"','"成本对象"','"fitemss99"' union all
select '"00"','"在建工程"','"fitemss00"'declare @b table (citemcode varchar(4),citemname varchar(16))
insert into @b
select '"01"','"磷酸二钠片"' union all
select '"02"','"盐酸钠片"' union all
select '"03"','"复方丹参注射液"'select * from @a CROSS JOIN @b ORDER BY 1 DESC
/*
citem_class citem_name ctable citemcode citemname
----------- ---------- ----------- --------- ----------------
"99" "成本对象" "fitemss99" "01" "磷酸二钠片"
"99" "成本对象" "fitemss99" "02" "盐酸钠片"
"99" "成本对象" "fitemss99" "03" "复方丹参注射液"
"00" "在建工程" "fitemss00" "01" "磷酸二钠片"
"00" "在建工程" "fitemss00" "02" "盐酸钠片"
"00" "在建工程" "fitemss00" "03" "复方丹参注射液"
*/
虽然在程序代码中可以遍历fitem表读取对应的项目表名称再检索项目信息,但我想知道单纯利用SQL语句能否实现?
“成本对象”的项目信息保存到fitemss99表中(需要从中检索citemcode,citemname两个字段),“在建工程”的项目信息保存到fitemss00表中。