php - 如何在不显示重复项的情况下在mysql中分组？

Question

我正在为保姆列表构建一个搜索页面，用户可以根据工资、距离和等级过滤结果。一切正常，但即使只有 2 人或 1 人，我也会得到三到四个结果，我无法弄清楚。

这是我的查询:

SELECT `users`.`id`                        AS `user_id`, 
       `user_wage_preferences`.`one_child` AS `wage`, 
       `user_contact_informations`.`lat`, 
       `user_contact_informations`.`lng`, 
       `user_reviews`.`rating`, 
       Sum(user_reviews.recommended)       AS recommended, 
       ( 6371 * Acos(Cos(Radians(59.448355500000005)) * Cos(Radians(lat)) * Cos( 
                                   Radians(lng) - Radians(24.7406023)) + 
                              Sin(Radians(59.448355500000005)) * Sin( 
                              Radians(lat))) ) 
                                           AS distance 
FROM   `users` 
       LEFT JOIN `user_reviews` 
              ON `users`.`id` = `user_reviews`.`nanny_id` 
       INNER JOIN `user_wage_preferences` 
               ON `users`.`id` = `user_wage_preferences`.`user_id` 
       INNER JOIN `user_contact_informations` 
               ON `users`.`id` = `user_contact_informations`.`user_id` 
WHERE  `users`.`role` = ? 
       AND `users`.`account_status` = ? 
       AND `users`.`id` IN ( ?, ? ) 
GROUP  BY `user_id`, 
          `user_contact_informations`.`lat`, 
          `user_contact_informations`.`lng`, 
          `user_reviews`.`rating`, 
          `user_wage_preferences`.`one_child` 
HAVING `recommended` > ? 
ORDER  BY `recommended` DESC 

在我的 json 响应中，我看到了这个:

[  
    {  
        "user_id":1,
        "wage":"6",
        "lat":59.44,
        "lng":24.74,
        "rating":5,
        "recommended":"1",
        "distance":0.92971285651468
    },
    {  
        "user_id":4,
        "wage":"4",
        "lat":59.46,
        "lng":24.83,
        "rating":5,
        "recommended":"1",
        "distance":5.2153552636895
    },
    {  
        "user_id":1,
        "wage":"6",
        "lat":59.44,
        "lng":24.74,
        "rating":4,
        "recommended":"0",
        "distance":0.92971285651468
    },
    {  
        "user_id":4,
        "wage":"4",
        "lat":59.46,
        "lng":24.83,
        "rating":1,
        "recommended":"1",
        "distance":5.2153552636895
    }
]

如您所见，我得到了两次 user_id 1 和 user_id 4。而且我不知道如何删除重复项或按 user id 分组。

我在这里做错了什么？如何向用户显示一次？我正在使用 laravel 作为框架。

Answer 1

您的结果评分不同。

我可以看到用户 1 的评分为 4、5。如果您删除评级，您将只会获得一条记录。

您还可以使用群组联系人以逗号分隔的字符串形式获得多个评级 -GROUP_CONCAT(评级 ORDER BY 评级 ASC)