在子查询中加入“最大-n-每组”和“和”。

Question

有超过1800个带有标签的greatest-n-per-group问题和一些出色的答案，我想我会找到一个解决这个问题的方法--但是我要么错过了解决方案，要么我需要一个新的方法。

我有一个桌子photo_types来存储user的投票，他们正在投票(上或下)他们认为某张照片是什么特定的photo_type。照片类型为1-10，每次投票都是1或-1。

+----+-----+-----------+------------+------+
| id | user | photo_id | photo_type | vote |
+----+------+----------+------------+------+
|  1 | jane |   photo1 |          1 |    1 |
|  2 | jane |   photo2 |          2 |    1 |
|  3 | jane |   photo3 |          4 |   -1 |
|  4 |  ben |   photo1 |          1 |    1 |
|  5 |  ben |   photo2 |          3 |   -1 |
|  6 |  ben |   photo2 |          2 |    1 |
|  7 | mary |   photo1 |          1 |   -1 |
|  8 | mary |   photo3 |         10 |    1 |
|  9 | mary |   photo2 |          1 |    1 |
| 10 | mary |   photo1 |          2 |   -1 |
+----+------+----------+------------+------+

我需要让这个表回到一个photos表(该表包含给定照片的所有其他细节)--，但只包括每张照片的前2位投票类型。

我需要对photos表进行LEFT JOIN的photo_types表如下所示：

+----+----------+------------+----------------+---------------+------------+
| id | photo_id | photo_name |   photographer |      location |       date |
+----+----------+------------+----------------+---------------+------------+
|  1 |   photo1 | the bridge |    Bill Murray |  Brooklyn, NY | 2012-10-11 |
|  2 |   photo2 |    the cat | Jacques Chirac | Paris, France | 2013-01-03 |
|  3 |   photo3 |      a car |     the Grinch |    London, UK | 2016-09-01 |
+----+----------+------------+----------------+---------------+------------+

显然，我是通过photo_id加入这两张桌子的。

为了获得每一张照片的最高投票类型，我尝试了如下所示的子查询：

SELECT photo_id, photo_type, sum(vote) AS votes
FROM photo_types
GROUP BY photo_type, photo_id
HAVING votes>0
ORDER BY votes DESC

它将由photo_type和photo_id组成的选票之和分组。

这是很好的工作，但包括所有类型与sum(vote) > 0 -不只是前两个投票类型。

SQL Fiddle在这里

当包含在联接中时，如下所示：

SELECT * 
FROM photos
LEFT JOIN
    (SELECT photo_id, photo_type, sum(vote) AS votes
    FROM photo_types
    GROUP BY photo_type, photo_id
    HAVING votes>0
    ORDER BY votes DESC) AS pt
ON photos.photo_id = pt.photo_id
WHERE photos.date > '2010-01-01';

SQL Fiddle在这里

我本来希望使用比尔·卡温的解决方案，但在基于分组值(在我的例子中是SUM )的基础上，我很难将表连接到它自己。我尝试的子查询看起来如下：

SELECT pt1.*, SUM(pt1.vote) AS votes1, SUM(pt2.vote) AS votes2
FROM photo_types AS pt1
LEFT OUTER JOIN photo_types AS pt2
    ON pt1.photo_id = pt2.photo_id
        AND (votes1 < votes2
        OR (votes1 = votes2 AND pt1.id < pt2.id))
WHERE pt2.photo_id IS NULL

...which无法工作，因为它试图在计算值上连接两个表(与Bill的解决方案不同)。

SQL Fiddle在这里

问题

当分组是基于计算值(如greatest-n-per-group SUM(xxx) )时，有一种方法可以获得吗？

部分涵盖这一点的解决方案是这里和这里，但在分组值中不包括聚合。

另一个明显的方法是简单地重新计算每次投票时最高的投票值，并将其直接存储在photos表-- 如这里所讨论的 --但除非是不可能的，否则出于各种原因，我更愿意在SELECT中计算。

Answer 1

如果您有一个有限的列表，最简单的方法是substring_index()/group_concat()技巧：

SELECT photo_id,
       SUBSTRING_INDEX(GROUP_CONCAT(photo_type ORDER BY votes DESC), ',', 2) as top2
FROM (SELECT photo_id, photo_type, sum(vote) AS votes
      FROM photo_types
      GROUP BY photo_type, photo_id
      HAVING votes > 0
     ) pt
GROUP BY photo_id;

备注：

• group_concat()的中间字符串大约是1k --这对这个问题来说已经足够了。
• 替代品(正如您已经发现的那样)要么使用变量进行更复杂的查询。

Answer 2

查找xxx应用函数。它们为您提供了更大的灵活性，而不仅仅是进行子聚合查询。

http://sqlserverplanet.com/sql-2005/cross-apply-explained

Answer 3

因此，在这个旧博文中(在其他greatest-n-per-group解决方案中提到过几次)，下面的工作如下：

SELECT pt1.*
FROM 
  (SELECT id, photo_id, photo_type, sum(vote) AS votes
  FROM photo_types
  GROUP BY photo_type, photo_id
  HAVING votes>0) AS pt1
WHERE (
  SELECT COUNT(*) 
  FROM 
    (SELECT id, photo_id, photo_type, sum(vote) AS votes
    FROM photo_types
    GROUP BY photo_type, photo_id
    HAVING votes>0) AS pt2
  WHERE pt1.photo_id = pt2.photo_id and pt1.votes <= pt2.votes
) <=2
ORDER BY photo_id, votes DESC

在这里见SqlFiddle

然而：

• 不确定它有多高效，因为它使用了两个子查询
• 如果任何greatest-n的值相同(因为这会将计数推到指定的限制之外)，则不会返回正确的结果数--正如您可以看到的在这个SqlFiddle中