Oracle-sql (b)

createtime--2017 June 1 14:36:37
Author:marydon

One, SQL statements

(b) Promotion

1.2.1 Left connection + paged query

Situation: the class table and the student table are related to each other, query the student information and the class

Method One: Use the left connection

SELECT T.*   from (SELECT S.*, c.clazzname, ROWNUM ROWNO           from STUDENT3 S            Left JOIN CLAZZ3 C              on = C.clazzid          WHERE <=  WHERE>= ?

updatetime--2017 January 22 14:06:25
Method Two: Use (+)

SELECT T.*   from (SELECT S.*, c.clazzname, ROWNUM ROWNO           from STUDENT3 S, CLAZZ3 C          WHERE = C.clazzid (+)           and<=WHERE>=  ?

Attention:

A. Only the Oracle database can use (+) to replace the left and right connections;
B. " (+) "On the right of" = "means: Left connection, such as: S.clazzid = C.clazzid (+); Left table (student table) is the main table;
C. " (+) "On the left of" = "means: Right connection, such as: S.clazzid (+) = C.clazzid; Right table (class table) is the main table.

Method Three: From behind and two tables

SELECT T.*   from (SELECT S.*, c.clazzname, ROWNUM ROWNO     from STUDENT3 S, CLAZZ3 C    WHERE = C.clazzid       and <=  WHERE>= ?

1.2.2 left connection implements three-table association

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

Grammar:

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

Scene:

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

SQL implementation:

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';

1.2.3 pagination, grouping, counting, sorting

/** * Return Data * Forgid medical institution id* Frcode County Code * Forgname Medical Institution name * Fcenname_tem_count Name Asymmetric count * Isnullcencode_count non-control count * Totalcoun How many data are in the T l_diaitem_error table*/SELECTT3.* from(SELECTT2.*, ROWNUM ROWNO from(SELECTT.forgid, T1. Frcode, T1. Forgname,SUM(NVL (T.NCCW,0)) Fcenname_tem_count,SUM(NVL (T.wdz,0)) Isnullcencode_count, TotalCount froml_diaitem_error T, torganization T1WHERET.forgid=T1. ForgidGROUP  byT.forgid, T1. Forgname, T1. Forgseq, T1. FrcodeORDER  byT1. FORGSEQ) T2WHEREROWNUM<=v_end) T3WHERET3. ROWNO>=V_start;

Attention:

A.TotalCount is a variable that already exists and is not the focus of attention here;

B. data for group BY, there are only two ways to query a field in a table:

Way One:

　　　　　　　　　　Add the fields you want to query after group by

Way two:

　　　　　　　　　　For numeric columns, use the SUM function sum () to implement the query

　　　　　　Otherwise, an error occurs: Not a GROUP by expression

Oracle-sql (b)