SQL 复杂查询

 一、子查询

1.相关子查询

  相关子查询是指需要引用主查询列表的子查询语句，相关子查询是通过EXISTS谓词来实现的。下面以显示工作在"new york"的所有雇员为例，说明相关子查询的使用方法，示例如下：

      SQL> select ename,job,sal,deptno from emp where exists

  (select 1 from dept where dept.deptno=emp.deptno and dept.loc=‘new york‘);

  如上所示，当使用exists谓词时，如果子查询存在返回结果，则条件为true;如果子查询没用返回结果，则条件为false。

 

2.在from子句中使用子查询时，该子查询会被作为视图对待，因此也被称为内嵌视图。注意，当在from

子句中使用子查询时，必须要给子查询指定别名。下面以显示高于部门平均工资的雇员为例，说明在from子句中使用子查询的方法。示例如下：

      SQL> select ename,job,sal from emp,

  (select deptno,avg(sal) avgsal from emp

           group by deptno) dept

           where emp.deptno=dept.deptno and emp.sal>dept.avgsal;

 

二、复杂查询

1.具有父子关系的层次查询

 start with:用于指定层次查询的根行。

 connect By:用于指定父行和子行之间的关系（connect by condition),在condition中必须使用prior引用父行。

      SQL> select LPAD(‘ ‘,3*(level-1))||ename ename,

           LPAD(‘ ‘,3*(level-1)||job job from emp

  where job<>‘CLERK‘ start with mgr is null 

  connect by mgr=prior empno;

 

2.使用case表达式

      SQL> select ename,sal,CASE WHEN sal>3000 THEN 3

  WHEN sal>2000 THEN 2 ELSE 1 END grade

  from emp where deptno=10;

 

3.倒叙查询

  查看历史数据(倒叙查询只能查询5分钟之前变化的数据，而不能查询5分钟之内变化的数据)

      SQL> select ename,sal from emp AS OF timestamp to_timestamp

     (‘2003-05-18 19:59:00‘,‘YYYY-MM-DD HH24:MI:SS‘)

  where ename=‘CLERK‘;

 

4.WITH创建临时表

      SQL> WITH tempname AS ( SELECT ...)

 

三、字符函数

1.initcap(char):该函数用于将字符串中的每个单词的首字符大写，其他字符小写，单词之间用空格和非字母字符分隔。示例如下：

      SQL> initcap(‘my world‘)   输出：My World

 

四、数字函数

 

五、日期函数

 

六、转换函数

 

七、使用记录(单行多列数据的处理用记录方式比较好)

 

    定义记录的两种方式

    declare 

  type testRecode id record(

     rname number(6,2),

     rpassword emp.empPassword%type)

     exampleRecord testRecode;

 begin

   ....

   exampleRecord.rname:=‘hhh‘;

   end    

     

   

declare

   testRecord emp%rowtype;

   begin

     .....

     emp.name:=‘jjj‘;

     end

八、单列多行（索引、嵌套、VARRAY)

 

九、多行多列（记录表）

 

把行数据变成列数据的SQL查询
GROUP BY 子句
指定用来放置输出行的组，并且如果 SELECT 子句 中包含聚合函数，则计算每组的汇总值。指定 GROUP BY 时，选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。

即select选择的列要么在聚合函数中，要么在Group by 中进行分组。

如果你想按下面的方式显示数据

 

而你的数据库中实际的存储方式是如下的。即是以行的形式存储的数据，现在要变成上面的列来存储

 

SQL可以如下：

select s.ClassProCode,s.YearMonth,
max(case s.SurveyCode when ‘SC001‘ then s.Score else 0 end) as ‘报名报道‘,
max(case s.SurveyCode when ‘SC002‘ then s.Score else 0 end) as ‘后勤接待‘,
max(case s.SurveyCode when ‘SC003‘ then s.Score else 0 end) as ‘副班主任‘
from SerSurvey s
group by s.ClassProCode,s.YearMonth

--备注：按照SurveyCode这个条件来转换数据，如果条件成立，则转换Score这一行的数据成列显示，否则为0

或者

select A.*,
(select top 1 Score from SerSurvey

where ClassProCode = A.ClassProCode and YearMonth = A.YearMonth and SurveyCode = ‘SC001‘) as ‘报名报道‘,
(select top 1 Score from SerSurvey

where ClassProCode = A.ClassProCode and YearMonth = A.YearMonth and SurveyCode = ‘SC002‘) as ‘后勤接待‘,
(select top 1 Score from SerSurvey

where ClassProCode = A.ClassProCode and YearMonth = A.YearMonth and SurveyCode = ‘SC003‘) as ‘副班主任‘
from 
(
select s.ClassProCode,s.YearMonth
from  SerSurvey s
group by s.ClassProCode,s.YearMonth
) A

 

 

可以分页的：

begin WITH list AS ( SELECT ROW_NUMBER() OVER (ORDER BY s.YearMonth DESC)AS Row,
s.ClassProCode,s.YearMonth,
max(case s.SurveyCode when ‘SC001‘ then s.Score else 0 end) as ‘ApplyReport‘,
max(case s.SurveyCode when ‘SC002‘ then s.Score else 0 end) as ‘LogisticsReception‘,
max(case s.SurveyCode when ‘SC003‘ then s.Score else 0 end) as ‘ViceHeadTeacher‘,
max(case s.SurveyCode when ‘SC001‘ then s.flag else 0 end) as ‘ApplyReportFlag‘,
max(case s.SurveyCode when ‘SC002‘ then s.flag else 0 end) as ‘LogisticsReceptionFlag‘,
max(case s.SurveyCode when ‘SC003‘ then s.flag else 0 end) as ‘ViceHeadTeacherFlag‘ 
from SerSurvey s
where 1=1 --And s.ClassProCode like ‘%ss%‘ And s.YearMonth=‘2010-05‘
group by s.ClassProCode,s.YearMonth)
SELECT * FROM list WHERE Row between 1 and 10
end