- 浏览: 1149200 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (411)
- Java Foundation (41)
- AI/机器学习/数据挖掘/模式识别/自然语言处理/信息检索 (2)
- 云计算/NoSQL/数据分析 (11)
- Linux (13)
- Open Source (12)
- J2EE (52)
- Data Structures (4)
- other (10)
- Dev Error (41)
- Ajax/JS/JSP/HTML5 (47)
- Oracle (68)
- FLEX (19)
- Tools (19)
- 设计模式 (4)
- Database (12)
- SQL Server (9)
- 例子程序 (4)
- mysql (2)
- Web Services (4)
- 面试 (8)
- 嵌入式/移动开发 (18)
- 软件工程/UML (15)
- C/C++ (7)
- 架构Architecture/分布式Distributed (1)
最新评论
-
a535114641:
LZ你好, 用了这个方法后子页面里的JS方法就全不能用了呀
页面局部刷新的两种方式:form+iframe 和 ajax -
di1984HIT:
学习了,真不错,做个记号啊
Machine Learning -
赵师傅临死前:
我一台老机器,myeclipse9 + FB3.5 可以正常使 ...
myeclipse 10 安装 flash builder 4.6 -
Wu_Jiang:
触发时间在将来的某个时间 但是第一次触发的时间超出了失效时间, ...
Based on configured schedule, the given trigger will never fire. -
cylove007:
找了好久,顶你
Editable Select 可编辑select
遇到的问题:
一个sql中,union了几个子查询。单独执行每个子查询都没问题,但union后执行,报
ORA-00904: "xxx": invalid identifier
关于union的使用:
SQL: UNION Query:
http://www.techonthenet.com/sql/union.php
SQL: UNION ALL Query:
http://www.techonthenet.com/sql/union_all.php
所union的各个子查询要有相同数量的列,且对应位置的列必须具有相同的数据类型;但列的名字可以不同。
the diffrence between UNION ALL and UNION is that UNION will attempt to eliminate duplicates.
关于order by的使用:
SQL: ORDER BY Clause
http://www.techonthenet.com/sql/order_by.php
Example #3
You can also sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.
This would return all records sorted by the supplier_city field in descending order, since the supplier_city field is in position #1 in the result set.
union中order by的使用:
You have to use the Order By at the end of ALL the unions。
the ORDER BY is considered to apply to the whole UNION result(it's effectively got lower binding priority than the UNION).
The ORDER BY clause just needs to be the last statement, after you've done all your unioning. You can union several sets together, then put an ORDER BY clause after the last set.
所以,只能在union的最后一个子查询中使用order by,而这个order by是针对整个unioning后的结果集的。So:
如果unoin的几个子查询列名不同,如
这里的问号如果是company_name,则执行整个查询会报“company_name:invalid identifier”(当然,单独执行第二个含order by的子查询是没有问题的);这是因为unioning后结果集的列名是以第一个参加union的子查询的列名为准的;order by针对的是整个unioning后的结果集。对整个查询结果来说,无”company_name“这个字段
如果是supplier_name,则单独执行第二个含order by的子查询是会报“supplier_name:invalid identifier”的,而执行整个查询是没有问题的,因为order by针对的是unioning后的整个结果集,而这“整个结果集”是有supplier_name这列的(以第一个union子查询的列名作为unioning后整个结果集的列名)
为了避免这样事情的发生,可以:
1 使用列序号代替实际列名。如:
2 为unoin的各个子查询使用相同的列名,如:
这样,不管是执行整个查询还是单独执行包含order by的最后一个union子查询,都不会有问题。
Q&A:
http://p2p.wrox.com/sql-language/9505-order-union.html
Q:
A:
一个sql中,union了几个子查询。单独执行每个子查询都没问题,但union后执行,报
ORA-00904: "xxx": invalid identifier
关于union的使用:
SQL: UNION Query:
http://www.techonthenet.com/sql/union.php
SQL: UNION ALL Query:
http://www.techonthenet.com/sql/union_all.php
所union的各个子查询要有相同数量的列,且对应位置的列必须具有相同的数据类型;但列的名字可以不同。
the diffrence between UNION ALL and UNION is that UNION will attempt to eliminate duplicates.
关于order by的使用:
SQL: ORDER BY Clause
http://www.techonthenet.com/sql/order_by.php
Example #3
You can also sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.
SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY 1 DESC;
This would return all records sorted by the supplier_city field in descending order, since the supplier_city field is in position #1 in the result set.
union中order by的使用:
You have to use the Order By at the end of ALL the unions。
the ORDER BY is considered to apply to the whole UNION result(it's effectively got lower binding priority than the UNION).
The ORDER BY clause just needs to be the last statement, after you've done all your unioning. You can union several sets together, then put an ORDER BY clause after the last set.
所以,只能在union的最后一个子查询中使用order by,而这个order by是针对整个unioning后的结果集的。So:
如果unoin的几个子查询列名不同,如
select supplier_id, supplier_name from suppliers UNION select company_id, company_name from companies ORDER BY ?;
这里的问号如果是company_name,则执行整个查询会报“company_name:invalid identifier”(当然,单独执行第二个含order by的子查询是没有问题的);这是因为unioning后结果集的列名是以第一个参加union的子查询的列名为准的;order by针对的是整个unioning后的结果集。对整个查询结果来说,无”company_name“这个字段
如果是supplier_name,则单独执行第二个含order by的子查询是会报“supplier_name:invalid identifier”的,而执行整个查询是没有问题的,因为order by针对的是unioning后的整个结果集,而这“整个结果集”是有supplier_name这列的(以第一个union子查询的列名作为unioning后整个结果集的列名)
为了避免这样事情的发生,可以:
1 使用列序号代替实际列名。如:
select supplier_id, supplier_name from suppliers UNION select company_id, company_name from companies ORDER BY 2;
2 为unoin的各个子查询使用相同的列名,如:
select supplier_id as id, supplier_name as name from suppliers UNION select company_id as id, company_name as name from companies ORDER BY name;
这样,不管是执行整个查询还是单独执行包含order by的最后一个union子查询,都不会有问题。
Q&A:
http://p2p.wrox.com/sql-language/9505-order-union.html
Q:
引用
I have two tables, TableA and TableB defined as follows,
TableA
A1 int
A2 int
A3 int
TableB
B1 int
B2 int
B3 int
If I try to run this query, SQL Server says syntex failed at the Order By clouse. Is such Order by not allowed in SQL, Any other way to achieve this?
Any help will be appreciated.
TableA
A1 int
A2 int
A3 int
TableB
B1 int
B2 int
B3 int
If I try to run this query, SQL Server says syntex failed at the Order By clouse. Is such Order by not allowed in SQL, Any other way to achieve this?
(Select A1, A2 from TableA) Union All (Select B1, B2 from TableB Order by B3)
Any help will be appreciated.
A:
引用
First of all, you can not order by a column that is not included in your SELECT list(我注:这句话是错误的;可以order by一个不在select列表中的column). Secondly, when performing a UNION query the ORDER BY clause must be(我注:not “must be”!) a column index not a column name, because a UNION query does not have column headings (although SQL Server(我注:此处泛指DBMS) pretends that it has by picking the column names used in the first query although this is not ANSI compliant]). Assuming you want to order the second column (A2 and B2) your query should look like this:
Code:
Conceptually, ORDER BY works by producing the final query table with all the queries joined together (if it is a UNION query), then it orders the query results and does not care about what is in the database.
Code:
SELECT A1, A2 FROM TableA UNION ALL SELECT B1, B2 FROM TableB ORDER BY 2
Conceptually, ORDER BY works by producing the final query table with all the queries joined together (if it is a UNION query), then it orders the query results and does not care about what is in the database.
发表评论
-
Oracle: minus | in | exists
2012-09-05 13:49 1470解释及例子: MINUS Query: http://www. ... -
一个奇怪的Oracle sql问题
2011-01-13 16:13 1364select A.M,B.N from Table1 A ... -
Oracle Analytic Functions:RANK, DENSE_RANK, FIRST and LAST;PARTITION BY
2010-12-13 17:02 1317Oracle/PLSQL: Rank Function: ht ... -
Oracle Analytic Functions:RANK, DENSE_RANK, FIRST and LAST
2010-12-13 17:02 1264Oracle/PLSQL: Rank Function: ht ... -
Oracle:Collections Records Type %TYPE %ROWTYPE
2010-11-09 22:27 1269PL/SQL Collections and Records: ... -
Oracle Cursor 游标
2010-11-09 20:44 3046Oracle中Cursor介绍: http://www.ite ... -
Oracle 锁机制
2010-09-19 20:12 3723Oracle多粒度封锁机制研究: http://www.itp ... -
Oracle Data Dictionary 数据字典
2010-09-19 16:44 1540Oracle数据字典查阅: http://download.o ... -
Oracle Sign Function
2010-09-17 14:52 1464Oracle/PLSQL: Sign Function: ht ... -
Oracle Built-In Functions: Next_Day and Last_Day
2010-09-16 17:09 1538next_day(date,char): 它用来返回从第一个 ... -
Oracle Procedure 存储过程
2010-09-16 08:36 1364Oracle/PLSQL: Creating Procedur ... -
Oracle Exception Handle 异常处理
2010-09-15 13:00 2087Handling PL/SQL Errors: http:// ... -
Oracle 性能工具 : Explain plan、Autotrace、Tkprof
2010-09-14 18:07 2228Oracle: 三个内置的性能工具包 Explain plan ... -
关于Oracle数据和对象的导入导出 [转]
2010-09-14 10:25 1269关于Oracle数据和对象的导入导出 [转]: http:// ... -
Oracle jobs(DBMS_JOB and DBMS_SCHEDULER)
2010-07-21 14:14 7807写PL/SQL procedure的时候,一定要写的够健壮、够 ... -
Oracle 各种注释
2010-07-20 14:19 3649为SQL语句添加注释: http://do ... -
Oracle 监听 本地Net服务名 配置
2010-07-20 10:32 1323Oracle数据库配置: http://shupili1410 ... -
[Oracle]Difference between a database and an instance(数据库 实例 区别)
2010-07-20 09:31 1500Difference between a database a ... -
Oracle Bulk Collect
2010-07-16 10:03 1373On BULK COLLECT: http://www.ora ... -
Oracle/PLSQL: FOR Loop 循环语句
2010-07-15 16:43 9350Oracle/PLSQL: FOR Loop: http:// ...
相关推荐
这是因为 UNION ALL 的优先级高于 ORDER BY,ORDER BY 只能在 UNION ALL 的最后一个子查询中使用。 解决方法 为了解决这个问题,我们可以使用以下方法: 1. 将 ORDER BY 子句移动到 UNION ALL 的最后一个子查询中...
MySQL中union和order by是可以一起使用的,但是在使用中需要注意一些小问题,下面通过例子来说明。首先看下面的t1表。 1、如果直接用如下sql语句是会报错:Incorrect usage of UNION and ORDER BY。 SELECT * FROM ...
因此,常常出现这样的错误 代码如下:select * from [IND] where INDID>10unionselect * from [IND] where INDID<9>10 order by INDID descunionselect * from [IND] where INDID<9 order by INDID desc此时就出现...
在SQL Server 2008中,`UNION` 子句用于合并两个或多个 `SELECT` 语句的结果集,但不允许直接在 `UNION` 子句内部使用 `ORDER BY`。这是因为 `ORDER BY` 通常用于对查询结果进行排序,而 `UNION` 的目的是合并不同的...
- `ORDER BY` 和 `COMPUTE` 子句只能应用于整个 `UNION` 或 `UNION ALL` 的结果集,不能分别应用于每个单独的 `SELECT` 语句。 - 示例正确用法: ```sql SELECT * FROM ( SELECT AID, AName, ADemo FROM ATable ...
当尝试在`UNION`中使用`ORDER BY`时,会出现错误提示“Incorrect usage of UNION and ORDER BY”,这意味着我们不能直接将`ORDER BY`放在每个`SELECT`后面。为了实现排序,我们可以将每个`SELECT`语句包裹在一个子...
- **排序结果**:可以通过添加`ORDER BY`子句对结果进行排序。 - **条件过滤**:可以使用`WHERE`子句对原始数据进行过滤后再使用`UNION`。 - **组合多表**:可以同时使用多个`UNION`操作符连接多个`SELECT`语句。 ...
2. 使用UNION子句时,需要注意查询结果的输出顺序,可以使用ORDER BY子句来指定输出顺序。 3. 在使用UNION子句时,需要注意查询结果的输出字段类型和宽度,避免出现输出字段类型和宽度不一致的情况。 UNION子句是...
如果想要在 UNION 中使用 ORDER BY 对结果集进行排序,需要将 ORDER BY 子句放在最后一个查询语句的后面。这是因为 ORDER BY 对整个结果集进行排序,而最后一个查询的结果集是最终结果集的一部分,只有在最后一个...
3. **排序结果**:`UNION`后的结果默认无序,如果需要排序,可以添加`ORDER BY`子句,但注意`ORDER BY`只能放在所有`SELECT`语句的最后。 ```sql SELECT ProductID, ProductName FROM Products1 ORDER BY ...
### SQL中的ORDER BY, GROUP BY, HAVING 的用法区别详解 #### 一、概述 在SQL查询语言中,`ORDER BY`, `GROUP BY`, 和 `HAVING` 是三个非常重要的概念,它们分别用于对查询结果进行排序、分组以及在分组后进一步...
在使用集合操作符时,可以使用 ORDER BY 子句对结果集进行排序。例如: SELECT id, name FROM worker UNION ALL SELECT empno, ename FROM emp ORDER BY name; 在上面的例子中,ORDER BY 子句将对合并后的结果集...
ORDER BY ...)` 中,`PARTITION BY` 定义了分区的依据,而 `ORDER BY` 规定了行号的排序规则。例如: ```sql SELECT *, ROW_NUMBER() OVER(PARTITION BY corp_name ORDER BY submit_time DESC) AS row_num FROM ...
在MySQL中,`UNION ALL` 用于合并多个`...同时,理解如何在`UNION ALL`操作中嵌套`ORDER BY`子句,对于编写复杂查询至关重要。这种技术在处理多条件排序和复杂数据组合时非常实用,能够灵活地适应不断变化的业务需求。
您似乎至少有6个表具有完全相同的架构。 通常这可以肯定地表明您的数据库设计需要引起注意。 从我可以看到的所有这些信息可以(并且应该)存储在单个表中。 添加另一列以指示“ BuildingType” -...