MYSQL语句问题？

Question

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该如何解决（别名问题）？

Answer 1

说话。你WE.EditionDotID=WE.EditionDotID为何意?

Answer 2

给里面的查询结果加个别名。。set后面的字段加上别名就ＯＫ了。

Answer 3

楼主逻辑没有搞清 别名加个AS试试    WE.EditionDotID=WE.EditionDotID不知道想要表达什么意思

Answer 4

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$$贴出代码参考下，不知道对不对

Answer 5

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 这里的条件判断大家是否有更好的优化方法呢？

Answer 6

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
红色的部分就是一个派生表，需要有别名
被修改的是查询结果，你这样能修改成功吗？