2023-05-26 22:31 扬州大学 数据分析师
关注
牛客刷题 SQL193
统计回答过教育类问题的用户里有多少用户回答过职场类问题
https://www.nowcoder.com/share/jump/8193419321685109477515
错误点：一开始的思路是想统计issue_type='Education' AND issue_type='Career' 的用户数，但是数据表中的对应关系每个记录对应一个问题类型，未仔细查看数据表，思路有误
思路1：找到回答教育类问题(WHERE)的id(去重！)，再子查询找到回答职场类问题的用户，只对出现在子查询中的用户名计数
代码：
SELECT
count(distinct author_id)
FROM
issue_tb AS a1
LEFT JOIN answer_tb AS a2 ON a1.issue_id = a2.issue_id
WHERE
issue_type = 'Education'
AND author_id in (
SELECT
author_id
from
issue_tb AS a1
LEFT JOIN answer_tb AS a2 ON a1.issue_id = a2.issue_id
WHERE
issue_type = 'Career'
)
https://www.nowcoder.com/share/jump/8193419321685109477515
错误点：一开始的思路是想统计issue_type='Education' AND issue_type='Career' 的用户数，但是数据表中的对应关系每个记录对应一个问题类型，未仔细查看数据表，思路有误
思路1：找到回答教育类问题(WHERE)的id(去重！)，再子查询找到回答职场类问题的用户，只对出现在子查询中的用户名计数
代码：
SELECT
count(distinct author_id)
FROM
issue_tb AS a1
LEFT JOIN answer_tb AS a2 ON a1.issue_id = a2.issue_id
WHERE
issue_type = 'Education'
AND author_id in (
SELECT
author_id
from
issue_tb AS a1
LEFT JOIN answer_tb AS a2 ON a1.issue_id = a2.issue_id
WHERE
issue_type = 'Career'
)
全部评论
粥粥今天有实习了吗 楼主
楼主 扬州大学 数据分析师
思路2：利用行转列，得到每个用户作答教育类和职业类问题的数量(子查询部分)，对教育类和职业类作答数量都不为零的用户计数，两个方法都可以通过，对SQL底层不明确，思路2可能更耗费时间
SELECT
count(*) as num
FROM
(
SELECT DISTINCT
author_id,
sum(
CASE
WHEN issue_type = 'Education' THEN 1
ELSE 0
END
) AS Education,
sum(
CASE
WHEN issue_type = 'Career' THEN 1
ELSE 0
END
) AS Career
FROM
issue_tb AS a1
LEFT JOIN answer_tb AS a2 ON a1.issue_id = a2.issue_id
GROUP BY
author_id
) AS t1
WHERE
Education != 0
AND Career != 0
相关推荐
05-30 10:59
海口当伍网络科技有限公司
| 实习
| 39个岗位
点赞 评论 收藏
分享
04-15 15:01
南京理工大学 C++ 
点赞 评论 收藏
分享