mysql - 存储过程无法运行

Question

我正在尝试制作一个存储过程，用于计算黄金、白银、铂金等排名系统上的业务点。我制作了这个程序，但它给出了我的错误，无法找出问题所在。它说检查查询上的 mysql 语法，但是当我单独运行查询时，它们运行得很好。任何人都可以帮我弄清楚问题是什么，我做错了什么吗？我是程序新手。这应该是从前端完成的，但现在它应该是从后端数据库完成的。请大家帮忙指点一下

 DELIMITER //
 CREATE procedure weightedavg(IN  businessid INT(11))
 BEGIN
 SET @basic = 1;
 SET @silver = 2;
 SET @gold = 4;
 SET @platinum = 8;
 SET @diamond = 16;

 select @badgegold:=@badgegold user_badge_id from user_badge_masters where        
 user_badge_desc="Gold";
 select @badgePlatinum:=@badgePlatinum user_badge_id from user_badge_masters where      
 user_badge_desc="Platinum";
 select @badgeSilver:=@badgeSilver user_badge_id from user_badge_masters where    
 user_badge_desc="Silver";
 select @badgeDiamond:=@badgeDiamond user_badge_id from user_badge_masters where     
 user_badge_desc="Diamond";
 select @badgeBasic:=@badgeBasic user_badge_id from user_badge_masters where    
 user_badge_desc="Basic";



  SET @sum_valg= select SUM(review_trans.rating_given) as sum
        from `review_trans` where  `review_trans`.`business_id`=businessid  and      
  `review_trans`.`user_badge_id`=@badgegold
        GROUP BY `review_trans`.`user_badge_id`;




  SET @sum_valp= select SUM(review_trans.rating_given) as sum
        from `review_trans` where  `review_trans`.`business_id`=businessid  and    
  `review_trans`.`user_badge_id`=@badgePlatinum
        GROUP BY `review_trans`.`user_badge_id`;

        SET @sum_vals= select SUM(review_trans.rating_given) as sum
        from `review_trans` where  `review_trans`.`business_id`=businessid  and 
  `review_trans`.`user_badge_id`=@badgeSilver
        GROUP BY `review_trans`.`user_badge_id`;

       SET @sum_vald= select SUM(review_trans.rating_given) as sum
        from `review_trans` where  `review_trans`.`business_id`=businessid  and 
   `review_trans`.`user_badge_id`=@badgeDiamond
        GROUP BY `review_trans`.`user_badge_id`;

       SET @sum_valb= select SUM(review_trans.rating_given) as sum
        from `review_trans` where  `review_trans`.`business_id`=businessid  and 
    `review_trans`.`user_badge_id`=@badgeBasic
        GROUP BY `review_trans`.`user_badge_id`;


        SET @sum_valg_count=select count(user_id) from review_trans where   
   user_badge_id=1;  
         SET @sum_vals_count=select count(user_id) from review_trans where 
   user_badge_id=4;  
          SET @sum_valp_count=select count(user_id) from review_trans where 
   user_badge_id=3;  
           SET @sum_valb_count=select count(user_id) from review_trans where 
   user_badge_id=6;  
            SET @sum_vald_count=select count(user_id) from review_trans where 
   user_badge_id=5;





        IF @sum_valg=0 THEN
        set @gold_avg=0
        else
        set @gold_avg=@sum_valg/@sum_valg_count;


          IF @sum_valp=0 THEN
        set @platinum_avg=0
        else
        set @platinum_avg=@sum_valp/@sum_valp_count;



          IF @sum_vals=0 THEN
        set @silver_avg=0
        else
        set @silver_avg=@sum_vals/@sum_vals_count;

        IF @sum_vald=0 THEN
        set @diamond_avg=0
        else
        set @diamond_avg=@sum_vald/@sum_vald_count;

        IF @sum_valb=0 THEN
        set @basic_avg=0
        else
        set @basic_avg=@sum_valb/@sum_valb_count;


        IF @gold_avg <=0 THEN
        set @gold_effectiveweight=0
        else
        set @gold_effectiveweight=@basic;

        IF @platinum_avg <=0 THEN
        set @platinum_effectiveweight=0
        else
        set @platinum_effectiveweight=@platinum;


         IF @silver_avg <=0 THEN
        set @silver_effectiveweight=0
        else
        set @silver_effectiveweight=@silver;


         IF @diamond_avg <=0 THEN
        set @diamond_effectiveweight=0
        else
        set @diamond_effectiveweight=@diamond;


        IF @basic_avg <=0 THEN
        set @basic_effectiveweight=0
        else
        set @basic_effectiveweight=@basic;


        set @total_weight=@gold_effectiveweight+@platinum_effectiveweight+
        @silver_effectiveweight+@diamond_effectiveweight+@basic_effectiveweight;

     set @basic_weighted_score=@basic_effectiveweight/@total_weight;
     set @silver_weighted_score=@silver_effectiveweight/@total_weight;
     set @gold_weighted_score=@gold_effectiveweight/@total_weight;
     set @plat_weighted_score=@platinum_effectiveweight/@total_weight;
     set @diam_weighted_score=@diamond_effectiveweight/@total_weight;


      set @avg_basic_weighted_score=@basic_weighted_score*@basic_avg;
      set @avg_silver_weighted_score=@silver_weighted_score*@silver_avg;
      set @avg_gold_weighted_score=@gold_weighted_score*@gold_avg;
      set @avg_plat_weighted_score=@plat_weighted_score*@platinum_avg;
      set @avg_diam_weighted_score=@diam_weighted_score*@diamond_avg;

          @business_point=@avg_basic_weighted_score+ @avg_silver_weighted_score+
      @avg_gold_weighted_score+@avg_plat_weighted_score+@avg_diam_weighted_score;

          @business_point=TRUNCATE(@business_point,1);
          UPDATE businessmasters SET business_point=@business_point WHERE    
          business_id=businessid;


  END;//
     Delimiter ;

Answer 1

您必须将 SELECT 语句写在括号中才能工作。所以而不是

SET @sum_valg= select SUM(review_trans.rating_given) as sum
        from `review_trans` where  `review_trans`.`business_id`=businessid  and      
  `review_trans`.`user_badge_id`=@badgegold
        GROUP BY `review_trans`.`user_badge_id`;

你应该使用

SET @sum_valg=(select SUM(review_trans.rating_given) as sum
        from `review_trans` where  `review_trans`.`business_id`=businessid  and      
  `review_trans`.`user_badge_id`=@badgegold
        GROUP BY `review_trans`.`user_badge_id`);

然后它应该可以工作。您必须对所有要分配由查询计算的值的 SET 语句执行此操作。