当我使用全部联合并分组时获取所有最后一个 ID

Question

如何更改以下代码以获取所有三元组(nr1，n2，n3(的最大id？ 每组三元组出现多次(几个 id(。我想获取每个三元组的最大 id。例如：

id    Number1   Number2   Number3   Number4   Number5                                                                       
66       2         3         4         7         9
74       2         3         5         6         8                        
102      3         7         8         9        10
123      3         6         7         9        10
157      1         2         3         4         5   
188      1         2         3         5         7 

答案(前两行(按计数排序((：

nr1   nr2   nr3   count   max_id                                            
2     3     5      3       188
3     7     9      3       123

我试图在选择中添加id，但结果不完整。请帮忙。

select nr1,nr2,nr3, count(*)
from         (select Number1 as nr1, Number2 as nr2, Number3 as nr3 from tab
union all
select Number1 as nr1, Number2 as nr2, Number4 as nr3 from tab
union all
select Number1 as nr1, Number2 as nr2, Number5 as nr3 from tab
union all
select Number1 as nr1, Number3 as nr2, Number4 as nr3 from tab
union all
select Number1 as nr1, Number3 as nr2, Number5 as nr3 from tab
union all
select Number1 as nr1, Number4 as nr2, Number5 as nr3 from tab
union all
select Number2 as nr1, Number3 as nr2, Number4 as nr3 from tab
union all
select Number2 as nr1, Number3 as nr2, Number5 as nr3 from tab
union all
select Number2 as nr1, Number4 as nr2, Number5 as nr3 from tab
union all
select Number3 as nr1, Number4 as nr2, Number5 as nr3 from tab) g
group by nr1, nr2, nr3
ORDER BY count(*) DESC, nr1, nr2, nr3

Answer 1

DESC 顺序中的最后一个是 ASC 中的第一个，因此您可以将限制 1 用于 ASC

select nr1,nr2,nr3, count(*)
from         (select Number1 as nr1, Number2 as nr2, Number3 as nr3 from table
union all
select Number1 as nr1, Number2 as nr2, Number4 as nr3 from table
union all
select Number1 as nr1, Number2 as nr2, Number5 as nr3 from table
union all
select Number1 as nr1, Number3 as nr2, Number4 as nr3 from table
union all
select Number1 as nr1, Number3 as nr2, Number5 as nr3 from table
union all
select Number1 as nr1, Number4 as nr2, Number5 as nr3 from table
union all
select Number2 as nr1, Number3 as nr2, Number4 as nr3 from table
union all
select Number2 as nr1, Number3 as nr2, Number5 as nr3 from table
union all
select Number2 as nr1, Number4 as nr2, Number5 as nr3 from table
union all
select Number3 as nr1, Number4 as nr2, Number5 as nr3 from table) g
group by nr1, nr2, nr3
ORDER BY count(*) ASC, nr1, nr2, nr3
LIMIT 1

Answer 2

你应该修复你的数据结构。 最好每个 id 和每个数字有一行。

这看起来很复杂。 在不寻常的情况下，我将从一个视图开始(MySQL不支持CTE(：

create view better_data as
select id, number1 as num, 1 as which from tab union all
select id, number2 as num, 2 as which from tab union all
select id, number3 as num, 3 as which from tab union all
select id, number4 as num, 4 as which from tab union all
select id, number5 as num, 5 as which from tab;

然后，您可以使用以下方法生成所有三元组：

select distinct bd1.num, bd2.num, bd3.num
from better_data bd1 cross join
better_data bd2 cross join
better_data bd3
where bd1.num < bd2.num and bd2.num < bd3.num;

现在，您可以使用聚合来计算这些内容：

select triples.num1, triples.num2, triples.num3, count(*) as cnt
from (select distinct bd1.num as num1, bd2.num as num2, bd3.num as num3
from better_data bd1 cross join
better_data bd2 cross join
better_data bd3
where bd1.num < bd2.num and bd2.num < bd3.num
) triples left join
t
on triples.num1 in (tab.number1, tab.number2, tab.number3, tab.number4, tab.number5) and
triples.num2 in (tab.number1, tab.number2, tab.number3, tab.number4, tab.number5) and
triples.num3 in (tab.number1, tab.number2, tab.number3, tab.number4, tab.number5)
group by triples.num1, triples.num2, triples.num3
order by count(*) desc;