首页 > 代码库 > SQL 复杂查询

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 sgroup by s.ClassProCode,s.YearMonth--备注:按照SurveyCode这个条件来转换数据,如果条件成立,则转换Score这一行的数据成列显示,否则为0或者select A.*,(select top 1 Score from SerSurveywhere ClassProCode = A.ClassProCode and YearMonth = A.YearMonth and SurveyCode = SC001) as 报名报道,(select top 1 Score from SerSurveywhere ClassProCode = A.ClassProCode and YearMonth = A.YearMonth and SurveyCode = SC002) as 后勤接待,(select top 1 Score from SerSurveywhere ClassProCode = A.ClassProCode and YearMonth = A.YearMonth and SurveyCode = SC003) as 副班主任from (select s.ClassProCode,s.YearMonthfrom  SerSurvey sgroup 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 swhere 1=1 --And s.ClassProCode like %ss% And s.YearMonth=2010-05group by s.ClassProCode,s.YearMonth)SELECT * FROM list WHERE Row between 1 and 10end

 

SQL 复杂查询