php - 合并2条sql查询位置和平均评分

Question

如何将这两个 SQL 查询合并到一个查询中？

$query2 ="select raiting.rated_user,avg(raiting.rate) as `avg_rating` from raiting,users  where
raiting.rated_user=user_id  group by raiting.rated_user order by avg(raiting.rate) desc";

$query      = “SELECT `user_id`,`name`,`email`,`degree`,`avatar`,`mobile`,`sex`,`nationality`,`nationalitycode`,`birthday_year`,`pr_hour`,`spicality`,`city`,`online`,`intro`, (SQRT(POW((lat - $lat), 2) + POW((lang - $lang), 2)) * $multiplier) AS distance FROM users  WHERE POW((lat - $lat), 2) + POW((lang - $lang), 2) < POW(($distance / $multiplier), 2) ORDER BY distance";

Answer 1

使用第一个查询作为第二个查询中的子查询:

$query = "
 SELECT
      `user_id`,`name`,`email`,`degree`,`avatar`,`mobile`
      ,`sex`,`nationality`,`nationalitycode`,`birthday_year`,`pr_hour`
      ,`spicality`,`city`,`online`
      ,`intro`
      , (SQRT(POW((lat - $lat), 2) + POW((lang - $lang), 2)) * $multiplier) AS distance 
      , r.`avg_rating`
 FROM users 
 INNER JOIN (
      select
            raiting.rated_user
           ,avg(raiting.rate) as `avg_rating` 
      from raiting
      group by raiting.rated_user 
      ) AS r  on users.user_id  = raiting.rated_user
 WHERE POW((lat - $lat), 2) + POW((lang - $lang), 2) < POW(($distance / $multiplier), 2) 
 ORDER BY distance
";

<小时/>

SQL 查询可以分布在多行上，使其更易于维护

采取预防措施避免 SQL 注入(inject)