题目如图,这个sql想了好久了,不知道怎么实现,求大神解答!!
解决方案 »
- 救命啊!!
- 1.字段相同记录只显示一个,后面显示空;2.grouping中subtotal显示数字的问题
- 关于添加日期的时分秒问题
- show parameter
- 关于ORACLE的utl_file包的资料
- ORACLE存储过程中的ELSE IF语句怎么写,我这样写为何报错?应该怎么写?
- 加了一个实例出了问题,急,在线等!
- oracle 8i安装时,setup线程自动消失?
- 在建立OBJECT时会出现pls-00103错误!在线等待!
- 讲讲表空间问题!
- JAVA BEAN有个属性long类型,oracle表中对应的字段也是long类型,但是HQL查询出来放在list里出错,应该怎么处理这个字段的问题?
- ORA-12154:TNS:无法解析制定的连接标识符
这个问题使用Oracle中的mode子句可以解决,
mode子句可以进行行转列,在多维数据库中经常使用。
with A as (select 1 id,'流水线A' LiushuiNm,10001 ProductNo,'产品A' ProductNm,8 num union all
select 2 id,'流水线B' LiushuiNm,10001 ProductNo,'产品A' ProductNm,5 num union all
select 3 id,'流水线C' LiushuiNm,10002 ProductNo,'产品B' ProductNm,7 num union all
select 4 id,'流水线D' LiushuiNm,10002 ProductNo,'产品B' ProductNm,12 num union all
select 5 id,'流水线E' LiushuiNm,10003 ProductNo,'产品C' ProductNm,9 num)
select ProductNo,ProductNm,
sum(case when LishuiNm='流水线A' then num else 0 end) Liushui_A,
sum(case when LishuiNm='流水线B' then num else 0 end) Liushui_B,
sum(case when LishuiNm='流水线C' then num else 0 end) Liushui_C,
sum(case when LishuiNm='流水线D' then num else 0 end) Liushui_D,
sum(case when LishuiNm='流水线E' then num else 0 end) Liushui_E
from A
group by ProductNo,ProductNm
order by ProductNo,ProductNm
select
p.productno,p.productnm,
sum(decode(p.liushuinm,'流水线A',num,0)) 流水线A,
sum(decode(p.liushuinm,'流水线B',num,0)) 流水线B,
sum(decode(p.liushuinm,'流水线C',num,0)) 流水线C,
sum(decode(p.liushuinm,'流水线D',num,0)) 流水线D,
sum(decode(p.liushuinm,'流水线E',num,0)) 流水线E
from product p
group by p.productno,p.productnm order by p.productno;常见的查询方法:使用decode;
如果你用的Oracle是10g以上,可以使用mode子句(正在学习中)。
建表语句:create table stock
(
ID NUMBER,
LIUSHUINM NVARCHAR2(20),
PRODUCTNO NVARCHAR2(20),
PRODUCTNM NVARCHAR2(20),
NUM NUMBER
)插入数据语句:
insert into stock (ID, LIUSHUINM, PRODUCTNO, PRODUCTNM, NUM)
values (1, '流水线A', '10001', '产品A', 8);insert into stock (ID, LIUSHUINM, PRODUCTNO, PRODUCTNM, NUM)
values (2, '流水线A', '10001', '产品A', 5);insert into stock (ID, LIUSHUINM, PRODUCTNO, PRODUCTNM, NUM)
values (3, '流水线B', '10002', '产品B', 7);insert into stock (ID, LIUSHUINM, PRODUCTNO, PRODUCTNM, NUM)
values (4, '流水线B', '10002', '产品B', 12);insert into stock (ID, LIUSHUINM, PRODUCTNO, PRODUCTNM, NUM)
values (5, '流水线C', '10003', '产品C', 9);
select p.productno,
p.productnm,
max(decode(p.liushuinm, '流水线A', num, 0)) 流水线A,
max(decode(p.liushuinm, '流水线B', num, 0)) 流水线B,
max(decode(p.liushuinm, '流水线C', num, 0)) 流水线C,
max(decode(p.liushuinm, '流水线D', num, 0)) 流水线D,
max(decode(p.liushuinm, '流水线E', num, 0)) 流水线E
from stock p
group by p.productno, p.productnm
order by p.productno;
select *
from (select productno, productnm, liushuinm, num from stock)
pivot(max(num)
for liushuinm in('流水线A', '流水线B', '流水线C', '流水线D', '流水线E'))
order by productno楼主关于这个问题开了两个贴,楼主是想要什么?
with stock as (select 1 id,'流水线A' LiushuiNm,10001 ProductNo,'产品A' ProductNm,8 num from dual union all
select 2 id,'流水线B' LiushuiNm,10001 ProductNo,'产品A' ProductNm,5 num from dual union all
select 3 id,'流水线C' LiushuiNm,10002 ProductNo,'产品B' ProductNm,7 num from dual union all
select 4 id,'流水线D' LiushuiNm,10002 ProductNo,'产品B' ProductNm,12 num from dual union all
select 5 id,'流水线E' LiushuiNm,10003 ProductNo,'产品C' ProductNm,9 num from dual)
select *
from (select productno, productnm, liushuinm, num from stock)
pivot(max(num)
for liushuinm in('流水线A', '流水线B', '流水线C', '流水线D', '流水线E'))
order by ProductNo,ProductNm
select 2 id,'流水线B' LiushuiNm,10001 ProductNo,'产品A' ProductNm,5 num from dual union all
select 3 id,'流水线C' LiushuiNm,10002 ProductNo,'产品B' ProductNm,7 num from dual union all
select 4 id,'流水线D' LiushuiNm,10002 ProductNo,'产品B' ProductNm,12 num from dual union all
select 5 id,'流水线E' LiushuiNm,10003 ProductNo,'产品C' ProductNm,9 num from dual)
select productno,
productnm,
max((case
when liushuinm = '流水线A' then
num
end)) as 流水线A,
max((case
when liushuinm = '流水线B' then
num
end)) as 流水线B,
max((case
when liushuinm = '流水线C' then
num
end)) as 流水线C,
max((case
when liushuinm = '流水线D' then
num
end)) as 流水线D,
max((case
when liushuinm = '流水线E' then
num
end)) as 流水线E
from stock
group by productno, productnm
order by ProductNo;