# 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语句

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 totalfrom E:/txt/Sales.txt as S join E:/txt/Products.txt as P on S.productid=P.IDwhere S.quantity<=10

# 13. 多个文件关联查询

A
1 \$select e.NAME as NAMEfrom 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.STATEIDwhere d.NAME=’HR’ and s.NAME=’California’

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

A
1 \$select e.NAME as ENAMEfrom 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.EIDwhere 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.EIDwhere 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.txtwhere NAME=’HR’ or NAME=’R&D’ or NAME=’Sales’) select A.DEPT DEPT,count(*) NUM,avg(B.SALARY) AVG_SALARY fromA left join E:/txt/EMPLOYEE.txt Bon A.DEPT=B.DEPTwhere B.GENDER=’F’ group by A.DEPT

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

(=￣ω￣=)··· 暂无内容！

15

0

2

4