MySQL:获取两个字段组合的最高数量

Question

我有两个表:实验和配对。

experiments:
-experimentId
-user

pairings:
-experimentId
-tone
-color

每个实验都包含七个配对。配对包括将颜色与色调相匹配。并且该实验由单个用户重复多次。

现在我正试图找出如何获得每个音调的最大配对数。示例:

user  | tone | color | number of equal pairings

user1 | b4   | red   | 5
user1 | c4   | blue  | 4
user2 | b4   | green | 4
…

到目前为止，我可以通过以下查询获得所有相等的配对:

SELECT user, tone, color, COUNT(tone) as toneCounter
FROM experiments LEFT JOIN pairings ON experiments.experimentId = pairings.experimentId
GROUP BY user, tone, color
ORDER BY toneCounter DESC, user ASC

例如，这看起来像这样:

user  | tone | color | number of equal pairings

user1 | b4   | red   | 5
user1 | b4   | blue  | 2
user1 | c4   | blue  | 4
user1 | c4   | red   | 1
user1 | c4   | green | 2
user2 | b4   | green | 4
…

但我不确定如何只获得top 相等的配对。所以在上面的例子中，我想去掉 user1 的 b4 和 c4 的其他条目，只显示 b4 红色和 c4 蓝色。

我尝试使用以下查询，但显然这不是有效的 SQL:

SELECT user, tone, color, COUNT(tone) as toneCounter
FROM experiments LEFT JOIN pairings ON experiments.experimentId = pairings.experimentId 
GROUP BY user, tone, color
HAVING toneCounter = (select max(COUNT(tone)) as tc from pairings as p where p.tone = pairings.tone)
ORDER BY toneCounter DESC, user ASC

我该怎么做？

Answer 1

2 个 SQL-Statments，第二个应该做...

SELECT
    AA.user, AA.tone, AA.color, MAX(AA.toneCounter) as toneCounter
FROM (
    SELECT
    user, tone, color, COUNT(tone) as toneCounter
    FROM
    experiments
    LEFT JOIN
    pairings
    ON
    experiments.experimentId = pairings.experimentId 
    GROUP BY
    user, tone, color
) AA
Group by
    AA.user, AA.tone

...我的回答并不令自己满意，我仔细检查了一下。而且我认为下一个答案更合适(甚至可以在 no-mysql 上运行)

SELECT 
    AAA.user, AAA.tone, BBB.color, AAA.toneCounter 
FROM (
    SELECT
    AA.user, AA.tone, MAX(AA.toneCounter) as toneCounter
    FROM (
    SELECT
        user, tone, color, COUNT(tone) as toneCounter
    FROM
        experiments
    LEFT JOIN
        pairings
    ON
        experiments.experimentId = pairings.experimentId 
    GROUP BY
        user, tone, color
    ) AA
    Group by
    AA.user, AA.tone
) AAA
join (
    SELECT
    BB.user, BB.tone, BB.color, MAX(BB.toneCounter) as toneCounter
    FROM (
    SELECT
        user, tone, color, COUNT(tone) as toneCounter
    FROM
        experiments
    LEFT JOIN
        pairings
    ON
        experiments.experimentId = pairings.experimentId 
    GROUP BY
        user, tone, color
    ) BB
    Group by
    BB.user, BB.tone, BB.color 
) BBB
ON
    BBB.user = AAA.user
    AND BBB.tone = AAA.tone 
    AND BBB.toneCounter = AAA.toneCounter