SQL cook book笔记一

Problem

You have used aliases to provide more meaningful column names for your result set and would like to exclude some of the rows using the WHERE clause. However, your attempt to reference alias names in the WHERE clause fails:

	select sal as salary, comm as commission
	  from emp
	 where salary < 5000

如果你想要直接在WHERE 里面使用别名是行不通的

Solution

By wrapping your query as an inline view you can reference the aliased columns:

	1 select *
	2   from (
	3 select sal as salary, comm as commission
	4   from emp
	5        ) x
	6  where salary < 5000

解决办法是使用子查询的方式

Discussion

In this simple example, you can avoid the inline view and reference COMM or SAL directly in the WHERE clause to achieve the same result. This solution introduces you to what you would need to do when attempting to reference any of the following in a WHERE clause:

• Aggregate functions

• Scalar subqueries

• Windowing functions

• Aliases

Placing your query, the one giving aliases, in an inline view gives you the ability to reference the aliased columns in your outer query. Why do you need to do this? The WHERE clause is evaluated before the SELECT, thus, SALARY and COMMISSION do not yet exist when the "Problem" query's WHERE clause is evaluated. Those aliases are not applied until after the WHERE clause processing is complete. However, the FROM clause is evaluated before the WHERE. By placing the original query in a FROM clause, the results from that query are generated before the outermost WHERE clause, and your outermost WHERE clause "sees" the alias names. This technique is particularly useful when the columns in a table are not named particularly well.

 

原因是WHERE子句的分析早于SELECT子句，因此，直接在WHERE子句中使用别名的时候，别名还没有被创建出来，所以，这个时候WHERE“看不到”别名。解决方法之所以奏效，是因为FROM子句的分析早于WHERE子句。