我该如何在Oracle中编写SQL? [英] How can I write SQL in Oracle in my case?
本文介绍了我该如何在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
只会映射其他两个表.
Here, table person_injuryPlace_map
will just map the both other tables.
我想如何显示数据-
Kitchen Pct Washroom Pct Rooftop Pct Garden Pct
-----------------------------------------------------------------------
1 14.29% 3 42.86% 1 14.29% 2 28.57%
此处,厨房",洗手间",屋顶",花园"列的值是发生的总事件数. Pct列将显示总数的百分比.
Here, the value of Kitchen, Washroom, Rooftop, Garden column is the total incidents happened. Pct columns will show the percentage of the total count.
如何在Oracle SQL中做到这一点?
How can I do this in Oracle SQL?
推荐答案
您需要使用标准的 PIVOT 查询.
You need to use the standard PIVOT query.
根据您的 Oracle数据库版本,您可以通过两种方式进行操作:
Depending on your Oracle database version, you could do it in two ways:
将 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 :
Using MAX and DECODE for version 10g and before:
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?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文