MYSQL语句问题？

码无边 2011-12-15 01:43:59

UPDATE 

(

SELECT  WorkID,

WK.PpjID,

WD.CheckupPoint,

WD.AdjustPoint,

WD.FactPoint,

 WE.IsSpecial,

 WK.WorkType,

 WK.WorkStatus 

  FROM `Work` WK  LEFT JOIN WorkDesign WD ON WK.WorkID=WD.Work_id

LEFT JOIN WorkEditionDot WE ON WE.EditionDotID=WE.EditionDotID WHERE `WK`.WorkID IN

(SELECT `W`.WorkID   FROM `Work` W WHERE  W.PpjID=18035 AND (`W`.WorkStatus='已出稿' OR `W`.WorkStatus='已定稿')))   

 SET  WD.CheckupPoint=WD.AdjustPoint+WD.FactPoint WHERE  WK.PpjID=18035

报错；Every derived table must have its own alias该如何解决（别名问题）？

...全文

153 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

lt1299416898 2011-12-16

打赏
举报

统一6楼的说话

码无边 2011-12-16

打赏
举报

[Quote=引用 8 楼 levinstong 的回复:]

楼主逻辑没有搞清别名加个AS试试 WE.EditionDotID=WE.EditionDotID不知道想要表达什么意思
[/Quote]不好意思，写错啦

xuzuning 2011-12-16

打赏
举报

Every derived table must have its own alias
每一个派生表必须有自己的别名

UPDATE
(
SELECT WorkID,
WK.PpjID,
WD.CheckupPoint,
WD.AdjustPoint,
WD.FactPoint,
WE.IsSpecial,
WK.WorkType,
WK.WorkStatus
FROM `Work` WK LEFT JOIN WorkDesign WD ON WK.WorkID=WD.Work_id
LEFT JOIN WorkEditionDot WE ON WE.EditionDotID=WE.EditionDotID WHERE `WK`.WorkID IN
(SELECT `W`.WorkID FROM `Work` W WHERE W.PpjID=18035 AND (`W`.WorkStatus='已出稿' OR `W`.WorkStatus='已定稿')))
SET WD.CheckupPoint=WD.AdjustPoint+WD.FactPoint WHERE WK.PpjID=18035
红色的部分就是一个派生表，需要有别名
被修改的是查询结果，你这样能修改成功吗？

码无边 2011-12-16

打赏
举报

SET WorkDesign.CheckupPoint = CASE   

 -- 特版

     WHEN((WorkEditionDot.IsSpecial=0 AND Work.IsWorkMakeUp=1) AND ((Work.WorkType='修改')OR (Work.WorkType='新稿')

     OR (Work.WorkType='改稿') OR (Work.WorkType='转稿') OR (Work.WorkType='重新设计'))) THEN

       WorkDesign.FactPoint+WorkDesign.AdjustPoint

       

     WHEN(Work.WorkType='修改'AND WorkEditionDot.IsSpecial=0 AND Work.IsWorkMakeUp=1) THEN

       1+WorkDesign.AdjustPoint

 -- 非特版  

     WHEN(Work.IsWorkMakeUp=0 AND (((Work.WorkType='修改'OR Work.WorkType='新稿') AND (WorkEditionDot.IsSpecial=1))

     OR (Work.WorkType='改稿')OR (Work.WorkType='转稿') OR (Work.WorkType='重新设计'))) THEN  

       WorkDesign.AdjustPoint+WorkDesign.FactPoint/2

       

    WHEN((Work.WorkType='新稿'OR Work.WorkType='修改')AND WorkEditionDot.IsSpecial=0 AND Work.IsWorkMakeUp=0) THEN

       WorkDesign.AdjustPoint+WorkDesign.FactPoint

  END

这里的条件判断大家是否有更好的优化方法呢？

码无边 2011-12-16

打赏
举报

CREATE DEFINER=`fxzycrmdb`@`%` PROCEDURE `UPDATE_WorkPoint`(

      M_PPJID   INT(11) -- 书刊编号

)

BEGIN

   

 UPDATE `Work` LEFT JOIN WorkDesign ON Work.WorkID=WorkDesign.Work_id LEFT JOIN

  WorkEditionDot  ON `Work`.EditionDotID=WorkEditionDot.EditionDotID

 SET WorkDesign.CheckupPoint = CASE   

 -- 特版

     WHEN((WorkEditionDot.IsSpecial=0 AND Work.IsWorkMakeUp=1) AND ((Work.WorkType='修改')OR (Work.WorkType='新稿')

     OR (Work.WorkType='改稿') OR (Work.WorkType='转稿') OR (Work.WorkType='重新设计'))) THEN

       WorkDesign.FactPoint+WorkDesign.AdjustPoint

       

     WHEN(Work.WorkType='修改'AND WorkEditionDot.IsSpecial=0 AND Work.IsWorkMakeUp=1) THEN

       1+WorkDesign.AdjustPoint

 -- 非特版  

     WHEN(Work.IsWorkMakeUp=0 AND (((Work.WorkType='修改'OR Work.WorkType='新稿') AND (WorkEditionDot.IsSpecial=1))

     OR (Work.WorkType='改稿')OR (Work.WorkType='转稿') OR (Work.WorkType='重新设计'))) THEN  

       WorkDesign.AdjustPoint+WorkDesign.FactPoint/2

       

    WHEN((Work.WorkType='新稿'OR Work.WorkType='修改')AND WorkEditionDot.IsSpecial=0 AND Work.IsWorkMakeUp=0) THEN

       WorkDesign.AdjustPoint+WorkDesign.FactPoint

  END 

 

 WHERE  `Work`.PpjID=M_PPJID AND WorkDesign.IsCancel=0 AND (`Work`.WorkStatus='已出稿' OR `Work`.WorkStatus='已定稿');

    END$$

贴出代码参考下，不知道对不对