• 博客访问： 4032737
• 博文数量： 172
• 博客积分： 0
• 博客等级： 民兵
• 技术积分： 1923
• 用 户 组： 普通用户
• 注册时间： 2018-12-20 14:57

2021年（19）

2020年（81）

2019年（68）

2018年（4）

2020-11-09 15:57:28

# 1.过滤

 A 1 \$select * from   E:/txt/Students_scores.txt where

# 2.汇总

 A 1 \$select   avg(Chinese),max(Math),sum(English) from E:/txt/Students_scores.txt

# 3.跨列计算

 A 1 \$select   *,English+Chinese+Math as total_score from E:/txt/students_scores.txt

A1中结果如下，增加了一个新的计算列total_score

# 4.CASE语句

SQL中可以使用CASE语句进行复杂条件计算。

 A 1 \$select   *, case when English>=60 then 'Pass' else 'Fail' end as English_evaluation   from E:/txt/students_scores.txt

A1中结果如下，增加了一个新的计算列English_evaluation

# 5.排序

 A 1 \$select *   from E:/txt/students_scores.txt order by CLASS,English+Chinese+Math desc

# 6.TOP-N

 A 1 \$select   top 3 * from E:/txt/students_scores.txt order by English desc

# 7.分组汇总

 A 1 \$select   CLASS,min(English),max(Chinese),sum(Math) from E:/txt/students_scores.txt   group by CLASS

# 8.分组后过滤

 A 1 \$select   CLASS,avg(English) as avg_En from E:/txt/students_scores.txt group by CLASS having   avg(English)<70

A1中查询结果如下：

# 9.去重

 A 1 \$select   distinct CLASS from E:/txt/students_scores.txt

# 10.去重计数

 A 1 \$select   count(distinct PID) from E:/txt/PRODUCT_SALE.txt

# 11.分组去重计数

 A 1 \$select   PID,count(distinct DATE) as no_sdate from E:/txt/PRODUCT_SALE.txt group by   PID

# 12.两个文件关联查询

 A 1 \$select   sum(S.quantity*P.Price) as total from E:/txt/Sales.txt as S   join E:/txt/Products.txt as P on S.productid=P.ID where S.quantity<=10

# 13.多个文件关联查询

 A 1 \$select   e.NAME as NAME from  E:/txt/EMPLOYEE_J.txt  as e       join E:/txt/DEPARTMENT.txt as d on   e.DEPTID=d.DEPTID       join E:/txt/STATE.txt as s on   e.STATEID=s.STATEID where       d.NAME='HR' and s.NAME='California'

# 14.多个文件多级关联查询

 A 1 \$select   e.NAME as ENAME from   E:/txt/EMPLOYEE.txt  as e      join E:/txt/DEPARTMENT.txt as d on   e.DEPT=d.NAME      join E:/txt/EMPLOYEE.txt  as emp on d.MANAGER=emp.EID where   e.STATE='New York' and emp.STATE='California'

# 15.嵌套子查询

 A 1 \$select   emp.BIRTHDAY as BIRTHDAY,emp.DEPT as DEPT          from E:/txt/DEPARTMENT.txt as dept             join  E:/txt/EMPLOYEE.txt emp             on  dept.MANAGER=emp.EID where     emp.BIRTHDAY=(select   max(BIRTHDAY)             from ( select emp1.BIRTHDAY as BIRTHDAY                     from E:/txt/DEPARTMENT.txt as   dept1                         join E:/txt/EMPLOYEE.txt as   emp1                         on  dept1.MANAGER=emp1.EID                    )              )

# 16.公用表表达式

 A 1 \$with A   as (select   NAME as DEPT from E:/txt/DEPARTMENT.txt where   NAME='HR' or NAME='R&D' or NAME='Sales')   select   A.DEPT DEPT,count(*) NUM,avg(B.SALARY) AVG_SALARY from A left   join E:/txt/EMPLOYEE.txt B on   A.DEPT=B.DEPT where   B.GENDER='F' group by A.DEPT

》中还有更多敏捷计算示例。