mysql - 如何计算 MySQL 查询中相似行的总数

Question

我需要 MySQL 查询方面的帮助来计算相同的age_from 和age_to 组合出现的次数。

示例数据:

 age_from   age_to
+---------+-------+
  18      |   100
  30      |   75
  18      |   50
  18      |   100
  30      |   75
  18      |   50
  30      |   75
+---------+------+

期望的结果:

18 to 100 = 2
30 to 75 = 3
18 to 50 = 2

我已经尝试过这个:

SELECT
`p_age_from` AS `age_from`,
`p_age_to` AS `age_to`,
COUNT(`p_age_from`) AS `user_count`
FROM `user`
GROUP BY `p_age_from`, `p_age_to`
ORDER BY `p_age_from`

Answer 1

您可以使用 CONCAT 连接 age_from 和 age_to 值。然后用它来对数据进行分组和计数:

SELECT
CONCAT(`age_from`,' to ',`age_to`) as 'grouping',
COUNT('grouping') AS `user_count`
FROM `user`
GROUP BY grouping