谁能解释这个查询? [英] Can anyone explain this Query?

问题描述

with a as (
select a.*, row_number() over (partition by department order by attributeID) rn
  from attributes a),
e as (
select employeeId, department, attribute1, 1 rn from employees union all
select employeeId, department, attribute2, 2 rn from employees union all
select employeeId, department, attribute3, 3 rn from employees
)
select e.employeeId, a.attributeid, e.department, a.attribute, a.meaning, 
   e.attribute1 as value 
 from e join a on a.department=e.department and a.rn=e.rn 
order by e.employeeId, a.attributeid

此查询由Ponder Stibbons编写，用于回答

this query is written by Ponder Stibbons for the answer of this question. But i am too dizzy with it as i quite don't understand what is going on here. i am new to SQL . so i would appreciate if anyone can explain what is happening on this query . thank you

推荐答案

基本上，他使用3条select语句(每个属性1条)取消数据透视，然后将UNION组合在一起以形成公用表表达式，以便他获取行每个员工属性.

Basically he unpivots the data using 3 select statements (1 for each attribute) and UNION them together to make a common table expression so that he gets rows for each employees attribute.

select employeeId, department, attribute1, 1 rn from employees union all
select employeeId, department, attribute2, 2 rn from employees union all
select employeeId, department, attribute3, 3 rn from employees

他使用窗口函数为属性，部门分配数字的另一张表.稍后，他将使用此数字重新连接到其未透视的数据.他为示例发布了代码.

The other table he using a window function to assign a number to attribute, department. He uses this number later to join back to his unpivoted data. He posted his code for the example.

select a.*, row_number() over (partition by department order by attributeID) rn
  from attributes a

我建议您使用他提供的示例数据并运行以下命令.这将向您显示CTE.我认为，一旦您看到该数据，它将变得更有意义.

I would suggest you use his example data he provided and run the following. This will show you the CTEs. I think once you see that data it will make more sense.

with a as (
select a.*, row_number() over (partition by department order by attributeID) rn
  from attributes a),
e as (
select employeeId, department, attribute1, 1 rn from employees union all
select employeeId, department, attribute2, 2 rn from employees union all
select employeeId, department, attribute3, 3 rn from employees
)

SELECT * from a
SELECT * from e

这篇关于谁能解释这个查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！