以前分割字符串时,都使用类似split函数的方式处理,下文分享一种对有规律的字符串的分隔方式,

效果3: 按ID分组合并name

 SQL
Code 

1

  

select id,wm_concat(name) name from test group by id;

   

图片 1

sql语句等同于下面的sql语句:

  SQL
Code 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

  

——– 适用范围:8i,9i,10g及以后版本  ( MAX + DECODE )
select id,
       max(decode(rn, 1, name, null)) ||
       max(decode(rn, 2, ‘,’ || name, null)) ||
       max(decode(rn, 3, ‘,’ || name, null)) str
  from (select id,
               name,
               row_number() over(partition by id order by name) as rn
          from test) t
 group by id
 order by 1;
——– 适用范围:8i,9i,10g及以后版本 ( ROW_NUMBER + LEAD )
select id, str
  from (select id,
               row_number() over(partition by id order by name) as rn,
               name || lead(‘,’ || name, 1) over(partition by id order by name) ||
               lead(‘,’ || name, 2) over(partition by id order by name) || 
               lead(‘,’ || name, 3) over(partition by id order by name) as str
          from test)
 where rn = 1
 order by 1;
——– 适用范围:10g及以后版本 ( MODEL )
select id, substr(str, 2) str
  from test model return updated rows partition by(id) dimension by(row_number() 
  over(partition by id order by name) as rn) measures(cast(name as varchar2(20)) as str) 
  rules upsert iterate(3) until(presentv(str [ iteration_number + 2 ], 1, 0) = 0)
  (str [ 0 ] = str [ 0 ] || ‘,’ || str [ iteration_number + 1 ])
 order by 1;
——– 适用范围:8i,9i,10g及以后版本 ( MAX + DECODE )
select t.id id, max(substr(sys_connect_by_path(t.name, ‘,’), 2)) str
  from (select id, name, row_number() over(partition by id order by name) rn
          from test) t
 start with rn = 1
connect by rn = prior rn + 1
       and id = prior id
 group by t.id;

   

您可能感兴趣的文章:

  • PHP执行linux命令常用函数汇总
  • PHP中的一些常用函数收集
  • 非常实用的PHP常用函数汇总
  • PHP 正则表达式常用函数
  • ThinkPHP字符串函数及常用函数汇总
  • PHP常用函数和常见疑难问题解答
  • 基于PHP中的常用函数回顾
  • 基于php常用函数总结(数组,字符串,时间,文件操作)
  • 基于PHP常用函数的用法详解
  • PHP 查找字符串常用函数介绍
  • PHP中的MYSQL常用函数(php下操作数据库必备)
  • 收藏的PHP常用函数
    推荐收藏保存
  • PHP开发过程中常用函数收藏
  • PHP
    正则表达式常用函数使用小结
  • PHP
    常用函数库和一些实用小技巧
  • PHP常用函数小技巧
  • 继续收藏一些PHP常用函数
  • PHP常用函数总结(180多个)

     
 2.使用xml.value的方式获取指定的字符串信息

wm_concat函数

首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以”,”号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据

 SQL
Code 

1
2
3
4
5
6

  

create table test(id number,name varchar2(20));
insert into test values(1,’a’);
insert into test values(1,’b’);
insert into test values(1,’c’);
insert into test values(2,’d’);
insert into test values(2,’e’);

复制代码 代码如下:

 

pivot 列转行

测试数据
(id,类型名称,销售数量),案例:根据水果的类型查询出一条数据显示出每种类型的销售数量。

 SQL
Code 

1
2
3
4
5
6
7
8
9

  

create table demo(id int,name varchar(20),nums int);  —- 创建表
insert into demo values(1, ‘苹果’, 1000);
insert into demo values(2, ‘苹果’, 2000);
insert into demo values(3, ‘苹果’, 4000);
insert into demo values(4, ‘橘子’, 5000);
insert into demo values(5, ‘橘子’, 3000);
insert into demo values(6, ‘葡萄’, 3500);
insert into demo values(7, ‘芒果’, 4200);
insert into demo values(8, ‘芒果’, 5500);

   

图片 2

分组查询
当然这是不符合查询一条数据的要求的)

 SQL
Code 

1

  

select name, sum(nums) nums from demo group by name

图片 3

行转列查询

 SQL
Code 

1

  

select * from (select name, nums from demo) pivot (sum(nums) for name in (‘苹果’ 苹果, ‘橘子’, ‘葡萄’, ‘芒果’));

图片 4

注意
pivot(聚合函数 for 列名 in(类型)) ,其中 in(”)
中可以指定别名,in中还可以指定子查询,比如 select distinct code from
customers

当然也可以不使用pivot函数,等同于下列语句,只是代码比较长,容易理解

 SQL
Code 

1
2
3
4
5

  

select *
  from (select sum(nums) 苹果 from demo where name = ‘苹果’),
       (select sum(nums) 橘子 from demo where name = ‘橘子’),
       (select sum(nums) 葡萄 from demo where name = ‘葡萄’),
       (select sum(nums) 芒果 from demo where name = ‘芒果’);

如下所示:

 
 即:1.使用replace函数,将字符串变成合规的xml格式串

字符串转多列

实际上就是拆分字符串的问题,可以使用
substr、instr、regexp_substr函数方式

  usleep() 函数延迟代码执行若干微秒。
  unpack() 函数从二进制字符串对数据进行解包。
  uniqid() 函数基于以微秒计的当前时间,生成一个唯一的 ID。
  time_sleep_until() 函数延迟代码执行直到指定的时间。
  PHP5常用函数之time_nanosleep() 函数延迟代码执行若干秒和纳秒。
  sleep() 函数延迟代码执行若干秒。
  show_source() 函数对文件进行语法高亮显示。
  strip_whitespace() 函数返回已删除 PHP
注释以及空白字符的源代码文件。
  pack() 函数把数据装入一个二进制字符串。
  ignore_user_abort() 函数设置与客户机断开是否会终止脚本的执行。
  highlight_string() 函数对字符串进行语法高亮显示。
  highlight_file() 函数对文件进行语法高亮显示。
  PHP5常用函数之get_browser() 函数返回用户浏览器的性能。
  exit() 函数输出一条消息,并退出当前脚本。
  eval() 函数把字符串按照 PHP 代码来计算。
  die() 函数输出一条消息,并退出当前脚本。
  defined() 函数检查某常量是否存在。
  define() 函数定义一个常量。
  constant() 函数返回常量的值。
  PHP5常用函数之connection_status() 函数返回当前的连接状态。
  connection_aborted() 函数检查是否断开客户机。
  zip_read() 函数读取打开的 zip 档案中的下一个文件。
  zip_open() 函数打开 ZIP 文件以供读取。
  zip_entry_read() 函数从打开的 zip 档案项目中获取内容。
  zip_entry_open() 函数打开一个 ZIP 档案项目以供读取。
  PHP5常用函数之zip_entry_name() 函数返回 zip 档案项目的名称。
  zip_entry_filesize() 函数返回 zip
档案项目的原始大小(在压缩之前)。
  zip_entry_compressionmethod() 函数返回 zip 档案项目的压缩方法。
  zip_entry_compressedsize() 函数返回 zip 档案项目的压缩文件尺寸。
  zip_entry_close() 函数关闭由 zip_entry_open() 函数打开的 zip
档案文件。
  zip_close() 函数关闭由 zip_open() 函数打开的 zip 档案文件。
  xml_set_unparsed_entity_decl_handler()
函数规定在遇到无法解析的实体名称(NDATA)声明时被调用的函数。
  xml_set_processing_instruction_handler() 函数规定当解析器在 XML
文档中找到处理指令时所调用的函数。
  xml_set_object() 函数允许在对象中使用 XML 解析器。
  PHP5常用函数之xml_set_notation_decl_handler() 函数规定当解析器在
XML 文档中找到符号声明时被调用的函数。
  xml_set_external_entity_ref_handler() 函数规定当解析器在 XML
文档中找到外部实体时被调用的函数。
  xml_set_element_handler() 函数建立起始和终止元素处理器。
  xml_set_default_handler() 函数为 XML
解析器建立默认的数据处理器。
  xml_set_character_data_handler() 函数建立字符数据处理器。
  xml_parser_set_option() 函数为 XML 解析器进行选项设置。
  xml_parser_get_option() 函数从 XML 解析器获取选项设置信息。
  xml_parser_free() 函数释放 XML 解析器。
  PHP5常用函数之xml_parser_create() 函数创建 XML 解析器。
  xml_parser_create_ns() 函数创建带有命名空间支持的 XML 解析器。
  xml_parse_into_struct() 函数把 XML 数据解析到数组中。
  xml_parse() 函数解析 XML 文档。
  xml_get_error_code() 函数获取 XML 解析器错误代码。
  xml_get_current_line_number() 函数获取 XML 解析器的当前行号。
  xml_get_current_column_number() 函数获取 XML
解析器的当前列号。
  PHP5常用函数之xml_get_current_byte_index() 函数获取 XML
解析器的当前字节索引。
  xml_error_string() 函数获取 XML 解析器的错误描述。
  utf8_encode() 函数把 ISO-8859-1 字符串编码为 UTF-8。
  utf8_decode() 函数把 UTF-8 字符串解码为 ISO-8859-1。
  wordwrap() 函数按照指定长度对字符串进行折行处理。
  vsprintf() 函数把格式化字符串写入变量中。
  vprintf() 函数输出格式化的字符串。
  vfprintf() 函数把格式化的字符串写到指定的输出流。
  PHP5常用函数之ucwords() 函数把字符串中每个单词的首字符转换为大写。
  ucfirst() 函数把字符串中的首字符转换为大写。
  trim() 函数从字符串的两端删除空白字符和其他预定义字符。
  substr_replace() 函数把字符串的一部分替换为另一个字符串。
  substr_count() 函数计算子串在字符串中出现的次数。
  substr_compare() 函数从指定的开始长度比较两个字符串。
  substr() 函数返回字符串的一部分。
  strtr() 函数转换字符串中特定的字符。
  strtoupper() 函数把字符串转换为大写。
  strtolower() 函数把字符串转换为小写。
  PHP5常用函数之strtok() 函数把字符串分割为更小的字符串。
  strstr() 函数搜索一个字符串在另一个字符串中的第一次出现。
  strspn() 函数返回在字符串中包含的特定字符的数目。
  strrpos() 函数查找字符串在另一个字符串中最后一次出现的位置。
  strripos() 函数查找字符串在另一个字符串中最后一次出现的位置。
  strrev() 函数反转字符串。
  strrchr()
函数查找字符串在另一个字符串中最后一次出现的位置,并返回从该位置到字符串结尾的所有字符。
  strpos() 函数返回字符串在另一个字符串中第一次出现的位置。
  PHP5常用函数之strpbrk() 函数在字符串中搜索指定字符中的任意一个。
  strncmp() 函数比较两个字符串。
  strncasecmp() 函数比较两个字符串。
  strnatcmp() 函数使用一种“自然”算法来比较两个字符串。
  strnatcasecmp() 函数使用一种“自然”算法来比较两个字符串。
  strlen() 函数返回字符串的长度。
  stristr() 函数查找字符串在另一个字符串中第一次出现的位置。
  stripos() 函数返回字符串在另一个字符串中第一次出现的位置。
  stripslashes() 函数删除由 addslashes() 函数添加的反斜杠。
  stripcslashes() 函数删除由 addcslashes() 函数添加的反斜杠。
  strip_tags() 函数剥去 HTML、XML 以及 PHP 的标签。
  strcspn() 函数返回在找到任何指定的字符之前,在字符串查找的字符数。
  PHP5常用函数之strcoll() 函数比较两个字符串。
  strcmp() 函数比较两个字符串。
  strchr() 函数搜索一个字符串在另一个字符串中的第一次出现。
  strcasecmp() 函数比较两个字符串。
  str_word_count() 函数计算字符串中的单词数。
  str_split() 函数把字符串分割到数组中。
  str_shuffle() 函数随机地打乱字符串中的所有字符。
  str_rot13() 函数对字符串执行 ROT13 编码。
  str_replace() 函数使用一个字符串替换字符串中的另一些字符。
  str_repeat() 函数把字符串重复指定的次数。
  str_pad() 函数把字符串填充为指定的长度。
  str_ireplace() 函数使用一个字符串替换字符串中的另一些字符。
  PHP5常用函数之sscanf()
函数根据指定的格式解析来自一个字符串的输入。
  sprintf() 函数把格式化的字符串写写入一个变量中。
  soundex() 函数计算字符串的 soundex 键。
  similar_text() 函数计算两个字符串的匹配字符的数目。
  sha1_file() 函数计算文件的 SHA-1 散列。
  sha1() 函数计算字符串的 SHA-1 散列。
  setlocale() 函数设置地区信息(地域信息)。
  PHP5常用函数之rtrim() P rtrim() 函数

    如下所示:

unpivot 行转列

顾名思义就是将多列转换成1列中去
案例:现在有一个水果表,记录了4个季度的销售数量,现在要将每种水果的每个季度的销售情况用多行数据展示。

创建表和数据

 SQL
Code 

1
2
3
4
5
6

  

create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);
insert into Fruit values(1,’苹果’,1000,2000,3300,5000);
insert into Fruit values(2,’橘子’,3000,3000,3200,1500);
insert into Fruit values(3,’香蕉’,2500,3500,2200,2500);
insert into Fruit values(4,’葡萄’,1500,2500,1200,3500);
select * from Fruit

图片 5

列转行查询

 SQL
Code 

1

  

select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )

注意:
unpivot没有聚合函数,xiaoshou、jidu字段也是临时的变量

图片 6

同样不使用unpivot也可以实现同样的效果,只是sql语句会很长,而且执行速度效率也没有前者高

 SQL
Code 

1
2
3
4
5
6
7

  

select id, name ,’Q1′ jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,’Q2′ jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,’Q3′ jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,’Q4′ jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit f

发表评论

电子邮件地址不会被公开。 必填项已用*标注