The used SELECT statements have a different number of columns 错误解决
来源:互联网 发布:江西理工大学软件学院 编辑:程序博客网 时间:2024/06/10 01:44
昨天,遇到了一个sql异常错误信息:java.sql.SQLException: The used SELECT statements have a different number of columns,sql我自己随意写了一个,大概是这样子:
SELECT S.s1 AS n1, S.s2 AS n2, Y.y2 AS n3, S.s3 AS n4FROM SINNER JOIN Y ON S.s1 = Y.y1WHERE S.s4 = 'student'UNION ALL SELECT S.s1 AS n1, S.s2 AS n2, Z.z2 AS n3, S.s3 AS n4 FROM S INNER JOIN Z ON S.s1 = Z.z1 WHERE S.s4 = 'teacher' ORDER BY n1 DESC;
其中UNION ALL所select的4个列类型都是一样的,而且列数都是4,只不过所查询的列中,Y表的y2和Z表的z2所查询的条件不同,但都是和S表关联起来的,不知道为何,本地跑起来没问题,断点调试进去把sql扒出来一查也是没问题,并且把sql搞到测试数据库也没问题,但是在后台显示的时候就会报上述的那种sql异常错误,后来去查了一下网上报这种错误的,都说是列数不一致或者是有某一列类型不一样,我看了一下,基本没问题啊。
最后,去查询了一下UNION 和 UNION ALL的差别,下面摘自网上:
UNION和UNION ALL关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同。
1、对重复结果的处理:UNION在进行表链接后会筛选掉重复的记录,Union All不会去除重复记录。
2、对排序的处理:Union将会按照字段的顺序进行排序;UNION ALL只是简单的将两个结果合并后就返回。
从效率上说,UNION ALL 要比UNION快很多,所以,如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话,那么就使用UNION ALL。
我要实现的功能可以包含重复的数据,所以就用UNION ALL,但还需要对查询出来的结果进行排序,但下面说对排序的处理,UNION ALL只是简单的将两个结果合并后就返回。所以就怀疑是不是后面那个ORDER BY n1 DESC导致的问题,所以改了一下SQL,改了之后如下:
SELECT * FROM (SELECT S.s1 AS n1, S.s2 AS n2, Y.y2 AS n3, S.s3 AS n4FROM SINNER JOIN Y ON S.s1 = Y.y1WHERE S.s4 = 'student'UNION ALL SELECT S.s1 AS n1, S.s2 AS n2, Z.z2 AS n3, S.s3 AS n4 FROM S INNER JOIN Z ON S.s1 = Z.z1 WHERE S.s4 = 'teacher') t ORDER BY t.n1 DESC;
把UNION ALL所查出来的数据作为一个临时表t,然后再通过查出来的t.n1来排序,最后没问题了,但是效率估计不太好。
不清楚是不是MYSQL版本或者敏感问题导致,以上内容仅供参考,谢谢!
- The used SELECT statements have a different number of columns 错误解决
- The used SELECT statements have a different number of columns
- [Err] 1222 - The used SELECT statements have a different number of columns
- ERROR 1222 (21000): The used SELECT statements have a different number of columns
- UNION关键字报错:ERROR 1222 (21000): The used SELECT statements have a different number of columns
- mysql The used SELECT statements have a different number of columns
- Nhibernate: Foreign key must have same number of columns as the referenced primary key
- have a better understand of the coordinate used in 3D
- Is there a limit to the number of columns in an HBase row?
- 解决illegal to have multiple occurrences of contentType with different values错误
- JSP 解决illegal to have multiple occurrences of contentType with different values错误
- JSP 解决illegal to have multiple occurrences of contentType with different values错误
- JSP 解决illegal to have multiple occurrences of contentType with different values错误
- JSP 解决illegal to have multiple occurrences of contentType with different values错误
- JSP 解决illegal to have multiple occurrences of contentType with different values错误
- What are the different kinds of parsers used in XML?
- 解决a different object with the same identifier value was already associated with the session错误
- 解决a different object with the same identifier value was already associated with the session错误
- Spring Aspectj 代理 前置 后置 以及 异常
- 122-123_容器_排序_冒泡排序
- mysql选择数据库,删库,建表语句
- 融云自定义消息通知栏显示
- mysql区分大小写方法
- The used SELECT statements have a different number of columns 错误解决
- 白话经典算法系列之六 快速排序 快速搞定
- Tensorflow实现一个CNN分类的例子
- 贝佐斯:人生意义,就在于你的选择
- HDu 2686 Sort is (树状数组)
- Spring Aspactj .xmlSpring-jdbc 的实现
- linux中的软连接与硬连接
- adb中常用的命令
- TCP协议之三次握手