php - 获取mysql上的货币数量

Question

我有 3 张 table ，

Table Country
---------------------
id_country |country |
---------------------
      1     Brazil
      2     Chile
      3     Colombia

Table Users
------------------------
id_country |colorNumber|
------------------------
      1          1
      1          2
      1          2
      1          3
      2          2
      2          4
      3          1
      3          4

Table Colors
---------------------
id_color | colorName|
---------------------
     1        Red
     2        Blue
     3        Fuschia
     4        Black

我想知道一个国家选择最多的颜色是什么

我做了类似的东西:

select color ,count(color), country as country from users group by (country) order by(color) desc

但是结果是错误的，不是预期的，我做错了什么？

提前致谢，如果这个问题有点菜鸟，抱歉。

Answer 1

以下查询可获取每个国家/地区的颜色数量:

select cc.country, c.colorName, count(*) as cnt
from users join
     colors
     on users.colorNmber = colors.id_color join
     countries cc
     on users.id_country = cc.id_country
group by cc.country, c.colorName;

您想要最受欢迎的一个。这是在 MySQL 中执行此操作的技巧:

select country,
       substring_index(group_concat(colorName order by cnt desc), ',', 1) as MostPopularColor
from (select cc.country, c.colorName, count(*) as cnt
      from users join
           colors
           on users.colorNmber = colors.id_color join
           countries cc
           on users.id_country = cc.id_country
      group by cc.country, c.colorName
     ) t
group by country;