select carid,InventoryName,MeasureDate,mz,pz,mz-pz as jz,MeasureOperator from
(
select CarID,InventoryName,MeasureOperator,
(case when Measurenum1>Measurenum2 then Measurenum1 else Measurenum2 end) as mz,
(case when Measurenum1>Measurenum2 then Measurenum2 else Measurenum1 end) as pz,
MeasureDate
from
(
select CarID,InventoryName,isnull(Measurenum,0) as Measurenum1,
(select isnull(Measurenum,0) from Kxd_SimpleMeasure where MesaureNo=a.RelationMesaureNo) as Measurenum2,
MeasureDate,RelationMesaureNo,MeasureOperator from Kxd_SimpleMeasure a where RelationMesaureNo <>''
)b)c
谁能帮我讲解下 整个过程?
(
select CarID,InventoryName,MeasureOperator,
(case when Measurenum1>Measurenum2 then Measurenum1 else Measurenum2 end) as mz,
(case when Measurenum1>Measurenum2 then Measurenum2 else Measurenum1 end) as pz,
MeasureDate
from
(
select CarID,InventoryName,isnull(Measurenum,0) as Measurenum1,
(select isnull(Measurenum,0) from Kxd_SimpleMeasure where MesaureNo=a.RelationMesaureNo) as Measurenum2,
MeasureDate,RelationMesaureNo,MeasureOperator from Kxd_SimpleMeasure a where RelationMesaureNo <>''
)b)c
谁能帮我讲解下 整个过程?
--子查询从最里面那层看起,容易理解些。比如:
---第一步,通过下面的语句查询出来一个结果集:
select CarID,InventoryName,isnull(Measurenum,0) as Measurenum1,
(select isnull(Measurenum,0) from Kxd_SimpleMeasure where MesaureNo=a.RelationMesaureNo) as Measurenum2,
MeasureDate,RelationMesaureNo,MeasureOperator from Kxd_SimpleMeasure a where RelationMesaureNo <>''--第二部:然后把第一步查询得到的结果集当成一个表再次进行查询
select CarID,InventoryName,MeasureOperator,
(case when Measurenum1>Measurenum2 then Measurenum1 else Measurenum2 end) as mz,
(case when Measurenum1>Measurenum2 then Measurenum2 else Measurenum1 end) as pz,
MeasureDate
from
(
--这里就放第一步的语句
)b --注意这里一定要有个别名,随便什么都可以,符合命名规则就行。一般就是a,b,c啥的。可以看成是给括号里面的结果集取个暂时性的表名--第三部,跟第二部一样:
select carid,InventoryName,MeasureDate,mz,pz,mz-pz as jz,MeasureOperator from
(
--这里放第二步的语句
)c