access里面的sql语句转换sqlserver的sql语句问题 (急，急，急，)难，难，难，(10分)

zyt_1978 · 2006-10-20

select M.*,        (select HH from CGDD where CGDD.ZBID=M.ZBID and CGDD.HH=M.HH) as TMP,        (select sum(SL) from CGSH where DDID=M.ZBID and HPID=M.HPID) as aSHSL,        case  M.SL > aSHSL when 1 then M.SL - aSHSL else 0 end as aMSSL,        --iif(M.SL > aSHSL, M.SL - aSHSL, 0) as aMSSL,        H.BH as aBH, H.PM as aPM, H.DW as aDW        from CGDD M  inner join HP H on M.HPID=H.ID  里面的iif已经让我屏蔽了 但是   第 4 行: '>' 附近有语法错误。 后面还提示  列名 'aSHSL' 无效。

由由(love) · 2006-10-20

Access里好像不支持CASE

xinxinhg · 2006-10-20

select M.*,        (select HH from CGDD where CGDD.ZBID=M.ZBID and CGDD.HH=M.HH) as TMP,        (select sum(SL) SL from CGSH where DDID=M.ZBID and HPID=M.HPID) as aSHSL,        case  M.SL > aSHSL.SL when 1 then M.SL - aSHSL.SL else 0 end as aMSSL,        --iif(M.SL > aSHSL, M.SL - aSHSL, 0) as aMSSL,        H.BH as aBH, H.PM as aPM, H.DW as aDW        from CGDD M  inner join HP H on M.HPID=H.ID 你的aSHSL相当于一个临时表（只是这么打比方，实际不是）了，做比较是要指出这个临时表的字段。。而不是把这个临时表做为字段。。

zyt_1978 · 2006-10-20

还是出错 列前缀 'aSHSL' 与查询中所用的表名或别名不匹配。 aSHSL看来不算是临时表呀

xinxinhg · 2006-10-20

对不起是我理解错了，你可以试试再套一层查询。 Select K.*,case  K.SL > aSHSL when 1 then K.SL - K.aSHSL else 0 end as K.aMSSL, ( select M.*,        (select HH from CGDD where CGDD.ZBID=M.ZBID and CGDD.HH=M.HH) as TMP,        (select sum(SL) from CGSH where DDID=M.ZBID and HPID=M.HPID) as aSHSL,        H.BH as aBH, H.PM as aPM, H.DW as aDW        from CGDD M  inner join HP H on M.HPID=H.ID &nbsp

K 如果还不行可以加我QQ120437873。

xinxinhg · 2006-10-20

写错了。。 Select K.*,   case  K.SL > aSHSL when 1 then K.SL - K.aSHSL else 0 end as K.aMSSL from ( select M.*,        (select HH from CGDD where CGDD.ZBID=M.ZBID and CGDD.HH=M.HH) as TMP,        (select sum(SL) from CGSH where DDID=M.ZBID and HPID=M.HPID) as aSHSL,        H.BH as aBH, H.PM as aPM, H.DW as aDW        from CGDD M  inner join HP H on M.HPID=H.ID &nbsp

K

zyt_1978 · 2006-10-20

原来的那个语句是用到access中的，没有问题  换到sqlserver真是差别不小呀，改动量也很大。 select M.*, (select HH from CGDD where CGDD.ZBID=M.ZBID and CGDD.HH=M.HH) as TMP, (select sum(SL) from CGSH where DDID=M.ZBID and HPID=M.HPID) as aSHSL, --case when (M.SL > aSHSL)  then M.SL - aSHSL else 0 end as aMSSL, H.BH as aBH, H.PM as aPM, H.DW as aDW from CGDD M   inner join HP H on M.HPID=H.ID where ZBID = 0 关键是case那行我屏蔽掉就没有问题了，看来aSHSL不能在别处使用，access却可以

xinxinhg · 2006-10-20

我机器没装SqlServer所以没法测试。。。在Orocle的环境下测试了一下。。。是会无法识别aSHSL。。。。不过再套一层查询就没问题了。。Case还是可以用的。。

zyt_1978 · 2006-10-21

(select sum(SL) from CGSH where DDID=M.ZBID and HPID=M.HPID) 替换aSHSL 就可以了，不过这样就太长了，太乱了。

xinxinhg · 2006-10-21

这样写就可以了 Select K.*,   case  K.SL > aSHSL when 1 then K.SL - K.aSHSL else 0 end as K.aMSSL from ( select M.*,        (select HH from CGDD where CGDD.ZBID=M.ZBID and CGDD.HH=M.HH) as TMP,        (select sum(SL) from CGSH where DDID=M.ZBID and HPID=M.HPID) as aSHSL,        H.BH as aBH, H.PM as aPM, H.DW as aDW        from CGDD M  inner join HP H on M.HPID=H.ID &nbsp

K

zyt_1978 · 2006-10-22

功夫不负有心人，终于在绝望的时候，解决了。   呵呵。sql语句的使用技术提高了一大截。

大器晚成 · 2006-10-23

把第四句改一下: case  M.SL-aSHSL when 1 then M.SL - aSHSL else 0 end as aMSSL,就可以了

access里面的sql语句转换sqlserver的sql语句问题 (急，急，急，)难，难，难，(10分)

zyt_1978

Unregistered / Unconfirmed

由由(love)

Unregistered / Unconfirmed

xinxinhg

Unregistered / Unconfirmed

zyt_1978

Unregistered / Unconfirmed

xinxinhg

Unregistered / Unconfirmed

xinxinhg

Unregistered / Unconfirmed

zyt_1978

Unregistered / Unconfirmed

xinxinhg

Unregistered / Unconfirmed

zyt_1978

Unregistered / Unconfirmed

xinxinhg

Unregistered / Unconfirmed

zyt_1978

Unregistered / Unconfirmed

大器晚成

Unregistered / Unconfirmed

Similar threads