Oracle row-to-column conversion and Join Summary

In Oracle, you can use the decode function to convert rows to columns. For example, if you have A student table named A: IDNAMESUBJECTSCORE1 Zhang San Language 902 Zhang San mathematics 803 Li Si.

In Oracle, you can use the decode function to convert rows to columns. For example, if you have A student table named A: id name subject score, 1 picture, 3 Chinese, 90 picture, 2 picture, 3 math, 80 picture, and 4 picture.

You can use the decode function to convert columns in Oracle:

If there is A student Table:

IDNAMESUBJECTSCORE

1 Zhang San, Chinese 90

2 Zhang San, mathematics 80

3 Li Si language 99

4 Li Si math 78

Five three English Letters 89

Now you want to convert it to the following table:

NAME Chinese Mathematical English

Zhang San 908089

Li Si 9978

This is a typical row-to-column conversion process. You only need the following SQL:

Select NAME,Sum (decode (SUBJECT, 'China', SCORE, null )),Sum (decode (SUBJECT, 'mat', SCORE, null )),Sum (decode (SUBJECT, 'English ', SCORE, null ))From A group by NAME;

About Join:

1. inner join:

Select * from A a, B B where a. id = B. aId and

Select * from A a inner join B B on a. id = B. aId is the same;

2. left join and left outer join:

Select * from A a left join B B on (a. id = B. aId );

In this case, all records in Table A will be found no matter whether there are any records corresponding to A in Table B;