在开发过程中经常碰到要实现按照某一字段,将其它字段中不同的值连接后显示到一行上的需求。本人总结了以下几种方法,在实际开发中可以参考。
建立表结构,在Oracle9i上。
CREATE TABLE temp
(
DEPTNO int,
ENAME varchar(20 BYTE),
SEX varchar(20 BYTE),
AGE varchar(20 BYTE)
);
insert into temp values(10,'aaa','F','18');
insert into temp values(10,'bbb','F','19');
insert into temp values(10,'ccc','F','20');
insert into temp values(20,'ddd','M','21');
insert into temp values(20,'eee','M','22');
insert into temp values(20,'fff','M','23');
insert into temp values(30,'hhh','X','24');
insert into temp values(30,'ggg','X','25');
insert into temp values(30,'yyy','X','26');
insert into temp values(30,'ttt','X','27');
commit;
第一种写法:
SELECT DISTINCT FIRST_VALUE (deptno) OVER (PARTITION BY deptno ORDER BY lv DESC) AS deptno,
FIRST_VALUE (ename) OVER (PARTITION BY deptno ORDER BY lv DESC) AS ename
FROM (SELECT deptno, SYS_CONNECT_BY_PATH (ename, ' ') ename,
LEVEL lv FROM (SELECT deptno, ename,
LAG (ename, 1, NULL) OVER (PARTITION BY deptno ORDER BY ename) ename_1
FROM (SELECT deptno, ename FROM temp))
CONNECT BY PRIOR ename = ename_1
ORDER BY deptno)
结果如下:
10| aaa bbb ccc
20| ddd eee fff
30| ggg hhh ttt yyy
改进后可以增加一列如下:
SELECT DISTINCT FIRST_VALUE (deptno) OVER (PARTITION BY deptno ORDER BY lv DESC) AS deptno,
FIRST_VALUE (sex) OVER (PARTITION BY sex ORDER BY lv DESC) AS sex,
FIRST_VALUE (ename) OVER (PARTITION BY deptno ORDER BY lv DESC) AS ename
FROM (SELECT deptno, sex, SYS_CONNECT_BY_PATH (ename, ' ') ename, LEVEL lv
FROM (SELECT deptno, ename, sex,
LAG (ename, 1, NULL) OVER (PARTITION BY deptno ORDER BY ename) ename_1
FROM (SELECT deptno, ename, sex FROM temp))
CONNECT BY PRIOR ename = ename_1
ORDER BY deptno)
结果如下:
10|F| aaa bbb ccc
20|M| ddd eee fff
30|X| ggg hhh ttt yyy
第二种写法:
SELECT deptno, SYS_CONNECT_BY_PATH (ename, ' ') AS ename, sex,
SYS_CONNECT_BY_PATH (age, ' ') AS age
FROM (SELECT deptno, ename, sex, age, RANK () OVER (ORDER BY deptno)
+ ROW_NUMBER () OVER (ORDER BY deptno) rn,
ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY deptno) rm
FROM temp) a1
WHERE a1.ROWID IN (SELECT MAX (a2.ROWID) FROM temp a2 WHERE a2.deptno = a1.deptno)
START WITH rm = 1
CONNECT BY PRIOR rn = rn - 1
结果如下:
10| aaa bbb ccc|F| 18 19 20
20| ddd eee fff|M| 21 22 23
30| hhh ggg yyy ttt|X| 24 25 26 27
出自:http://hi.baidu.com/rel_conquer/blog/item/fb3bb6f3a7a5185d352acc08.html
Oracle中SYS_CONNECT_BY_PATH函数是非常重要的函数,下面就为您介绍一个使用SYS_CONNECT_BY_PATH函数的例子,实例如下:
-
create table test (a varchar2(10),b varchar2(10));
-
-
INSERT INTO TEST (A, B) VALUES ('1', '我');
-
INSERT INTO TEST (A, B) VALUES ('1', '们');
-
INSERT INTO TEST (A, B) VALUES ('2', '一');
-
INSERT INTO TEST (A, B) VALUES ('2', '起');
-
COMMIT;
-
-
SELECT A, B FROM TEST
-
-
A B
-
---------- ----------
-
1 我
-
1 们
-
2 一
-
2 起
-
-
现在需要达到如下的效果,
-
A B
-
---------- ----------
-
1 我,们
-
2 一,起
-
只想用一句sql来返回结果。
-
SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ',')), ',') B
-
FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN
-
FROM TEST)
-
START WITH
RN
=
1
-
CONNECT BY RN -
1
=
PRIOR
RN
-
AND
A
=
PRIOR
A
-
GROUP BY A;
其中,SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分,然后连接在一个列中显示。
row_number函数的用途是非常广泛,这个函数的功能是为查询出来的每一行记录生成一个序号。生产序号的方法通过over()函数里面的语句来控制。
分享到:
相关推荐
泛微系统中常见的主要基于SQL server相关的SQL语句大全, 其中包括组织架构相关SQL,流程相关SQL以及其他自定义需求; 效率排名,等SQL总数将近百;
主要介绍了MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法,总结分析了MySQL建表、编码设置、字段添加、索引操作所涉及的SQL语句,需要的朋友可以参考下
需求:把一个表某个字段内容复制到另一张表的某个字段。 实现sql语句1: 代码如下:UPDATE file_manager_folder f1LEFT OUTER JOIN file_manager_folder f2 ON f1.name = f2.name AND f2.parentId = 54SET f1.parentId...
有一个非常长的sql,用编辑器打开编写的时候太长了导致编写非常吃力,而且容易错乱,我想做的是把A,B,C三个变量赋值到sql中的字段中去 A=1 B=2 C=3 sql = "update student t set t.name = '',t.sex = '',t.age = ''...
主要介绍了SQL SERVER 表与表之间 字段一对多sql语句写法,需要的朋友可以参考下
fire bird常用sql语句 一、分页写法小例: 二、显示表名和表结构 三、使用ISQL连接数据库 四、更新字段注释 五、显示字段注释 六、更新表注释 七、查询所有的表和视图(包括系统表和系统视图) 八、查询所有的用户表...
下面为您介绍的是查询表的字段名的sql语句写法,sql语句可以实现许多的功能,希望可以您在学习sql语句使用方面获得启示。 1. select name from syscolumns where id = (select id from sysobjects where type = 'u' ...
在字段DPT_ID上进行了合并空字符串的操作以诱导数据库先执行子查询中的(SELECTDPT_ID||'FROMDEPART-MENTWHEREDPT_NAMELIKE'AC%')在子查询完成后再与EMPLOYEE表进行嵌套循环连接(NestedLoopJoin)。图7 ...
1、把数据、日志、索引放到不同的I/O设备上,增加读取速度,以前可以将Tempdb应放在RAID0上,SQL2000不在支持。数据量(尺寸)越大,提高I/O越重要。 2、纵向、横向分割表,减少表的尺寸(sp_spaceuse) 3、升级...
在字段DPT_ID上进行了合并空字符串的操作以诱导数据库先执行子查询中的(SELECTDPT_ID||\'FROMDEPART-MENTWHEREDPT_NAMELIKE\'AC%\')在子查询完成后再与EMPLOYEE表进行嵌套循环连接(NestedLoopJoin)。图7 在线执行...
一是查询优化器为当前的SQL语句生成佳的执行计划,保证数据读写使用佳路径;二是设置合理的物理存储结构,如表的类型,字段的顺序,字段的数据类型等。本文主要描述如何编写高效的SQL语句并给出示例。下面的描述主要...
Delphi 7.0结合SQLSERVER数据库实现的查询例子,演示如何查询指定字段为空的数据,主要是SQL查询语句的写法,起到了至关重要的作用,下面的代码中,包括了核心的查询语句供参考: begin with ADOQuery1 do ...
codemirror的sql代码提示默认是不支持中文表名自动提示的,在源码中写法只是检索字母数字下划线,加上中文匹配的正则后,终于支持中文表了。修改文件只有sql.js,其他文件只是为了方便打断点,格式化了一下方法
10.2.5 将子查询因子化应用到PL/SQL中 270 10.3 递归子查询 273 10.3.1 一个CONNECT BY的例子 274 10.3.2 使用RSF的例子 275 10.3.3 RSF的限制条件 276 10.3.4 与CONNECT BY的不同点 276 10.4 复制CONNECT BY...
以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的...
SQL语句可以在一行上写出,建议多行写出,便于阅读和调试。 多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号,但特定的DBMS可能必须在单条SQL语句后加分号。 SQL语句的最后一句要以 “;”号...