SQL2005多条查询子句插入新表??江湖救急！！！

[zhqiao0729]

三个表YSXS1，YSXS2，CTXS
需要向CTXS表插入ctid,time,yye,kds其中
ctid,time,为固定值，yye,kds都是查询出来的，
求问插入语句怎么写
两个子查询如下：
yye=
select sum(cpje) from YSXS1 where
ctid='N001'and time='201401',
kds=
select kdsl from YSXS2 where
ctid='N001'and time='201401';

[發糞塗牆]

insert into ctxs(ctid,time,yye,kds) select '固定值' as ctid, ' 固定值' as time,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401') as yye,(select kdsl from YSXS2 where ctid='N001'and time='201401) as kds 前提是子查询只返回一个值，如果多个，要另外处理

[zhqiao0729]

引用 楼主 zhqiao0729 的回复:

三个表YSXS1，YSXS2，CTXS 需要向CTXS表插入ctid,time,yye,kds其中 ctid,time,为固定值，yye,kds都是查询出来的， 求问插入语句怎么写 两个子查询如下： yye= select sum(cpje) from YSXS1 where ctid='N001'and time='201401', kds= select kdsl from YSXS2 where ctid='N001'and time='201401';

谢了！

[zhqiao0729]

引用 1 楼 DBA_Huangzj 的回复:

insert into ctxs(ctid,time,yye,kds) select '固定值' as ctid, ' 固定值' as time,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401') as yye,(select kdsl from YSXS2 where ctid='N001'and time='201401) as kds 前提是子查询只返回一个值，如果多个，要另外处理

再请教一个问题，如果我要插入新表的数据，用到了新表新插入的数据，那怎么插入呢? 比如往CTXS里插入kdj,kdj=yye/kds

[發糞塗牆]

insert into ctxs(ctid,time,yye,kds,kdj) select '固定值' as ctid, ' 固定值' as time,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401') as yye,(select kdsl from YSXS2 where ctid='N001'and time='201401) as kds,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401')/(select kdsl from YSXS2 where ctid='N001'and time='201401) as kdj 如果得出的数据是整型，可能会导致截断小数点，如果是要保留小数点，可以用： insert into ctxs(ctid,time,yye,kds,kdj) select '固定值' as ctid, ' 固定值' as time,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401') as yye,(select kdsl from YSXS2 where ctid='N001'and time='201401) as kds,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401')*1.0/(select kdsl from YSXS2 where ctid='N001'and time='201401) as kdj

[zhqiao0729]

引用 4 楼 DBA_Huangzj 的回复:

insert into ctxs(ctid,time,yye,kds,kdj) select '固定值' as ctid, ' 固定值' as time,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401') as yye,(select kdsl from YSXS2 where ctid='N001'and time='201401) as kds,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401')/(select kdsl from YSXS2 where ctid='N001'and time='201401) as kdj 如果得出的数据是整型，可能会导致截断小数点，如果是要保留小数点，可以用： insert into ctxs(ctid,time,yye,kds,kdj) select '固定值' as ctid, ' 固定值' as time,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401') as yye,(select kdsl from YSXS2 where ctid='N001'and time='201401) as kds,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401')*1.0/(select kdsl from YSXS2 where ctid='N001'and time='201401) as kdj

试了一下，后面小数没有呢

[發糞塗牆]

select '固定值' as ctid, ' 固定值' as time,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401') as yye,(select kdsl from YSXS2 where ctid='N001'and time='201401) as kds,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401')*1.0/(select kdsl from YSXS2 where ctid='N001'and time='201401) as kdj 贴结果出来看看

[zhqiao0729]

引用 6 楼 DBA_Huangzj 的回复:

select '固定值' as ctid, ' 固定值' as time,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401') as yye,(select kdsl from YSXS2 where ctid='N001'and time='201401) as kds,(select sum(cpje) from YSXS1 where ctid='N001'and time='201401')*1.0/(select kdsl from YSXS2 where ctid='N001'and time='201401) as kdj 贴结果出来看看

找到原因了，是我前面的字段（numeric，0）的原因，现在可以了。太感谢了！！

[發糞塗牆]

找到就好

[zhqiao0729]

引用 8 楼 DBA_Huangzj 的回复:

找到就好

请问大神asp.net可熟悉？asp.net中怎么操作上面的语句呢？即对数据库操作

[發糞塗牆]

不熟。。。。。