把纵列的相同数据分组成不同的横列

最新推荐文章于 2021-12-13 08:50:10 发布

iteye_14791

最新推荐文章于 2021-12-13 08:50:10 发布

阅读量268

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/iteye_14791/article/details/81972532

本文提供了一个复杂的SQL查询案例，用于从数据库中选择特定的数据并按不同部分进行聚合。该查询使用了多个子查询、联接操作、解码函数以及窗口函数等高级特性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 select 
        CARRIERACCOUNDCODE, 
        max(decode(sectionId, 1,sectionId)) id1, 
        max(decode(sectionId, 1, setionName)) name1,

        max(decode(sectionId, 2,sectionId)) id2, 
        max(decode(sectionId, 2, setionName)) name2 ,

        max(decode(sectionId, 3,sectionId)) id3, 
        max(decode(sectionId, 3, setionName)) name3,

        max(decode(sectionId, 4,sectionId)) id4,
        max(decode(sectionId, 4, setionName)) name4,
        
         max(decode(sectionId, 5,sectionId)) id5,
        max(decode(sectionId, 5, setionName)) name5,
        
         max(decode(sectionId, 6,sectionId)) id6,
        max(decode(sectionId, 6, setionName)) name6
from (
        select 
            CARRIERACCOUNDCODE, 
            setionName,
            sectionId,
            row_number() over(partition by CARRIERACCOUNDCODE order by sectionId) rn, 
            count(*) over(partition by CARRIERACCOUNDCODE) cnt 

        from (SELECT t2.CARRIERACCOUNDCODE,
                        t2.sectionId,
                        t2.setionName
                FROM    carrier t1
                     LEFT JOIN
                        funcinfo t2
                     ON t1.CARRIERACCOUNTCODE = t2.CARRIERACCOUNDCODE 
                     where CARRIERACCOUNDCODE is not null
                 )
        )
  
     group by CARRIERACCOUNDCODE;