抱歉这个标题令人困惑，我想要实现的是，通过下表获取一份工作的总申请数:

CREATE TABLE IF NOT EXISTS `applications` (
  `application_id` int(11) NOT NULL AUTO_INCREMENT,
  `application_user` varchar(100) NOT NULL,
  `application_date` datetime NOT NULL,
  `application_job` int(11) NOT NULL,
  `application_status` varchar(10) DEFAULT 'pending',
  `application_enabled` int(2) NOT NULL DEFAULT '1',
  `application_resume` int(11) NOT NULL,
  `application_description` text NOT NULL,
  PRIMARY KEY (`application_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

为了了解他们的年龄，我将 user_personal_information 加入到 application_user 中，因为他们是申请工作的用户。我的查询:

SELECT count(*) as total,
       user_gender as gender,
       TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE()) AS age,
       application_date
FROM applications
LEFT JOIN user_personal_information
    ON user_personal_information_user = application_user
WHERE application_job = ?

我的用户表 user_gender 可以等于 male/female 和 user_birthdate 在上面的声明中我正在将其转换为年龄。

我正在尝试对年龄范围内的所有应用程序进行分组，例如:

16 - 21
22 - 30
31 - 45
45 - 64
65+

以及该年龄段的男性和女性百分比。用于需要这样数据的数据图表:

 "dataProvider": [
 {
   "age": "85+",
   "male": 25, //
   "female": 25
 }, {
   "age": "80-54",
   "male": 25,//percentage
   "female": 25//percentage
}]

因此，从上文可以看出，有 25% 的男性申请了 85 岁及以上，而女性为 25%。您明白了要点，这就是我试图让我的 select 语句起作用的方式。

这将创建一个像这样的图表:

所以澄清一下，我想统计申请总数，并根据性别和年龄组计算出申请的百分比。如何使用上面的 select 语句执行此操作？

最佳答案

几个嵌套的 group-by 子查询可以为您完成。请注意，计算的百分比是特定于性别的:

select a.age, a.gender, a.cnt, 100*a.cnt/b.sm as percentage from
    (
        SELECT user_gender as gender,
               TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE()) AS age,
               count(*) as cnt,
        FROM applications
        LEFT JOIN user_personal_information
        ON user_personal_information_user = application_user
        WHERE application_job = ?
        GROUP BY user_gender, TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE())
    ) a,
    (
        SELECT TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE()) AS age,
               count(*) as sm
        FROM applications
        LEFT JOIN user_personal_information
        ON user_personal_information_user = application_user
        WHERE application_job = ?
        GROUP BY TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE())
    ) b
    where a.age = b.age;

如果您要查找特定于总应用程序的百分比，您需要类似以下内容:

select a.age, 
       a.gender, 
       a.cnt, 
100*a.cnt/(
            select count(TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE()))
            from
            applications 
            LEFT JOIN user_personal_information
            ON user_personal_information_user = application_user
            WHERE application_job = ?
         ) as percentage 
from
    (
        SELECT user_gender as gender,
               TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE()) AS age,
               count(*) as cnt,
        FROM applications
        LEFT JOIN user_personal_information
        ON user_personal_information_user = application_user
        WHERE application_job = ?
        GROUP BY user_gender, TIMESTAMPDIFF(YEAR, user_birthdate, CURDATE())
    ) a;

关于mysql - 试图获得申请了年龄范围工作的男性/女性的百分比，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36873596/