34,593
社区成员
发帖
与我相关
我的任务
分享
SELECT p.psncode, -- 这是姓名字段?
p.sex
FROM hr_personnal p
JOIN hr_segment s
ON p.deptid = s.deptid
WHERE s.deptname = 'HR'
SELECT p.sex,
COUNT(*) cnt
FROM hr_personnal p
JOIN hr_segment s
ON p.deptid = s.deptid
WHERE s.deptname = 'HR'
GROUP BY p.sex
SELECT psncode,
deptname
FROM (
SELECT p.psncode,
p.grade,
s.deptname,
AVG(grade) OVER(PARTITION BY s.deptid) dept_grade
FROM hr_personnal p
JOIN hr_segment s
ON p.deptid = s.deptid
WHERE p.sex = '女'
) t
WHERE grade < dept_grade -- 是低于部门平均成绩吧
UPDATE hr_segment
SET hr_segment.deptcode = hr_segment_back.deptcode,
hr_segment.deptname = hr_segment_back.deptname
FROM hr_segment, hr_segment_back
WHERE hr_segment.deptid = hr_segment_back.deptid