在我的案例中如何在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中执行此操作？

您需要使用标准PIVOT查询.

根据您的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相关文章