Oracle中UNION和ORDER BY共用方法
发布时间:2020-12-12 13:16:41 所属栏目:百科 来源:网络整理
导读:问题 SQL语句中,UNION拼接两个单独的SQL时候,单独的SQL中加入ORDER BY会报错,ORDER BY只能放在句末。 // 会报错的语句SELECT S.S_ID AS ID,S.S_NAME AS NAMEFROM STUDENT SORDER BY S_NAMEUNIONSELECT S2.S_ID AS ID,S2.S_NAME AS NAMEFROM STUDENT2 S2OR
问题SQL语句中,UNION拼接两个单独的SQL时候,单独的SQL中加入ORDER BY会报错,ORDER BY只能放在句末。 // 会报错的语句 SELECT S.S_ID AS ID,S.S_NAME AS NAME FROM STUDENT S ORDER BY S_NAME UNION SELECT S2.S_ID AS ID,S2.S_NAME AS NAME FROM STUDENT2 S2 ORDER BY S_NAME // 可以正常执行的语句 SELECT S.S_ID AS ID,S.S_NAME AS NAME FROM STUDENT S UNION SELECT S2.S_ID AS ID,S2.S_NAME AS NAME FROM STUDENT2 S2 ORDER BY S_NAME 但是要根据每条分语句的字段来排序的话,这样写是不行的。 解决方法解决方法有两种 一、将结果集当做一个临时表再进行排序SELECT T.* FROM ( SELECT S.S_ID AS ID,S2.S_NAME AS NAME FROM STUDENT2 S2) T ORDER BY T.NAME 二、单独对表进行排序后再进行合并SELECT T.* FROM ( SELECT S.S_ID AS ID,S.S_NAME AS NAME FROM STUDENT S ORDER BY T.NAME) T UNION SELECT T2.* FROM ( SELECT S2.S_ID AS ID,S2.S_NAME AS NAME FROM STUDENT2 S2 ORDER BY T.NAME) T2 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
推荐文章
站长推荐
热点阅读