这SQL脚本咱写啊 ??求大侠帮忙,急急急

zsclengxue 2014-02-25 10:42:28
有一个表,原始数据如下
R_code Price
N-126 10
N-126 12
N-126 15
N-126 18
N-126 9
N-127 100
N-127 120
N-127 150
N-127 10

现在想要实现如下查询结果:
R_code p1 p2 p3 p4 p5
N-126 9 10 12 15 18
N-127 10 100 120 150
请问脚本咱写啊 ?
...全文
179 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
zsclengxue 2014-02-27
  • 打赏
  • 举报
回复
引用 1 楼 snbxp 的回复:

 
WITH tb AS 
(
select 'N-126' r_code, 10   price from dual union all
select 'N-126' r_code, 12   price from dual union all
select 'N-126' r_code, 15   price from dual union all
select 'N-126' r_code, 18   price from dual union all
select 'N-126' r_code, 9    price from dual union all
select 'N-127' r_code, 100  price from dual union all
select 'N-127' r_code, 120  price from dual union all
select 'N-127' r_code, 150  price from dual union all
select 'N-127' r_code, 10   price from dual 
)

SELECT r_code,
       SUM(DECODE(s,1,price,NULL)) P1,
       SUM(DECODE(s,2,price,NULL)) P2,
       SUM(DECODE(s,3,price,NULL)) P3,
       SUM(DECODE(s,4,price,NULL)) P4,
       SUM(DECODE(s,5,price,NULL)) P5
FROM        
(SELECT R_CODE,
       PRICE,
       ROW_NUMBER() OVER(PARTITION BY R_CODE ORDER BY PRICE) S
  FROM TB
)
GROUP BY r_code
zsclengxue 2014-02-27
  • 打赏
  • 举报
回复
引用 楼主 zsclengxue 的回复:
有一个表,原始数据如下 R_code Price N-126 10 N-126 12 N-126 15 N-126 18 N-126 9 N-127 100 N-127 120 N-127 150 N-127 10 现在想要实现如下查询结果: R_code p1 p2 p3 p4 p5 N-126 9 10 12 15 18 N-127 10 100 120 150 请问脚本咱写啊 ?
snbxp 2014-02-26
  • 打赏
  • 举报
回复

 
WITH tb AS 
(
select 'N-126' r_code, 10   price from dual union all
select 'N-126' r_code, 12   price from dual union all
select 'N-126' r_code, 15   price from dual union all
select 'N-126' r_code, 18   price from dual union all
select 'N-126' r_code, 9    price from dual union all
select 'N-127' r_code, 100  price from dual union all
select 'N-127' r_code, 120  price from dual union all
select 'N-127' r_code, 150  price from dual union all
select 'N-127' r_code, 10   price from dual 
)

SELECT r_code,
       SUM(DECODE(s,1,price,NULL)) P1,
       SUM(DECODE(s,2,price,NULL)) P2,
       SUM(DECODE(s,3,price,NULL)) P3,
       SUM(DECODE(s,4,price,NULL)) P4,
       SUM(DECODE(s,5,price,NULL)) P5
FROM        
(SELECT R_CODE,
       PRICE,
       ROW_NUMBER() OVER(PARTITION BY R_CODE ORDER BY PRICE) S
  FROM TB
)
GROUP BY r_code

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧