mysql - 使用 UNION 连接查询结果

Question

我正在尝试执行查询并将结果附加到另一个查询结果中，以维持顺序。所以我计算距离，最后按“假变量”排序，然后按距离排序。

这是查询:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Test`(IN basic_user_id INT, IN max_dist INT, IN q VARCHAR(255), IN index_start INT, IN index_end INT)
BEGIN
    DECLARE mylon DOUBLE; 
    DECLARE mylat DOUBLE;
    DECLARE lon1 FLOAT; 
    DECLARE lon2 FLOAT;
    DECLARE lat1 FLOAT; 
    DECLARE lat2 FLOAT;

    SET @location_id = (SELECT location_id from basicuser where id = basic_user_id);

    SET @group_id = (SELECT group_id from basicuser where id = basic_user_id);
    SET @subgroup_id = (SELECT subgroup_id from basicuser where id = basic_user_id);
    SET @tertiarygroup_id = (SELECT tertiarygroup_id from basicuser where id = basic_user_id);

    -- get the original lon and lat for the userid 
    SELECT longitude, latitude into mylon, mylat from location where id = @location_id;
    set lon1 = mylon - max_dist / abs(cos(radians(mylat)) * 69);
    set lon2 = mylon + max_dist / abs(cos(radians(mylat)) * 69);
    set lat1 = mylat - (max_dist / 69);
    set lat2 = mylat + (max_dist / 69);
    select @group_id, @subgroup_id, @tertiarygroup_id;

    (
        SELECT 1 as `temp`, `inradar_ad`.*, 3956 * 2 * ASIN(SQRT(POWER(SIN((orig.latitude - dest.latitude) * pi()/180 / 2), 2) + COS(orig.latitude * pi()/180) * COS(dest.latitude * pi()/180) * POWER(SIN((orig.longitude - dest.longitude) * pi()/180 / 2), 2))) as distance
        FROM
        location AS dest 
        LEFT OUTER JOIN `inradar_ad` ON (`inradar_ad`.location_id = dest.id)
        LEFT OUTER JOIN `inradar_ad_company` ON (`inradar_ad`.`id` = `inradar_ad_company`.`inradarad_ptr_id`)
        LEFT OUTER JOIN `inradar_ad_person` ON (`inradar_ad`.`id` = `inradar_ad_person`.`inradarad_ptr_id`)
        LEFT OUTER JOIN `inradar_category` ON (`inradar_ad`.`category_id` = `inradar_category`.`id`)
        LEFT OUTER JOIN `inradar_subcategory` ON (`inradar_ad`.`subcategory_id` = `inradar_subcategory`.`id`)
        LEFT OUTER JOIN `basicuser` ON (`inradar_ad`.`owner_id` = `basicuser`.`id`) 
        LEFT OUTER JOIN `auth_user` ON (`basicuser`.`user_id` = `auth_user`.`id`)
        LEFT OUTER JOIN `inradar_ad_multiple` ON (`inradar_ad`.`multiple_advertiser_id` = `inradar_ad_multiple`.`id`),
        location AS orig
        WHERE orig.id = @location_id AND
        (
            (
                `inradar_ad_multiple`.`id` IS NULL AND 
                (
                `inradar_ad_company`.`corporate_name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_ad_person`.`name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_category`.`name`  LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_subcategory`.`name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_ad`.`description` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `auth_user`.`first_name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `auth_user`.`last_name` LIKE REPLACE('%$$**$$%', '$$**$$', q)
                )
            )
        ) AND
        `basicuser`.`tertiarygroup_id` = @tertiarygroup_id AND
        dest.longitude BETWEEN lon1 AND lon2 AND dest.latitude BETWEEN lat1 AND lat2 
        HAVING distance < max_dist

    ) UNION (
        SELECT 2 as `temp`, `inradar_ad`.*, 3956 * 2 * ASIN(SQRT(POWER(SIN((orig.latitude - dest.latitude) * pi()/180 / 2), 2) + COS(orig.latitude * pi()/180) * COS(dest.latitude * pi()/180) * POWER(SIN((orig.longitude - dest.longitude) * pi()/180 / 2), 2))) as distance
        FROM
        location AS dest 
        LEFT OUTER JOIN `inradar_ad` ON (`inradar_ad`.location_id = dest.id)
        LEFT OUTER JOIN `inradar_ad_company` ON (`inradar_ad`.`id` = `inradar_ad_company`.`inradarad_ptr_id`)
        LEFT OUTER JOIN `inradar_ad_person` ON (`inradar_ad`.`id` = `inradar_ad_person`.`inradarad_ptr_id`)
        LEFT OUTER JOIN `inradar_category` ON (`inradar_ad`.`category_id` = `inradar_category`.`id`)
        LEFT OUTER JOIN `inradar_subcategory` ON (`inradar_ad`.`subcategory_id` = `inradar_subcategory`.`id`)
        LEFT OUTER JOIN `basicuser` ON (`inradar_ad`.`owner_id` = `basicuser`.`id`) 
        LEFT OUTER JOIN `auth_user` ON (`basicuser`.`user_id` = `auth_user`.`id`)
        LEFT OUTER JOIN `inradar_ad_multiple` ON (`inradar_ad`.`multiple_advertiser_id` = `inradar_ad_multiple`.`id`),
        location AS orig
        WHERE orig.id = @location_id AND
        (
            (
                `inradar_ad_multiple`.`id` IS NULL AND 
                (
                `inradar_ad_company`.`corporate_name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_ad_person`.`name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_category`.`name`  LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_subcategory`.`name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_ad`.`description` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `auth_user`.`first_name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `auth_user`.`last_name` LIKE REPLACE('%$$**$$%', '$$**$$', q)
                )
            )
        ) AND
        `basicuser`.`subgroup_id` = @subgroup_id AND
        dest.longitude BETWEEN lon1 AND lon2 AND dest.latitude BETWEEN lat1 AND lat2 
        HAVING distance < max_dist
    ) UNION (
        SELECT 3 as `temp`, `inradar_ad`.*, 3956 * 2 * ASIN(SQRT(POWER(SIN((orig.latitude - dest.latitude) * pi()/180 / 2), 2) + COS(orig.latitude * pi()/180) * COS(dest.latitude * pi()/180) * POWER(SIN((orig.longitude - dest.longitude) * pi()/180 / 2), 2))) as distance
        FROM
        location AS dest 
        LEFT OUTER JOIN `inradar_ad` ON (`inradar_ad`.location_id = dest.id)
        LEFT OUTER JOIN `inradar_ad_company` ON (`inradar_ad`.`id` = `inradar_ad_company`.`inradarad_ptr_id`)
        LEFT OUTER JOIN `inradar_ad_person` ON (`inradar_ad`.`id` = `inradar_ad_person`.`inradarad_ptr_id`)
        LEFT OUTER JOIN `inradar_category` ON (`inradar_ad`.`category_id` = `inradar_category`.`id`)
        LEFT OUTER JOIN `inradar_subcategory` ON (`inradar_ad`.`subcategory_id` = `inradar_subcategory`.`id`)
        LEFT OUTER JOIN `basicuser` ON (`inradar_ad`.`owner_id` = `basicuser`.`id`) 
        LEFT OUTER JOIN `auth_user` ON (`basicuser`.`user_id` = `auth_user`.`id`)
        LEFT OUTER JOIN `inradar_ad_multiple` ON (`inradar_ad`.`multiple_advertiser_id` = `inradar_ad_multiple`.`id`),
        location AS orig
        WHERE orig.id = @location_id AND
        (
            (
                `inradar_ad_multiple`.`id` IS NULL AND 
                (
                `inradar_ad_company`.`corporate_name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_ad_person`.`name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_category`.`name`  LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_subcategory`.`name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `inradar_ad`.`description` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `auth_user`.`first_name` LIKE REPLACE('%$$**$$%', '$$**$$', q)  OR 
                `auth_user`.`last_name` LIKE REPLACE('%$$**$$%', '$$**$$', q)
                )
            )
        ) AND
        `basicuser`.`group_id` = @group_id AND
        dest.longitude BETWEEN lon1 AND lon2 AND dest.latitude BETWEEN lat1 AND lat2 
        HAVING distance < max_dist
    )

    ORDER by `temp` ASC, distance ASC;

END

但这会返回重复的条目，如下所示:

    # temp, id, seller_id, owner_id, description, category_id, subcategory_id, video_url, logo, location_id, business_hours, subscription_plan_id, tags, advertiser_occupation, advertiser_group_message, email, email_contact_form, website, e_commerce, phone, phone2, blap_phone, delivery, comment_votes, comment_quantity, multiple_advertiser_id, user_type, additional_info, advertiser_available, used_free_coupom, distance
1   37294       40      35  2           37667                                       (62) 3523-9609              0   0       company     0   0   105.60177674937776
1   37256       36      35  1           37557                                       (19)4141-5857           0   0   0       company     0   0   233.5020148948106
1   37254       40      35  1           37555                                       (16) 3624-8409              0   0       company     0   0   297.9775326093067
1   37264       40      35  1           37579                                       (67) 3251-1186              0   0       company     0   0   829.305941965672
2   37294       40      35  2           37667                                       (62) 3523-9609              0   0       company     0   0   105.60177674937776
2   37255       52      35  1           37556                                       (11) 5669-0169              0   0       company     0   0   218.0241298958371
2   37256       36      35  1           37557                                       (19)4141-5857           0   0   0       company     0   0   233.5020148948106
2   37254       40      35  1           37555                                       (16) 3624-8409              0   0       company     0   0   297.9775326093067
2   37264       40      35  1           37579                                       (67) 3251-1186              0   0       company     0   0   829.305941965672
3   37294       40      35  2           37667                                       (62) 3523-9609              0   0       company     0   0   105.60177674937776
3   37255       52      35  1           37556                                       (11) 5669-0169              0   0       company     0   0   218.0241298958371
3   37256       36      35  1           37557                                       (19)4141-5857           0   0   0       company     0   0   233.5020148948106
3   37254       40      35  1           37555                                       (16) 3624-8409              0   0       company     0   0   297.9775326093067
3   37264       40      35  1           37579                                       (67) 3251-1186      

    0   0       company     0   0   829.305941965672

我做错了什么吗？

提前非常感谢。

Answer 1

您可以摆脱 UNION 并在一个 SELECT 中完成整个操作。

CASE 语句

选择您想要的所有内容，并在 SELECT 子句中设置您的假变量。

SELECT CASE
WHEN `basicuser`.`tertiarygroup_id` = @tertiarygroup_id THEN 1
WHEN `basicuser`.`subgroup_id` = @subgroup_id THEN 2
WHEN `basicuser`.`group_id` = @group_id THEN 3
END as temp, ...
...
WHERE (
`basicuser`.`tertiarygroup_id` = @tertiarygroup_id OR
`basicuser`.`subgroup_id` = @subgroup_id OR
`basicuser`.`group_id` = @group_id
) AND ...

ORDER BY bool 值

如果您的第三/第二/组有树状结构，则可以利用 ORDER BY 子句中的 bool 顺序

ORDER BY cond1 DESC, cond2 DESC, cond3 DESC, distance ASC