首页 > 代码库 > ORACLE-SQL(二)

ORACLE-SQL(二)

CreateTime--2017年6月1日14:36:37
Author:Marydon

一、SQL语句

  (二)提升篇

    1.2.1 左连接+分页查询

    情景:班级表和学生表两表关联,查询学生信息及所在班级

      方法一:使用左连接

SELECT T.*  FROM (SELECT S.*, C.CLAZZNAME, ROWNUM ROWNO          FROM STUDENT3 S          LEFT JOIN CLAZZ3 C            ON S.CLAZZID = C.CLAZZID         WHERE ROWNUM <= ?) T WHERE T.ROWNO >= ?      

      UpdateTime--2017年1月22日14:06:25
      方法二:使用(+)

SELECT T.*  FROM (SELECT S.*, C.CLAZZNAME, ROWNUM ROWNO          FROM STUDENT3 S, CLAZZ3 C         WHERE S.CLAZZID = C.CLAZZID(+)           AND ROWNUM <= ?) T WHERE T.ROWNO >= ?

      注意:        

        a.只有Oracle数据库可以使用(+)来代替左连接和右连接;
        b."(+)"在"="右侧,表示的是:左连接,如:S.CLAZZID = C.CLAZZID(+);左表(学生表)为主表;
        c."(+)"在"="左侧,表示的是:右连接,如:S.CLAZZID(+) = C.CLAZZID;右表(班级表)为主表。

      方法三:from后面跟两张表

SELECT T.*  FROM (SELECT S.*, C.CLAZZNAME, ROWNUM ROWNO    FROM STUDENT3 S, CLAZZ3 C   WHERE S.CLAZZID = C.CLAZZID     AND ROWNUM <= ?) T WHERE T.ROWNO >= ?

    1.2.2 左连接实现三表关联

    表A---------------------------------关联第一张表B-----------------------关联第二张表c

    语法:  

      select * from 表名A left join 表B on A.columnX=B.columnM and A.columnY=B.columnN left join 表c on 表A=表c的id

    情景:      

      四张表 GJPT_BASY、GJZY_BASY、GJPT_BASY_ERROR、GJZY_BASY_ERROR
      根据四张表,要求返回:医疗机构名称,医疗机构编号,总数,合格数和问题数

    SQL实现:

SELECT TEMP1.*, TEMP2.HEGESUM, TEMP3.TROUBLESUM  FROM (SELECT T1.YLNAME, T1.YLCODE, SUM(RS1) AS YLSUM--总数      FROM (SELECT COUNT(1) AS RS1,               HDSD00_11_118 AS YLNAME,               HDSD00_11_119 AS YLCODE          FROM GJPT_BASY         GROUP BY HDSD00_11_119, HDSD00_11_118        UNION ALL        SELECT COUNT(1) AS RS1,               HDSD00_12_133 AS YLNAME,               HDSD00_12_134 AS YLCODE          FROM GJZY_BASY         GROUP BY HDSD00_12_133, HDSD00_12_134        UNION ALL        SELECT COUNT(1) AS RS1,               HDSD00_11_118 AS YLNAME,               HDSD00_11_119 AS YLCODE          FROM GJPT_BASY_ERROR         GROUP BY HDSD00_11_119, HDSD00_11_118        UNION ALL        SELECT COUNT(1) AS RS1,               HDSD00_12_133 AS YLNAME,               HDSD00_12_134 AS YLCODE          FROM GJZY_BASY_ERROR         GROUP BY HDSD00_12_133, HDSD00_12_134) T1     GROUP BY T1.YLNAME, T1.YLCODE) TEMP1  LEFT JOIN (SELECT *           FROM (SELECT T2.YLNAME, T2.YLCODE, SUM(RS2) AS HEGESUM--合格数               FROM (SELECT COUNT(1) AS RS2,                    HDSD00_11_118 AS YLNAME,                    HDSD00_11_119 AS YLCODE                   FROM GJPT_BASY                  GROUP BY HDSD00_11_119, HDSD00_11_118                 UNION ALL                 SELECT COUNT(1) AS RS2,                    HDSD00_12_133 AS YLNAME,                    HDSD00_12_134 AS YLCODE                   FROM GJZY_BASY                  GROUP BY HDSD00_12_133, HDSD00_12_134) T2              GROUP BY T2.YLNAME, T2.YLCODE)) TEMP2    ON TEMP2.YLNAME = TEMP1.YLNAME   AND TEMP2.YLCODE = TEMP1.YLCODE  LEFT JOIN (SELECT *           FROM (SELECT T3.YLNAME, T3.YLCODE, SUM(RS3) TROUBLESUM--问题数               FROM (SELECT COUNT(1) AS RS3,                    HDSD00_11_118 AS YLNAME,                    HDSD00_11_119 AS YLCODE                   FROM GJPT_BASY_ERROR                  GROUP BY HDSD00_11_119, HDSD00_11_118                 UNION ALL                 SELECT COUNT(1) AS RS3,                    HDSD00_12_133 AS YLNAME,                    HDSD00_12_134 AS YLCODE                   FROM GJZY_BASY_ERROR                  GROUP BY HDSD00_12_133, HDSD00_12_134) T3              GROUP BY T3.YLNAME, T3.YLCODE)) TEMP3    ON TEMP3.YLNAME = TEMP1.YLNAME   AND TEMP3.YLCODE = TEMP1.YLCODE   WHERE TEMP3.YLCODE=41580781841010511A1001;

    1.2.3 分页,分组,计数,排序

/*** 返回数据* FORGID 医疗机构ID* FRCODE 县区编码* FORGNAME 医疗机构名称* FCENNAME_TEM_COUNT 名称不对称计数* ISNULLCENCODE_COUNT 未对照计数* TOTALCOUNT L_DIAITEM_ERROR表中共有多少条数据*/SELECT T3.*FROM (SELECT T2.*, ROWNUM ROWNO      FROM (SELECT T.FORGID,                   T1.FRCODE,                   T1.FORGNAME,                   SUM(NVL(T.NCCW, 0)) FCENNAME_TEM_COUNT,                   SUM(NVL(T.WDZ, 0)) ISNULLCENCODE_COUNT,                   TOTALCOUNT             FROM L_DIAITEM_ERROR T, TORGANIZATION T1             WHERE T.FORGID = T1.FORGID             GROUP BY T.FORGID, T1.FORGNAME, T1.FORGSEQ, T1.FRCODE             ORDER BY T1.FORGSEQ) T2     WHERE ROWNUM <= V_END) T3WHERE T3.ROWNO >= V_START;

    注意:

      a.TOTALCOUNT是已存在的一个变量,这里不作为关注重点;      

      b.被group by的数据,要想查询表中的某个字段,有且只有两种方式:

        方式一:

          在group by 后面加上想要查询出来的字段      

        方式二:

          对于数字列,使用求和函数sum()实现查询

      否则,会报错:不是group by 表达式      

 

ORACLE-SQL(二)