更新时间:2022-12-11 22:33:17
你正在以一个固定值,字符串字面值'RM'
进行循环,所以你真的没有做任何有用的枢轴 - 输出是相同的,自己运行'pivot_data'查询:
You are pivoting on a fixed value, the string literal 'RM'
, so you're really not doing anything useful in the pivot - the output is the same as you'd get from running the 'pivot_data' query on its own:
SELECT eNAME,workhrs,room, SCR from PRODUCTIVITY p,PRODUCTIVITYd d, emp e, ROOMS R
where p.PRODUCTIVITYID=d.PRODUCTIVITYID and e.empno=p.employeeid
AND R.ID=P.ROOMID;
ENAME WORKHRS ROOM SCR
----- ---------- ---------- ----------
JONES 3.6 101 53
ALLEN 1.32 101 43
ALLEN 6 102 22
您希望每个员工的总额 workhrs
以及他们售出的房间的枢纽。如果您更改该查询以获得 workhrs
的分析和,以及房间/ scr值的排名(并使用现代连接语法),您将获得:
You want the aggregate workhrs
for each employee, and a pivot of the rooms they sold. If you change that query to get the analytic sum of workhrs
and a ranking of the room/scr values (and using modern join syntax) you get:
select e.ename, r.room, p.scr,
sum(d.workhrs) over (partition by e.ename) as wrkhrs,
rank() over (partition by e.ename order by r.room, p.scr) as rnk
from productivity p
join productivityd d on d.productivityid = p.productivityid
join emp e on e.empno=p.employeeid
join rooms r on r.id = p.roomid;
ENAME ROOM SCR WRKHRS RNK
----- ---------- ---------- ---------- ----------
ALLEN 101 43 7.32 1
ALLEN 102 22 7.32 2
JONES 101 53 3.6 1
然后,您可以关注生成的 rnk
号码:
You can then pivot on that generated rnk
number:
with pivot_data as (
select e.ename, r.room, p.scr,
sum(d.workhrs) over (partition by e.ename) as wrkhrs,
rank() over (partition by e.ename order by r.room, p.scr) as rnk
from productivity p
join productivityd d on d.productivityid = p.productivityid
join emp e on e.empno=p.employeeid
join rooms r on r.id = p.roomid
)
select *
from pivot_data
pivot (
min(room) as room, min(scr) as scr --<-- pivot_clause
for rnk --<-- pivot_for_clause
in (1, 2, 3) --<-- pivot_in_clause
);
ENAME WRKHRS 1_ROOM 1_SCR 2_ROOM 2_SCR 3_ROOM 3_SCR
----- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ALLEN 7.32 101 43 102 22
JONES 3.6 101 53
您需要知道任何员工可能拥有的房间 - 即最高的 rnk
可能是 - 并且包括 in
这意味着您可能最终会出现空列,如本示例中没有 3_room
或 3_scr $ c $的数据c>。你不能避免这种情况,除非你得到一个XML结果或动态生成查询。
You need to know the maximum number of rooms any employee may have - i.e. the highest rnk
could ever be - and include all of those in the in
clause. Which means you're likely to end up with empty columns, as in this example where there is no data for 3_room
or 3_scr
. You can't avoid that though, unless you get an XML result or generate the query dynamically.