我该如何在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%
在这里，“厨房”，“洗手间”，“屋顶”，“花园”列的值是发生的事件总数。“百分比”列将显示总计数的百分比。
如何在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
在 版本10g* 及之前的 版本中 使用 MAX 和 DECODE ： *
SQL> SELECT MAX(DECODE(t.place,'kitchen',cnt)) Kitchen ,
  2 MAX(DECODE(t.place,'kitchen',pct)) Pct ,
  3 MAX(DECODE(t.place,'Washroom',cnt)) Washroom ,
  4 MAX(DECODE(t.place,'Washroom',pct)) Pct ,
  5 MAX(DECODE(t.place,'Rooftop',cnt)) Rooftop ,
  6 MAX(DECODE(t.place,'Rooftop',pct)) Pct ,
  7 MAX(DECODE(t.place,'Garden',cnt)) Garden ,
  8 MAX(DECODE(t.place,'Garden',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？

浏览过的版块

红色草 LV1