52280 1线 2012-03-24 13:50:35 C20河砂非泵 9 宁波利星汽车服务有限公司AH600项目
52280 1线 2012-03-24 13:52:16 C20河砂非泵 9 宁波利星汽车服务有限公司AH600项目这里的9代表方量,两个52280代表盘数。我现在要把9计算成每盘有4.5,这个如何计算 就是方量/盘数 9/2
52280 1线 2012-03-24 13:52:16 C20河砂非泵 9 宁波利星汽车服务有限公司AH600项目这里的9代表方量,两个52280代表盘数。我现在要把9计算成每盘有4.5,这个如何计算 就是方量/盘数 9/2
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
'',ProdMasterData.project1,'',''
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
order by ConsumpMaterial.prodno desc这个语句查询出来的是上面两条,现在我想让9/2,就是ProdMasterData.volume /盘数,这个盘数是根据52280 1线 2012-03-24 13:50:35 C20河砂非泵 9 宁波利星汽车服务有限公司AH600项目
52280 1线 2012-03-24 13:52:16 C20河砂非泵 9 宁波利星汽车服务有限公司AH600项目
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
'',ProdMasterData.project1,'',''
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) t
order by prodno desc
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
'',ProdMasterData.project1,'',''
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) t,
( select count(1) from
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
'',ProdMasterData.project1,'',''
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) n
) m
order by prodno desc
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
'' c1,ProdMasterData.project1,'' c2,'' c3
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) t,
( select count(1) from
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
ProdMasterData.project1
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) n
) m
order by prodno desc
没有为 'm' 的列 1 指定任何列名称。
消息 207,级别 16,状态 1,第 1 行
列名 'cnt' 无效。刚查了下,得出如下结论
引用 2 楼 dawugui 的回复:
select t.* , cast(volume*1.0/2 as decimal(18,2)) from
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterDa……
SQL codeselect t.* , cast(t.volume*1.0/m.count(1) as decimal(18,2)) from
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
'' c1,ProdMasterData.project1,'' c2,'' c3
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) t,
( select count(1) from
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
ProdMasterData.project1
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) n
) m
没有为'M'的列1指定任何列名称
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
'' c1,ProdMasterData.project1,'' c2,'' c3
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) t,
( select count(1) cnt from
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
ProdMasterData.project1
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) n
) m
order by prodno desc
select count(1) cnt from
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
ProdMasterData.project1
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) n
然后运行:
select t.* , cast(t.volume*1.0 / m.cnt as decimal(18,2)) from
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
'' c1,ProdMasterData.project1,'' c2,'' c3
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) t,
(
select count(1) cnt from
(
select distinct ConsumpMaterial.batchno,ConsumpMaterial.prodno,ProdMasterData.Pl,ConsumpMaterial.batchtime,
ProdMasterData.Mixcgrade,ProdMasterData.volume ,ProdMasterData.sname,
ProdMasterData.project1
from ProdMasterData JOIN ConsumpMaterial
ON ProdMasterData.prodno=ConsumpMaterial.prodno
) n
) m
order by prodno desc
52280 1线 2012-03-24 13:52:16 C20河砂非泵 9 宁波利星汽车服务有限公司AH600项目52281 1线 2012-03-24 13:50:33 C20河砂 8 宁波利星汽车服务有限公司AH603项目
52281 1线 2012-03-24 13:52:13 C20河砂 8 宁波利星汽车服务有限公司AH603项目52282 1线 2012-03-24 13:50:35 C30 1 宁波太阳集团 这里有很多的,我要让他变成52280 1线 2012-03-24 13:50:35 C20河砂非泵 4.5 宁波利星汽车服务有限公司AH600项目
52280 1线 2012-03-24 13:52:16 C20河砂非泵 4.5 宁波利星汽车服务有限公司AH600项目52281 1线 2012-03-24 13:50:33 C20河砂 4 宁波利星汽车服务有限公司AH603项目
52281 1线 2012-03-24 13:52:13 C20河砂 4 宁波利星汽车服务有限公司AH603项目52282 1线 2012-03-24 13:50:35 C30 1 宁波太阳集团 就是9/2 或者8/2,或者1/1