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
…

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

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
…

然而，我不知道如何才能只得到顶级相等的配对。因此，在上面的示例中，我希望去掉b4的其他条目和user1的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-状态，第二应该做它.

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

..。我的回答不能使我感到满意，于是我把它说了一遍。我认为下一个答案更合适(甚至不用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 

Answer 2

如果我正确地理解了您的问题，我将首先从结果表中检索每个用户的每个音调的最大音调计数器。第二，我将使用这一信息左联接与相同的结果表，您将得到最终的结果。

SELECT OriRef.* 
FROM
(
    SELECT user, tone, MAX(toneCounter) AS maxToneCounter
    FROM
    ( 
        SELECT user, tone, color, COUNT(tone) as toneCounter
        FROM experiments LEFT JOIN pairings ON experiments.experimentId = pairings.experimentId 
        GROUP BY user, tone, color
    ) AS Ref
) AS MaxRef
LEFT JOIN
( 
    SELECT user, tone, color, COUNT(tone) as toneCounter
    FROM experiments LEFT JOIN pairings ON experiments.experimentId = pairings.experimentId 
    GROUP BY user, tone, color
) AS OriRef ON MaxRef.user = OriRef.user AND MaxRef.tone = OriRef.tone AND MaxRef.maxToneCounter = OriRef.toneCounter

如果我错了，请纠正我。

Answer 3

create table experiments(
experimentId int identity(1,1),
[user] nvarchar(256));

create table pairings(
experimentId int,
tone nvarchar(256),
color nvarchar(256));

insert into experiments([user]) values ('user1');
insert into experiments([user]) values ('user1');
insert into experiments([user]) values ('user1');
insert into experiments([user]) values ('user2');
insert into experiments([user]) values ('user1');
insert into experiments([user]) values ('user1');
insert into experiments([user]) values ('user1');

insert into pairings(experimentId, tone,color) values (1,'b4','red');
insert into pairings(experimentId, tone,color) values (2,'b4','red');
insert into pairings(experimentId, tone,color) values (3,'c4','green');
insert into pairings(experimentId, tone,color) values (4,'c4','blue');
insert into pairings(experimentId, tone,color) values (5,'b4','red');
insert into pairings(experimentId, tone,color) values (6,'b4','green');
insert into pairings(experimentId, tone,color) values (7,'b4','green');

IF OBJECT_ID('tempdb..#tempData') IS NOT NULL DROP TABLE #tempData;
IF OBJECT_ID('tempdb..#tempTones') IS NOT NULL DROP TABLE #tempTones;
select  a.[user],b.tone,b.color,count(b.tone) as toneCounter into #tempData
from experiments a inner join pairings b on a.experimentId=b.experimentId
group by a.[user],b.tone,b.color
order by toneCounter desc,a.[user] asc;

select distinct [user],tone into #tempTones from #tempData;
with cte as(
select a.[user],a.[tone],max(a.toneCounter) as toneCounter from #tempData a inner join 
 #tempTones b on (b.[user]=a.[user] and b.tone=a.tone) group by a.tone,a.[user]
)
select a.* from #tempData a inner join cte b on 
(b.[user]=a.[user] and a.toneCounter=b.toneCounter and a.tone=b.tone)