存储过程: MySQL，错误

Question

无法在@testId表中选择id字段，其显示错误代码。

使用的代码

SELECT id INTO @testId, (3959 * ACOS (COS ( RADIANS(deviceLat ) ) * COS( RADIANS(`latitude`) )* 
COS( RADIANS(`longitude` ) - RADIANS(deviceLong) )+ SIN ( RADIANS(deviceLat) )* 
SIN( RADIANS(`latitude` )))) AS dist INTO @distance FROM route_waypoints 
GROUP BY route_ID = @routeID AND (route_waypoints.type =  'PICK' OR 
route_waypoints.type = 'CAMPUS') HAVING dist < 250  ORDER BY dist ASC LIMIT 1;

Answer 1

试试这个..。

  // Declare variables with datatype and size
    DECLARE @testId int, @distance varchar(500 

    // set value of the selected column to that variable by = operator.
    SELECT @testId = id, 
           @distance = (3959 * ACOS (COS ( RADIANS(deviceLat ) ) * COS( RADIANS(`latitude`) )* 
                        COS( RADIANS(`longitude` ) - RADIANS(deviceLong) )+ SIN ( RADIANS(deviceLat) )* 
                        SIN( RADIANS(`latitude` ))))
            FROM route_waypoints 
            GROUP BY route_ID = @routeID AND (route_waypoints.type =  'PICK' 
            OR route_waypoints.type = 'CAMPUS') HAVING dist < 250  
            ORDER BY dist ASC LIMIT 1;    

   // Print    
   SELECT @testId, @distance