mysql - 合并来自 MySQL 存储过程的结果

Question

我有一个存储过程，它根据用户可以选择他们想要搜索的多个位置这一事实来查找用户的偏好。因此，对于每个位置，查询需要在区域内查找结果。目前，该过程仅返回 1 个结果。此外，我希望能够实现某种排名系统，以便将结果排序为一个组合提要。我意识到如果我使用 while 循环，它将按位置对结果进行分组，这是我不想要的。重组此过程的最佳方式是什么，以便将 3 个查询的结果网格化并允许我灵活地对结果进行排序？

DELIMITER $$

    DROP PROCEDURE IF EXISTS `geodist` $$
    CREATE PROCEDURE geodist (IN userid INT) BEGIN
    DECLARE mylon DOUBLE;  DECLARE mylat DOUBLE;
    DECLARE lon1 FLOAT;  DECLARE lon2 FLOAT;
    DECLARE lat1 FLOAT; DECLARE lat2 FLOAT;
    DECLARE dist INT;
    DECLARE no_more_locations INT; DECLARE l_location_count INT;

    -- get the original lon and lat for the userid
    DECLARE location_cursor CURSOR FOR
        SELECT geo_lat, geo_long, distance
        FROM activity_location_preferences
        INNER JOIN activity_preferences ON activity_location_preferences.preference_id = activity_preferences.id
        WHERE activity_preferences.user_id = userid
        ORDER BY activity_preferences.id ASC
        LIMIT 3;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_locations = 1;

    SET no_more_locations = 0;
    OPEN location_cursor;
    location_loop:WHILE(no_more_locations = 0) DO
        FETCH location_cursor INTO mylat, mylon, dist;
        IF no_more_locations = 1 THEN
            LEAVE location_loop;
        END IF;
        SET l_location_count = l_location_count+1;

        -- calculate lon and lat for the rectangle:
        SET lon1 = mylon - dist / abs(cos(radians(mylat)) * 69);
        SET lon2 = mylon + dist / abs(cos(radians(mylat)) * 69);
        SET lat1 = mylat - (dist / 69);
        SET lat2 = mylat + (dist / 69);

        -- run the query:
        SELECT `id`, `user_id`, `activity`, `geo_lat`, `geo_long`, `data`, `date_created`,  
        7912 * ASIN(SQRT(POWER(SIN((mylat - activity_logs.geo_lat) * pi()/180 / 2), 2) +  
        COS(mylat * pi()/180) *  COS(activity_logs.geo_lat * pi()/180) *  POWER(SIN((mylon - activity_logs.geo_long) * pi()/180 / 2), 2)  )) as distance
        FROM activity_logs
        WHERE 1
        AND activity_logs.geo_long BETWEEN lon1 AND lon2 
        AND activity_logs.geo_lat BETWEEN lat1 AND lat2
        HAVING distance < dist ORDER BY date_created DESC LIMIT 100;

    END WHILE location_loop;
    CLOSE location_cursor;
    SET no_more_locations = 0;

END $$

DELIMITER ;

Answer 1

想到的最简单的方法是创建一个临时表来存储 3 个结果，然后从临时表中查询这 3 个结果作为过程的最终结果。

另一种方法是将所有查询重写为一个带有子查询的查询，如果我从头开始编写，我可能会自己这样做，因为它比使用临时表更快。