- 浏览: 430998 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
q12344566789:
...
如何查看表的并行度并设置表的并行度 -
chruan:
刚好遇到这个问题,谢谢了。
使用Spring的CharacterEncodingFilter应注意的问题 -
nwpucyp:
还需要修改shutdown.bat里的CATALINA_HOM ...
一台机器上同时部署多个tomcat服务 -
ronghua_liu:
dom4j比较奇葩,其他开源项目没看到这样的
dom4j下载地址 -
mc90716:
引用引用引用引用
数据库查询时对多个字段group by 有什么作用
遇到的问题:
一个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.
Sql代码
SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;
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的几个子查询列名不同,如
Sql代码
select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY ?;
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 使用列序号代替实际列名。如:
Sql代码
select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY 2;
select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY 2;
2 为unoin的各个子查询使用相同的列名,如:
Sql代码
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;
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?
Sql代码
(Select A1, A2 from TableA)
Union All
(Select B1, B2 from TableB Order by B3)
(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:
Sql代码
SELECT A1, A2
FROM TableA
UNION ALL
SELECT B1, B2
FROM TableB
ORDER BY 2
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.
----------------------------
自己的看法:
union 之后可以使用order by,这时候order by的是union之后的结果集,但是我在oracle中测试,直接order by 列有问题,给列重新give name or use number is all ok!
一个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.
Sql代码
SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;
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的几个子查询列名不同,如
Sql代码
select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY ?;
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 使用列序号代替实际列名。如:
Sql代码
select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY 2;
select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY 2;
2 为unoin的各个子查询使用相同的列名,如:
Sql代码
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;
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?
Sql代码
(Select A1, A2 from TableA)
Union All
(Select B1, B2 from TableB Order by B3)
(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:
Sql代码
SELECT A1, A2
FROM TableA
UNION ALL
SELECT B1, B2
FROM TableB
ORDER BY 2
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.
----------------------------
自己的看法:
union 之后可以使用order by,这时候order by的是union之后的结果集,但是我在oracle中测试,直接order by 列有问题,给列重新give name or use number is all ok!
发表评论
-
深入理解Oracle索引(5):反向索引的定义、缺点和适用场景
2014-02-20 13:26 756http://blog.csdn.net/dba_waterb ... -
java.sql.SQLException: ORA-01008: 并非所有变量都已绑定
2013-10-17 19:21 3465java.sql.SQLException: ORA-0100 ... -
linux下mysql的root密码忘记解决方
2013-10-08 10:02 8151.首先确认服务器出于安全的状态,也就是没有人能够任意地连接 ... -
centos6 修改profile后,无法登录系统(profile修改错误)
2013-09-29 15:41 774使用单用户模式登陆去修改profile 一、单用户模式; ... -
centos6 修改profile后,无法登录系统(profile修改错误)
2013-09-29 15:41 1702使用单用户模式登陆去修改profile 一、单用户模式; ... -
JavaScript trim函数大赏
2013-09-18 17:41 834http://www.cnblogs.com/rubylouv ... -
java 线程
2013-07-17 17:57 952http://www.cnblogs.com/devinzha ... -
AIX 操作系统查看文件夹及文件大小的命令
2013-07-03 18:51 74881、df -sg 说明:查看各分区的使用情况 2、du ... -
oracle 多个例程,启动具体例程 startup pfile
2013-07-03 13:46 3697如果环境是AIX系统,安装了oracle,具有多个例程(实例i ... -
解决ORA-30036:无法按8扩展段(在还原表空间‘XXXX’中)
2013-07-01 18:03 1751http://blog.sina.com.cn/s/blog_ ... -
oracle 并行度--转载
2013-06-28 15:51 1863从巴乔博客中看到: ‘并行度为DEFAULT的表进行PDML时 ... -
oracle并行查询常见问题 --转载
2013-06-28 15:46 1033在OLAP环境,以利用多的 ... -
多核技术与并发多线程技术介绍(转载)
2013-06-28 15:45 1294看多很多人在这个上面 ... -
AIX如何查看cpu个数(转载)
2013-06-28 15:19 1235http://hi.baidu.com/fgvibxjaneb ... -
如何查看表的并行度并设置表的并行度
2013-06-28 15:17 7539查看表的并行度语句: select table_name,de ... -
Oracle--optimizer_mode
2013-06-24 17:00 1460Oracle--optimizer_mode O ... -
jtable单元格的悬浮提示和表头标题的悬浮提示
2013-06-19 18:14 1499http://blog.csdn.net/yufaw/arti ... -
servlet2.4 和servlet2.5中配置taglib的区别
2013-06-17 15:54 14462.4写法: <jsp-config> & ... -
win7 SP2-1503: 无法初始化 Oracle 调用界面
2013-06-09 15:01 1411win7 下 cmd 运行 sqlplus 报 ... -
触发Full GC执行的情况
2013-06-06 18:38 910http://blog.sina.com.cn/s/blog_ ...
相关推荐
union all与order by用法,并详细举例,oracle pl/sql
MySQL中union和order by是可以一起使用的,但是在使用中需要注意一些小问题,下面通过例子来说明。首先看下面的t1表。 1、如果直接用如下sql语句是会报错:Incorrect usage of UNION and ORDER BY。 SELECT * FROM ...
如果想要在 UNION 中使用 ORDER BY 对结果集进行排序,需要将 ORDER BY 子句放在最后一个查询语句的后面。这是因为 ORDER BY 对整个结果集进行排序,而最后一个查询的结果集是最终结果集的一部分,只有在最后一个...
因此,常常出现这样的错误 代码如下: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此时就出现...
主要介绍了MySQL中Union子句不支持order by的解决方法,结合实例形式分析了在mysql的Union子句中使用order by的方法,需要的朋友可以参考下
主要介绍了SQL Server2008 Order by在union子句不可直接使用的原因详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
您似乎至少有6个表具有完全相同的架构。 通常这可以肯定地表明您的数据库设计需要引起注意。 从我可以看到的所有这些信息可以(并且应该)存储在单个表中。 添加另一列以指示“ BuildingType” -...
在本文中,我们将详细介绍 Partition By 函数的使用方法和示例。 Partition By 函数的基本语法 --------------------------- Partition By 函数的基本语法如下所示: ``` SELECT *, ROW_NUMBER() OVER ...
order_by_、group_by_、having的用法区别.doc
仔细查看了MS的在线帮助,在有关UNION的描述中找到这样一段说明: 如果使用 UNION 运算符,那么各个 SELECT 语句不能包含它们自己的 ORDER BY 或 COMPUTE 子句。而只能在最后一个 SELECT 语句的后面使用一个 ORDER ...
SQL的union查询 联合查询注入原理-SQL的union查询 联合查询注入原理-SQL的union查询 联合查询注入原理-判断原有查询语句的列数 判断原有查询语句的列数通常基于布尔型盲注的思想来进行,通常使用order by 语句来...
两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低,接下来为大家介绍下连接查询排序limit
今天在接到一个需求的时候使用了UNION进行查询后发现,如果两个查询分别使用ORDER BY后拼接居然无法成功排序,经过了好一番折腾,记录下 表结构及数据 -- 创建表 CREATE TABLE test_user ( ID int(11) NOT NULL ...
SQLQueryEvaluator 使用JSQLParser在Java中实现一个简单的数据库SQL查询评估器,该评估器支持Select,Project,Join,Limit,Order by,Bag Union和Aggregate操作。 实现了查询优化,例如选择下推,投影下推,混合...
* 熟悉 MySQL 中的常用函数,如 UNION、ORDER BY、LIMIT 等。 2. SQL 注入的基本流程: * 探测网站是否存在 SQL 漏洞; * 确定查询字段数和回显点; * 查询数据库信息和数据库名; * 利用库名查询表名和列名; ...
这样的实现比较简单,如下的order by语句就可以实现了。 order by PROMOTE_STATUS desc ,SEQUENCE_ID desc…… 结果移交测试之后,产品觉得这里可以优化下,文章的展示要改为已发布、待发布、已下线(没错,已下线...