php - ORDER BY 不与 GROUP BY 一起使用

Question

我已经使用 group by 和 order by 编写了 sql 代码，但是在这里 order by 不起作用任何人都可以帮助我

SELECT " . DB_PREFIX . "leaderboard_scores.* , SUM(" . DB_PREFIX . "leaderboard_scores.score) as total_score, " . DB_PREFIX . "customer.firstname, 
        " . DB_PREFIX . "customer.lastname
FROM " . DB_PREFIX . "leaderboard_scores 
JOIN " . DB_PREFIX . "customer ON  " . DB_PREFIX . "leaderboard_scores.philips_store_id = " . DB_PREFIX . "customer.customer_id
GROUP BY " . DB_PREFIX . "leaderboard_scores.phi_store_id
ORDER BY " . DB_PREFIX . "leaderboard_scores.week DESC

此查询没有 php 错误，但给出了中间行，但没有给出前几周。 weeks 存储使用时间戳

实际查询

SELECT rc_leaderboard_scores.* , SUM(rc_leaderboard_scores.score) 
as total_score, rc_customer.firstname, rc_customer.lastname 
FROM rc_leaderboard_scores 
JOIN rc_customer 
ON rc_leaderboard_scores.phi_store_id = rc_customer.customer_id 
GROUP BY rc_leaderboard_scores.phi_store_id 
ORDER BY rc_leaderboard_scores.week DESC 

Answer 1

如果您需要为每个客户返回最后一条记录，您需要一个 JOIN 和一个为每个客户 ID 返回 MAX(week) 的子查询:

SELECT
  rc_leaderboard_scores.*,
  m.total_score,
  rc_customer.firstname,
  rc_customer.lastname 
FROM
  (SELECT   phi_store_id,
            MAX(`week`) as max_week,
            SUM(score) as total_score,
   FROM     rc_leaderboard_scores
   GROUP BY phi_store_id) m
  INNER JOIN
  rc_leaderboard_scores
  ON rc_leaderboard_scores.phi_store_id = m.phi_store_id
     AND rc_leaderboard_scores.`week` = m.max_week
  INNER JOIN
  rc_customer ON m.phi_store_id = rc_customer.customer_id