3个表表一是部门表有字段1.部门号,2部门名表二是员工信息表有字段1,部门号(外键)2员工号,3员工名,4员工民族5.员工年龄6员工现在职位表三是职位更换表有三个字段1.员工号,2员工以前职位,3员工现在职位员工职位每变动一次,就向这个表插入一条数据。现在求(必须用一条sql语句实现)1,职位变动大于2次的员工的部门名以及员工名以及变动次数2.年龄为20-30的员工为多少人,30-40有多少人40-50有多少人50以上的几人思考半天没有头绪希望高手解答
调试欢乐多
第一题:SELECT T3.部门名,T4.员工名,COUNT(1) AS 变动次数
FROM 职位更换表 T1,职位更换表 T2,部门表 T3,员工信息表 T4
WHERE T1.员工现在职位 = T2.员工以前职位
AND T1.员工号=T2.员工号
AND T1.员工号=T4.员工号
AND T3.部门号=T4.部门号
GROUP BY T3.部门名,T4.员工名
HAVING COUNT(1)>2第二题:
SELECT SUM(CASE WHEN T1.员工年龄 BETWEEN 20 AND 30 THEN 1 ELSE 0 END) AS 20-30的员工,
SUM(CASE WHEN T1.员工年龄 BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS 31-40的员工,
SUM(CASE WHEN T1.员工年龄 BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS 41-50的员工,
SUM(CASE WHEN T1.员工年龄 >50 THEN 1 ELSE 0 END) AS 50以上的员工
FROM 员工信息表 T1写错了,请帮忙改正,如果有现成的数据那就好办了。
select t1.部門號,t2.員工姓名,a.變動資料
(
select 員工號,count(1) 變動次數
from t3
group by 員工號 having count(1)>2
)a
left join t2 on t2.員工號=a.員工號
left join t1 on t1.部門號=t2.部門號
SELECT J2.部门名,J3.员工名,J1.变动次数
(SELECT T1.员工号,MAX(DENSE_RANK() OVER(PARTITION BY T1.员工号)) AS 变动次数
FROM 职位更换表 T1
GROUP BY T1.员工号) J1,部门表 J2,员工信息表 J3
WHERE J1.员工号=J3.员工号 AND J3.部门号 = J2.部门号
AND J1.变动次数>2不知道这样写对不对。呵呵
表二.员工名
from 表一,表二,(select 员工号,
count(员工号) 次数
from 表三
group by 员工号) 表四
where 表一.部门号 = 表二.部门号
and 表二.员工号 = 表四.员工号
and 表四.次数 > 2
FROM (SELECT T1.员工号,MAX(T1.变动次数) AS 变动次数
FROM (SELECT T1.员工号,DENSE_RANK() OVER(PARTITION BY T1.员工号) AS 变动次数
FROM 职位更换表 T1) T1
GROUP BY T1.员工号) J1,部门表 J2,员工信息表 J3
WHERE J1.员工号 = J3.员工号
AND J3.部门号 = J2.部门号
AND J1.变动次数 > 2
上面的那个有错误,现改正。
departId, departName2: Employee <table>
departId, empId,empName,empNat,empAge,empJob3: Job <table>
empId, oldJob, newJobselect d.departName,e.empId,count(*)
from Department d,
Employee e,
Job j
where e.empId = j.empId
and e.departId = d.departId
group by e.empId
having count(*) > 2;
select sum(case when e.empAge>= 20 and e.empAge < 30 then 1 else 0 end) as "20-30 Emp Age",
sum(case when e.empAge>= 30 and e.empAge < 40 then 1 else 0 end) as "30-40 Emp Age",
sum(case when e.empAge>= 40 and e.empAge < 50 then 1 else 0 end) as "40-50 Emp Age",
sum(case when e.empAge>= 50 then 1 else 0 end) as "above 50 Emp Age"
from Employee e;
FROM (SELECT T2.员工号,MAX(T2.变动次数) AS 变动次数
FROM (SELECT T1.员工号,DENSE_RANK() OVER(PARTITION BY T1.员工号 ORDER BY T1.员工以前职位) AS 变动次数
FROM 职位更换表 T1) T2
GROUP BY T1.员工号) J1,部门表 J2,员工信息表 J3
WHERE J1.员工号 = J3.员工号
AND J3.部门号 = J2.部门号
AND J1.变动次数 > 2