watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

Q           The title is as follows ：

   There is a table in the database as shown below , Table, sales.

year

quarter

Sales volume

1991

1

11

1991

2

12

1991

3

13

1991

4

14

1992

1

21

1992

2

22

1992

3

23

1992

4

24

requirement ： Write a SQL Statement to query the result as shown below .

year

First quarter

The two quarter

third quater

In the fourth quarter

1991

11

12

13

14

1992

21

22

23

24

     

A           The answer is as follows ：          

       

    

    The environment is as follows ：

  
                 
                 

  1. CREATE TABLE sales( year NUMBER, quarter NUMBER , Sales volume NUMBER);

  2. INSERT INTO sales VALUES(1991, 1 ,11);

  3. INSERT INTO sales VALUES(1991, 2 ,12);

  4. INSERT INTO sales VALUES(1991, 3 ,13);

  5. INSERT INTO sales VALUES(1991, 4 ,14);

  6. INSERT INTO sales VALUES(1992, 1 ,21);

  7. INSERT INTO sales VALUES(1992, 2 ,22);

  8. INSERT INTO sales VALUES(1992, 3 ,23);

  9. INSERT INTO sales VALUES(1992, 4 ,24);

  11. SELECT * FROM sales;

  12. ————————————

  13.     year quarter Sales volume

  14. 1 1991 1 11

  15. 2 1991 2 12

  16. 3 1991 3 13

  17. 4 1991 4 14

  18. 5 1992 1 21

  19. 6 1992 2 22

  20. 7 1992 3 23

  21. 8 1992 4 24

 

  This is a row column problem , Using aggregate functions +DECODE or CASE Just answer , As shown below

：

  
                  
                  SELECT  year ,  SUM(CASE WHEN  quarter =1 THEN  Sales volume  ELSE 0 END) AS  First quarter , SUM(CASE WHEN  quarter =2 THEN  Sales volume  ELSE 0 END) AS  The two quarter , SUM(CASE WHEN  quarter =3 THEN  Sales volume  ELSE 0 END) AS  third quater , SUM(CASE WHEN  quarter =4 THEN  Sales volume  ELSE 0 END) AS  In the fourth quarter FROM SALES GROUP BY  year ;

 

     If you use this question PIVOT function , As shown below ：

  
                  
                  SELECT *  FROM SALESPIVOT(SUM( Sales volume )   FOR  quarter  IN(1 AS " First quarter ", 2 AS " The two quarter ", 3 AS " third quater ", 4 AS " In the fourth quarter ")) ORDER BY 1;

     If you use a temporary table , As shown below ：

  
                  
                  SELECT T. year ,       NVL(SUM(T1. First quarter ),0) AS " First quarter ",       NVL(SUM(T2. The two quarter ),0) AS " The two quarter ",       NVL(SUM(T3. third quater ),0) AS " third quater ",       NVL(SUM(T4. In the fourth quarter ),0) AS " In the fourth quarter "  FROM (SELECT  year , Sales volume  AS " First quarter " FROM SALES A WHERE A. quarter  = '1') T1,       (SELECT  year , Sales volume  AS " The two quarter " FROM SALES A WHERE A. quarter  = '2') T2,        (SELECT  year , Sales volume  AS " third quater " FROM SALES A WHERE A. quarter  = '3') T3,        (SELECT  year , Sales volume  AS " In the fourth quarter " FROM SALES A WHERE A. quarter  = '4') T4,       (SELECT DISTINCT  year  FROM SALES) T WHERE T. year  = T1. year (+)   AND T. year  = T2. year (+)   AND T. year  = T3. year (+)   AND T. year  = T4. year (+) GROUP BY T. year  ORDER BY 1;