SQL ORACLE UNPIVOT [英] SQL ORACLE UNPIVOT

问题描述

我无法处理这种毫无争议的事情

i can not deal with this unpivot

select *
    from (select c.country_id, r.region_id, count(*) liczba
    from countries c join regions r on r.region_id = c.region_id
    group by c.country_id, r.region_id)
    UnPivot(
         count(liczba) for r.region_id in (any))
         order by c.country_id

这是代码，仍然无法使用:(

this is code, and still will not work :(

select r.region_name, count(*)
from countries c join regions r on r.region_id = c.region_id
group by r.region_name

此功能正常. 在此处输入图片描述

无效标识符"

在此处输入图片描述

第二个想象！

推荐答案

您的查询没有任何意义，因为:

Your query does not make sense as:

• UNPIVOT用于将列转置为行；您只有3列country_id，region_id和liczba.

• UNPIVOT语法为:

• UNPIVOT is used to transpose columns into rows; you only have 3 columns country_id, region_id and liczba.

• The UNPIVOT syntax is:

UNPIVOT ( value FOR key IN ( column1 AS 'alias1', column2 AS 'alias2' ) )

您不能将聚合表达式作为值.

You cannot have a aggregation expression as the value.

您似乎想使用PIVOT而不是UNPIVOT.

我想看看，每个job_id中有多少人工作

I would like to see, how many people work in each job_id

使用GROUP BY和COUNT:

SELECT job_id,
       COUNT(DISTINCT EMPLOYEE_ID) AS number_of_employees
FROM   employees
GROUP BY job_id

使用不可旋转

using unpivot

这不是UNPIVOT的目的； UNPIVOT将列转换为行，而您希望将行聚合在一起.

That's not what UNPIVOT is for; UNPIVOT converts columns to rows whereas you want to aggregate rows together.

这篇关于SQL ORACLE UNPIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！