大佬教程收集整理的这篇文章主要介绍了在我的案例中如何在Oracle中编写SQL？，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
create table person ( id number,name varchar2(50) ); create table injury_place ( id number,place varchar2(50) ); create table person_injuryPlace_map ( person_id number,injury_id number ); insert into person values (1,'Adam'); insert into person values (2,'Lohan'); insert into person values (3,'Mary'); insert into person values (4,'John'); insert into person values (5,'Sam'); insert into injury_place values (1,'kitchen'); insert into injury_place values (2,'Washroom'); insert into injury_place values (3,'Rooftop'); insert into injury_place values (4,'Garden'); insert into person_injuryPlace_map values (1,2); insert into person_injuryPlace_map values (2,3); insert into person_injuryPlace_map values (1,4); insert into person_injuryPlace_map values (3,2); insert into person_injuryPlace_map values (4,4); insert into person_injuryPlace_map values (5,2); insert into person_injuryPlace_map values (1,1);
这里,表person_injuryPlace_map将只映射其他两个表.
我想如何显示数据 –
Kitchen Pct Washroom Pct Rooftop Pct Garden Pct ----------------------------------------------------------------------- 1 14.29% 3 42.86% 1 14.29% 2 28.57%
在这里,厨房,洗手间,屋顶,花园柱的价值是发生的总事故. Pct列将显示总计数的百分比.
我怎样才能在Oracle SQL中执行此操作？
根据您的Oracle数据库版本,您可以通过两种方式执行此操作：
使用PIVOT版本11g及更高版本：
sql> SELECT * 2 FROM 3 (SELECT c.place place,4 row_number() OVER(PARTITION BY c.place ORDER BY NulL) cnt,5 (row_number() OVER(PARTITION BY c.place ORDER BY NulL)/ 6 COUNT(placE) OVER(ORDER BY NulL))*100 pct 7 FROM person_injuryPlace_map A 8 JOIN person b 9 ON(A.person_ID = b.ID) 10 JOIN injury_place c 11 ON(A.injury_ID = c.ID) 12 ORDER BY c.place 13 ) PIVOT (MAX(cnt),14 MAX(pct) pct 15 FOR (placE) IN ('kitchen' AS kitchen,16 'Washroom' AS Washroom,17 'Rooftop' AS Rooftop,18 'Garden' AS Garden)); KITCHEN KITCHEN_PCT WASHROOM WASHROOM_PCT ROOFtop ROOFtop_PCT GARDEN GARDEN_PCT ---------- ----------- ---------- ------------ ---------- ----------- ---------- ---------- 1 14.2857143 3 42.8571429 1 14.2857143 2 28.5714286
使用MAX和DECODE版本10g及之前：
sql> SELECT MAX(DECODE(t.place,'kitchen',cnt)) Kitchen,2 MAX(DECODE(t.place,pct)) Pct,3 MAX(DECODE(t.place,'Washroom',cnt)) Washroom,4 MAX(DECODE(t.place,5 MAX(DECODE(t.place,'Rooftop',cnt)) Rooftop,6 MAX(DECODE(t.place,7 MAX(DECODE(t.place,'Garden',cnt)) Garden,8 MAX(DECODE(t.place,pct)) Pct 9 FROM 10 (SELECT b.ID bID,11 b.name name,12 c.ID cID,13 c.place place,14 row_number() OVER(PARTITION BY c.place ORDER BY NulL) cnt,15 round((row_number() OVER(PARTITION BY c.place ORDER BY NulL)/ 16 COUNT(placE) OVER(ORDER BY NulL))*100,2) pct 17 FROM person_injuryPlace_map A 18 JOIN person b 19 ON(A.person_ID = b.ID) 20 JOIN injury_place c 21 ON(A.injury_ID = c.ID) 22 ORDER BY c.place 23 ) t; KITCHEN PCT WASHROOM PCT ROOFtop PCT GARDEN PCT ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 14.29 3 42.86 1 14.29 2 28.57
以上是大佬教程为你收集整理的在我的案例中如何在Oracle中编写SQL？全部内容，希望文章能够帮你解决在我的案例中如何在Oracle中编写SQL？所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。