oracle中sql案例,在我的案例中如何在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,'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