复杂SQL查询

您缺少distinct。

  SELECT U.ID, COUNT(DISTINCT P.Id)+COUNT(DISTINCT C.Id) Count
  FROM User U
  LEFT JOIN Photos P ON P.User_Id=U.Id
  LEFT JOIN Classifications C ON C.User_Id=U.Id
  GROUP BY U.Id
  ORDER BY COUNT(DISTINCT P.Id)+COUNT(DISTINCT C.ID)

我可能误解了你的计划,但这不应该是:

LEFT JOIN classifications ON (classifications.user_id = users.id) 

是这样的:

LEFT JOIN classifications ON (classifications.user_id = users.id) 
                         AND (classifications.photo_id = photos.id)

?

SELECT users1.id, users1.n_photo, users2.n_classifications
FROM (
    SELECT users.id, COUNT(photos.id) AS n_photo
    FROM users LEFT OUTER JOIN photos ON photos.user_id = users.id
    GROUP BY users.id
  ) users1
  INNER JOIN (
    SELECT users.id, COUNT(classifications.id) AS n_classifications
    FROM users LEFT OUTER JOIN classifications ON classifications.user_id = users.id
    GROUP BY users.id
  ) users2 ON users1.id = users1.id

改为尝试类似的方法:

SELECT users.id as n_id,
(SELECT COUNT(photos.id) FROM photos WHERE photos.user_id = n_id) AS n_photos,
(SELECT COUNT(classifications,id) FROM classifications WHERE classifications.user_id = n_id) AS n_classifications,
(n_photos + n_classifications) AS n_sum
FROM users
GROUP BY n_id
ORDER BY n_sum DESC