关于union和子查询过程中的给表起别名（个人笔记）

一段语句不能运行：

select status,count(*)
from
(select invoice_id,vendor_id,invoice_total,'1st' as status
from invoices
where invoice_total > 1000
union
select invoice_id,vendor_id,invoice_total,'2nd' as status
from invoices
where invoice_total between 500 and 1000
union
select invoice_id,vendor_id,invoice_total,'3rd' as status
from invoices
where invoice_total <500)
group by status

原因：Every derived table must have its own alias（每一个派生出来的表都必须有一个自己的别名）

按照Every derived table must have its own alias（sql语句错误解决方法）_KING-CSDN博客的方法进行改正，为from后面的子查询起别名：

select count(*)
from
(select * from
(select invoice_id,vendor_id,invoice_total,'1st' as status
from invoices
where invoice_total > 1000
union
select invoice_id,vendor_id,invoice_total,'2nd' as status
from invoices
where invoice_total between 500 and 1000
union
select invoice_id,vendor_id,invoice_total,'3rd' as status
from invoices
where invoice_total <500) 
as a)
group by status

依然不行，后发现应该取两个别名：

select status,count(*)
from
(select * from
(select invoice_id,vendor_id,invoice_total,'1st' as status
from invoices
where invoice_total > 1000
union
select invoice_id,vendor_id,invoice_total,'2nd' as status
from invoices
where invoice_total between 500 and 1000
union
select invoice_id,vendor_id,invoice_total,'3rd' as status
from invoices
where invoice_total <500) t2) t1
group by status

尝试使用视图方法，先把子查询的表变成视图，然后用分组查询计算每个等级的人数：

子查询的适用范围：

where,select,having,from

1.where xx字段名 in / 运算符

2.select (子查询) as 字段名

3.having

        select avg(sal)

        from emp

        group by deptno

        having avg(sal) > (select max(sal) from emp where deptno = 30)

子查询无法使用的情况：

1.group by后面不能使用子查询

2.单行子查询使用单行操作符，多行子查询使用多行操作符

        单行：运算符等，

        多行：in（等于列表中的任意一个） any（和子查询返回的任意一个值比较） all（和子查询返回的所有值比较）

3.子查询中的空值问题

是：where empno in (select mgr from emp) 

否：where empno not in (select mgr from emp where mgr is not null)   如果用not in查询，要注意空值，否则可能返回0行

拓展

子查询可以和多表查询相互代替

TOP-N问题中的伪列：rownum（只能在Oracle中使用）