Microsoft SQL Server 2008技术内幕:T-SQL查询---------逻辑查询处理

Three-Valued Logic(三值逻辑)

SQL values: TRUE, FALSE, UNKNOWN

由于查询筛选器中 (ON, WHERE, and HAVING) 将UNKNOW认定为FALSE,所以代入后表达式为 (NULL=NULL,NULL>5, NULL<NULL)之类的行集都会被过滤掉而不出现在结果集中.

UNKNOWN logical results and NULLs are treated inconsistently in different elements of
the language:

all query filters (ON, WHERE, and HAVING) treat UNKNOWN 
like FALSE. A row for which a filter is UNKNOWN is eliminated from the result set.

an UNKNOWN value in a CHECK constraint is actually treated like TRUE.
Suppose you have a CHECK constraint in a table to require that the salary column be
greater than zero. A row entered into the table with a NULL salary is accepted because
(NULL > 0) is UNKNOWN and treated like TRUE in the CHECK constraint.

A comparison between two NULLs in a filter yields UNKNOWN, which, as I mentioned
earlier, is treated like FALSE—as if one NULL is different than another.

for UNIQUE constraints, set operators (such as UNION and EXCEPT), 
and sorting or grouping operations, NULLs are treated as equal:

          ■   You cannot insert into a table two rows with a NULL in a column that has a

              UNIQUE constraint defi ned on it. T-SQL violates the standard on this point.

          ■   A GROUP BY clause groups all NULLs into one group.

          ■   An ORDER BY clause sorts all NULLs together.

          ■   Set operators treat NULLs as equal when comparing rows from the two sets.

COUNT(NULL) 返回值为0

COUNT(*) 与 COUNT(COLUMN_NAME)区别就在于有无NULL值

SELECT  语句中的别名只能在order by阶段 中引用.由于SQL 语句是同时执行的,所以没法用.

SELECT TOP N WITH TIES... ORDER BY ...

Step 5-3: Apply the TOP Option
  The TOP option is a feature specifi  c to T-SQL that allows you to specify a number or percentage
of rows (rounded up) to return. The specifi  ed number of rows is selected based on the query’s
ORDER BY clause. Traditionally, and according to the ANSI SQL standard, ORDER BY is supposed
to serve a presentation purpose. However, when the TOP option is specifi  ed, the ORDER BY
clause also serves a logical purpose— answering the question “top according to what order?”
Table VT5-3 is generated.
  As mentioned, this step relies on the query’s ORDER BY clause to determine which rows are
  considered the “fi  rst” requested number of rows. If an ORDER BY clause with a unique ORDER
BY list is specifi  ed in a query, the result is deterministic. That is, only one correct result is possible,
containing the fi  rst requested number of rows based on the specifi  ed order. Similarly, when an
ORDER BY clause is specifi  ed with a non-unique ORDER BY list but the TOP option is specifi  ed
WITH TIES, the result is also deterministic. SQL Server inspects the last row that was returned
and returns all other rows from the table that have the same sort values as the last row.
  However, when a non-unique ORDER BY list is specifi  ed without the WITH TIES option, or
ORDER BY is not specifi  ed at all, a TOP query is nondeterministic. That is, the rows returned
are the ones that SQL Server happened to access fi  rst, and there might be different results
that are considered correct. If you want to guarantee determinism, a TOP query must have
either a unique ORDER BY list or the WITH TIES option. 

OVER 子句出现在select 阶段或order by 阶段,因此像下面的查询

select custid, count(*)over(partiton by custid) as numbers from orders where shipcountry="china"

统计出的数值是先除去china的行,然后再进行的行数统计

 

转载于:https://www.cnblogs.com/Gravin-Gu/archive/2013/01/28/2880717.html