SQL相关问题 [英] Sql ralated Question

问题描述

我可以在SQL Server 2008中将所有与where和order by子句结合使用吗?

can i used union all with where and order by clause in sql server 2008

推荐答案

此处给出了UNION 的语法.
http://msdn.microsoft.com/en-us/library/ms180026.aspx [ ^ ]
从这里可以看出，WHERE 不是UNION 语句的一部分.
本质上UNION 合并两个查询表达式的结果，而UNION ALL合并两个查询表达式的所有行，包括重复行，如此处所述
http://www.fmsinc.com/free/NewTips/SQL/SQLtip5.asp [ ^ ]

因此，UNION可以与包含WHERE 子句的查询表达式一起使用，但是WHERE 不是UNION语句的一部分.

正如 Espen Harlinn 在其解决方案中所说的那样，ORDER BY子句对整个结果集进行排序.

ORDER BY的顺序很重要，如
第一个链接中所示 C.使用两个带有ORDER BY的SELECT语句的UNION，以及ORDER BY 子句的正确位置和正确位置的示例

The syntax of UNION is given here.
http://msdn.microsoft.com/en-us/library/ms180026.aspx[^]
As seen from here the WHERE is not part of UNION statement.
Essentially UNION combines the results from two query expressions and UNION ALL combines all rows from both the query expressions including duplicate rows as explained here
http://www.fmsinc.com/free/NewTips/SQL/SQLtip5.asp[^]

So UNION can be used with query expressions containing WHERE clause, but the WHERE is not part of the UNION statement.

As Espen Harlinn said in his solution the ORDER BY clause sorts the full result set.

The order of ORDER BY is important as shown in the first link at
C. Using UNION of two SELECT statements with ORDER BY, with sample of both incorrect and correct placement of ORDER BY clause

像这样:

SELECT NAME FROM T1
 WHERE NAME LIKE 'O%'
UNION ALL
SELECT NAME FROM T2
 WHERE NAME LIKE 'A%' 
ORDER BY NAME

ORDER BY子句对整个结果集进行排序，因此T2中的元素将在T1中的元素之前列出-假设在T2中存在以A开头的元素，在T1中以O开头的元素.

最好的问候
Espen Harlinn

The ORDER BY clause sorts the full result set, so elements from T2 will be listed ahead of elements from T1 - assuming there are elements that starts with A in T2 and elements that starts with O in T1.

Best regards
Espen Harlinn

这篇关于SQL相关问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！