mysql 分组子_mysql-子选择和分组

我有两个表：

main：id_main,field1,过滤器

main_logs(5,000万行)：auto inc,id_main,路径

我在寻找以下结果：

id_main,field1,最常见的路径

我尝试了以下查询：

select id_main,

field1,

(select path, count(*) as cpt

from main_log

where main_log.id_main=main.id_main group by path order by cpt desc limit 1)

from main

where filter in (1,3,5);

Mysql return：操作数应包含1列

如果我删除路径,结果是正确的,但是我错过了路径值.

select id_main,

field1,

(select path, count(*) as cpt

from main_log

where main_log.id_main=main.id_main group by path order by cpt desc limit 1)

from main

where filter in (1,3,5);

我不需要count(*)的结果,但我需要将其用于“ order by”

如何编写此查询以获取结果？

谢谢

主要

id_main | field1 | filter

1 | red | 1

2 | blue | 3

3 | pink | 1

main_logs

autoinc | id_main | path

1 | 1 | home1

2 | 1 | home2

3 | 1 | home2

4 | 2 | house2

5 | 2 | house7

6 | 2 | house7

7 | 3 | casee

预期结果

id_main | fields1 | most common path

1 | red | home2

2 | blue | house7

3 | pink | casee