试试:另外不要用#这种符号做列名SELECT c.sx# ,
c.tm ,
c.na ,
c.dw ,
a.psl AS 上期机量 ,
a.dsl AS 上期盘量 ,
a.dsl - a.psl AS 上期盈亏量 ,
CAST(( a.dsl - a.psl ) * a.pjj AS NUMERIC(8, 2)) AS 上期盈亏进额 ,
b.psl AS 这期机量 ,
b.dsl AS 这期盘量 ,
b.dsl - b.psl AS 这期盈亏量 ,
CAST(( b.dsl - b.psl ) * b.pjj AS NUMERIC(8, 2)) AS 这期盈亏进额
FROM jmwd.pos.dbo.jps b
LEFT JOIN jmwd.pos.dbo.jspt c ON b.ps# = c.sx#
LEFT JOIN jmwd.pos.dbo.jps a ON b.ps# = a.ps#
WHERE a.d# = 42
AND b.d# = 43
AND ( b.psl - b.dsl ) * b.pjj > 50
c.tm ,
c.na ,
c.dw ,
a.psl AS 上期机量 ,
a.dsl AS 上期盘量 ,
a.dsl - a.psl AS 上期盈亏量 ,
CAST(( a.dsl - a.psl ) * a.pjj AS NUMERIC(8, 2)) AS 上期盈亏进额 ,
b.psl AS 这期机量 ,
b.dsl AS 这期盘量 ,
b.dsl - b.psl AS 这期盈亏量 ,
CAST(( b.dsl - b.psl ) * b.pjj AS NUMERIC(8, 2)) AS 这期盈亏进额
FROM jmwd.pos.dbo.jps b
LEFT JOIN jmwd.pos.dbo.jspt c ON b.ps# = c.sx#
LEFT JOIN jmwd.pos.dbo.jps a ON b.ps# = a.ps#
WHERE a.d# = 42
AND b.d# = 43
AND ( b.psl - b.dsl ) * b.pjj > 50
c.tm ,
c.na ,
c.dw ,
a.psl AS 上期机量 ,
a.dsl AS 上期盘量 ,
a.dsl - a.psl AS 上期盈亏量 ,
CAST(( a.dsl - a.psl ) * a.pjj AS NUMERIC(8, 2)) AS 上期盈亏进额 ,
b.psl AS 这期机量 ,
b.dsl AS 这期盘量 ,
b.dsl - b.psl AS 这期盈亏量 ,
CAST(( b.dsl - b.psl ) * b.pjj AS NUMERIC(8, 2)) AS 这期盈亏进额
FROM jmwd.pos.dbo.jps b
full JOIN jmwd.pos.dbo.jspt c ON b.ps# = c.sx#
full JOIN jmwd.pos.dbo.jps a ON b.ps# = a.ps#
WHERE a.d# = 42
AND b.d# = 43
AND ( b.psl - b.dsl ) * b.pjj > 50
cast((a.dsl-a.psl)*a.pjj as numeric(8,2)) as 上期盈亏进额
, b.psl as 这期机量,b.dsl as 这期盘量, b.dsl-b.psl as 这期盈亏量,
cast((b.dsl-b.psl)*b.pjj as numeric(8,2)) as 这期盈亏进额
from jmwd.pos.dbo.jps b
left join jmwd.pos.dbo.jps a on b.ps#=a.ps# and a.d#=42 and b.d#=43
inner join jmwd.pos.dbo.jspt c on b.ps#=c.sx#
where (b.psl-b.dsl)*b.pjj>50