首页 > 试题广场 >

统计每种性别的人数

[编程题]统计每种性别的人数
  • 热度指数：294456 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

示例：user_submit
device_id profile blog_url
2138 180cm,75kg,27,male http:/url/bigboy777
3214 165cm,45kg,26,female http:/url/kittycc
6543 178cm,65kg,25,male http:/url/tiger
4321 171cm,55kg,23,female http:/url/uhksd
2131 168cm,45kg,22,female http:/urlsydney

根据示例，你的查询应返回以下结果：
gender number
male 2
female 3

示例1

输入

drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');

输出

male|2
female|3
算法知识视频讲解
添加回答
Mysql
牛客605422071号头像 牛客605422071号
SELECT
    CASE
        WHEN profile LIKE '%,male' THEN 'male'
        WHEN profile LIKE '%,female' THEN 'female'
    END AS gender,
    COUNT(*) AS number
FROM
    user_submit
GROUP BY
    gender;


SELECT
    IF(u.profile LIKE '%,female', 'female', 'male') AS gender,
    COUNT(1) AS number
FROM
    user_submit u
WHERE
    u.profile LIKE '%,female'
    OR u.profile LIKE '%,male'
GROUP BY
    gender;

发表于 2025-12-24 10:45:04 回复(0)
Mysql
郝孝德头像 郝孝德
select
case
when right(profile,5)=",male"
then "male"
ELSE "female"
end as gender,
count(*) number
from user_submit
group by gender
发表于 2025-12-03 14:48:22 回复(0)
Mysql
yyyyy123456头像 yyyyy123456
为什么不能用
count( disitnct device_id) as number
发表于 2025-11-10 11:10:45 回复(0)
Mysql
刘的蓝图头像 刘的蓝图
-- 切割文本，取出特定的值

select substring_index(profile, ',', -1) as gender
    , count(1) as number
from
    user_submit
group by
    substring_index(profile, ',', -1)
;
发表于 2025-10-29 18:21:22 回复(0)
Mysql
richielw头像 richielw
select  'male' as gender,
count(case when profile regexp',male' then 1 end) as number
from user_submit
union all
select  'female' as gender,
count(case when profile regexp',female' then 1 end) as femalenum
from user_submit

运用正则表达式也是另外一种选择
发表于 2025-10-21 09:42:33 回复(0)
Mysql
在游泳的无尾熊很emo头像 在游泳的无尾熊很emo
select
case when profile regexp 'female' then 'female'
else 'male'
end as gender,
count(device_id) as number
from user_submit
group by gender
发表于 2025-10-16 11:08:02 回复(0)
Mysql
野蛮的熊熊求你们别卷了头像 野蛮的熊熊求你们别卷了
select case when (profile like '%,male') then 'male' else 'female' end as gender,count(device_id)
from user_submit
group by gender;
发表于 2025-08-14 10:23:54 回复(0)
Mysql
牛客113910245号头像 牛客113910245号
select gender, count(gender) as number
from
(
    select
        case
            when profile like '%,male' then 'male'
            when profile like '%,female' then 'female'
            else ''
        end as gender
    from
        user_submit
) u1
group by gender
发表于 2025-07-23 17:30:08 回复(0)
Mysql
牛客733881433号头像 牛客733881433号 
select
    case
        when profile regexp 'female$' then 'female'
        else 'male'
    end as gender,
    COUNT(*) as number
from
    user_submit
GROUP BY
    gender
发表于 2025-07-16 23:40:56 回复(0)
Mysql
年薪百万的柯基很有胆量头像 年薪百万的柯基很有胆量 
select 
substring(profile,15) as gender,
count(device_id) as number
from user_submit
group by gender

发表于 2025-07-16 16:39:38 回复(0)
Mysql
牛客529794785号头像 牛客529794785号 


select 
    substring_index(profile, ',', -1) as gender,
    count(*) as number
from user_submit
group by gender

发表于 2025-06-28 16:57:36 回复(0)
Mysql
追烽头像 追烽 
select
    case 
        when profile like '%female'
        then 'female'
        else 'male'
    end as gender,
    count(*) as number
from user_submit
group by gender
发表于 2025-06-14 16:04:57 回复(0)
Mysql
活跃在圈中外头像 活跃在圈中外
select substr(profile,15) as gender,count(1) as number
      from user_submit
      group by substr(profile,15)
发表于 2025-06-13 15:34:49 回复(0)
Mysql
丞相何故发笑头像 丞相何故发笑
select substring_index(profile,',',-1) as gender,count(*) as number
from user_submit
group by  gender
发表于 2025-05-27 16:44:37 回复(0)
Mysql
吃不饱的小熊猫很帅气头像 吃不饱的小熊猫很帅气
select
    case
        when profile like '%female%' then 'female'
        else 'male'
    end as gender,
    count(profile) as number
from
    user_submit
group by
    gender;
发表于 2025-05-15 18:29:29 回复(0)
Mysql
神奇的海螺在抱佛脚头像 神奇的海螺在抱佛脚
select substring(profile,15) as gender,
count(device_id) as number
from user_submit
group by gender
发表于 2025-05-07 21:33:11 回复(0)
Mysql
东方宸星头像 东方宸星
法一：LOCATE函数 
SELECT 
    IF(LOCATE(',male', profile) > 0, 'male', 'female') AS gender, 
    COUNT(*) AS number
FROM 
    user_submit 
GROUP BY 
    IF(LOCATE(',male', profile) > 0, 'male', 'female')
法二：SUBSTRING_INDEX函数 
SELECT 
    SUBSTRING_INDEX(profile, ',', -1) AS gender, 
    COUNT(*) AS number
FROM 
    user_submit 
GROUP BY 
    SUBSTRING_INDEX(profile, ',', -1)



发表于 2025-04-08 18:12:31 回复(0)
Mysql
可以k头像 可以k
SELECT
case
when profile LIKE "%,male" then "male"
when profile LIKE "%,female" then "female"
END gender,
count(*) NUMBER
FROM
user_submit
GROUP BY
gender
发表于 2025-03-06 13:17:31 回复(0)
Mysql
糊涂的乌龟在改简历头像 糊涂的乌龟在改简历
有三种方法
方法一：substring_index() 
select
    substring_index(profile,',', -1) gender,
    count(*) number 
from user_submit
group by gender
方法二：case when then end 与like结合 
select case when profile like "%,male" then "male"  
            when profile like "%,female" then "female" end gender,
        count(*)number
from user_submit
group by gender
方法三：if与like结合 
select if(profile like"%female","female","male") gender,
        count(*) number
from user_submit
group by gender

发表于 2025-03-04 11:07:29 回复(0)
提交观点

问题信息

SQL
难度：
310条回答 4757浏览

热门推荐

通过挑战的用户

查看代码

相关试题

统计每种性别的人数

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号