Oracle Three-Table Association query

Oracle Three-Table Association query creationtime--2018 July 4 17:52 Author:marydon

Left join to implement three-table association

Table A---------------------------------association first table B-----------------------Association second table C

1. Syntax

SELECT * FROM table name A left join table B on A.COLUMNX=B.COLUMNM and A.columny=b.columnn left JOIN Table C on table a= ID of Table C

2. Application Scenarios

Four sheets of Gjpt_basy, Gjzy_basy, Gjpt_basy_error, Gjzy_basy_error
According to four sheets, request return: Medical institution name, medical institution number, total number, number of qualifying and number of questions

3.SQL implementations

SELECTTEMP1.*, TEMP2. Hegesum, TEMP3. Troublesum from(SELECTT1. Ylname, T1. Ylcode,SUM(RS1) asYlsum--Total       from(SELECT COUNT(1) asRS1, hdsd00_11_118 asYlname, hdsd00_11_119 asYlcode fromGjpt_basyGROUP  byhdsd00_11_119, hdsd00_11_118UNION  All        SELECT COUNT(1) asRS1, hdsd00_12_133 asYlname, hdsd00_12_134 asYlcode fromGjzy_basyGROUP  byhdsd00_12_133, hdsd00_12_134UNION  All        SELECT COUNT(1) asRS1, hdsd00_11_118 asYlname, hdsd00_11_119 asYlcode fromGjpt_basy_errorGROUP  byhdsd00_11_119, hdsd00_11_118UNION  All        SELECT COUNT(1) asRS1, hdsd00_12_133 asYlname, hdsd00_12_134 asYlcode fromGjzy_basy_errorGROUP  byhdsd00_12_133, hdsd00_12_134) T1GROUP  byT1. Ylname, T1. Ylcode) TEMP1 Left JOIN(SELECT *            from(SELECTT2. Ylname, T2. Ylcode,SUM(RS2) asHegesum--Number of qualified                from(SELECT COUNT(1) asRS2, hdsd00_11_118 asYlname, hdsd00_11_119 asYlcode fromGjpt_basyGROUP  byhdsd00_11_119, hdsd00_11_118UNION  All                 SELECT COUNT(1) asRS2, hdsd00_12_133 asYlname, hdsd00_12_134 asYlcode fromGjzy_basyGROUP  byhdsd00_12_133, hdsd00_12_134) T2GROUP  byT2. Ylname, T2. Ylcode)) TEMP2 onTEMP2. Ylname=TEMP1. Ylname andTEMP2. Ylcode=TEMP1. Ylcode Left JOIN(SELECT *            from(SELECTT3. Ylname, T3. Ylcode,SUM(RS3) Troublesum--Number of questions                from(SELECT COUNT(1) asRS3, hdsd00_11_118 asYlname, hdsd00_11_119 asYlcode fromGjpt_basy_errorGROUP  byhdsd00_11_119, hdsd00_11_118UNION  All                 SELECT COUNT(1) asRS3, hdsd00_12_133 asYlname, hdsd00_12_134 asYlcode fromGjzy_basy_errorGROUP  byhdsd00_12_133, hdsd00_12_134) T3GROUP  byT3. Ylname, T3. Ylcode)) TEMP3 onTEMP3. Ylname=TEMP1. Ylname andTEMP3. Ylcode=TEMP1. YlcodeWHERETEMP3. Ylcode='41580781841010511a1001';

Related recommendations:

• Oracle Two-Table Association query

Oracle Three-Table Association query