ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验】【SQL】为祖国60华诞献礼之格式化行政区划

【实验】【SQL】为祖国60华诞献礼之格式化行政区划

原创 Linux操作系统 作者:secooler 时间:2009-09-21 16:57:49 0 删除 编辑
伟大的祖国60华诞在即,为表达一名DBA的心声,特完成了如下SQL实验,明确一下祖国的行政区划,抵制一切分裂祖国的行为:)!

1.创建测试表homeland_china
sec@ora10g> create table homeland_china (area varchar2(100), province varchar2(200));

2.初始化行政区划数据
sec@ora10g> insert into homeland_china values
('华北'  ,'北京市,天津市,河北省,山西省,内蒙古自治区');

sec@ora10g> insert into homeland_china values
('东北'  ,'辽宁省,吉林省,黑龙江省');

sec@ora10g> insert into homeland_china values
('华东'  ,'上海市,江苏省,浙江省,安徽省,福建省,江西省,山东省');

sec@ora10g> insert into homeland_china values
('中南'  ,'河南省,湖北省,湖南省,广东省,广西壮族自治区,海南省');

sec@ora10g> insert into homeland_china values
('西南'  ,'重庆市,四川省,贵州省,云南省,西藏自治区');

sec@ora10g> insert into homeland_china values
('西北'  ,'陕西省,甘肃省,青海省,宁夏回族自治区,新疆维吾尔自治区');

sec@ora10g> insert into homeland_china values
('港澳台','香港特别行政区,澳门特别行政区,台湾省');


3.查看初始化后的数据
sec@ora10g> col AREA for a10
sec@ora10g> col PROVINCE for a100
sec@ora10g> select * From homeland_china;

AREA       PROVINCE
---------- -----------------------------------------------------------
华北       北京市,天津市,河北省,山西省,内蒙古自治区
东北       辽宁省,吉林省,黑龙江省
华东       上海市,江苏省,浙江省,安徽省,福建省,江西省,山东省
中南       河南省,湖北省,湖南省,广东省,广西壮族自治区,海南省
西南       重庆市,四川省,贵州省,云南省,西藏自治区
西北       陕西省,甘肃省,青海省,宁夏回族自治区,新疆维吾尔自治区
港澳台     香港特别行政区,澳门特别行政区,台湾省

7 rows selected.

4.为祖国60华诞献礼:上面初始化后的数据显示效果是不是不太直观?我们来使用一系列技巧来格式化上面的行政区划数据,使之显示更加清晰。
请细致比较处理后的输出和初始化数据之间的区别,一切自明。
sec@ora10g> break on area
sec@ora10g> col AREA for a10
sec@ora10g> select area,
  2         province
  3    from (select area,
  4                 trim(substr(str, instr(str, ',', 1, level) + 1,
  5                 instr(str, ',', 1, level + 1)
  6                 - instr(str, ',', 1, level) - 1)) province,
  7                 level lv,
  8                 lag(level, 1, 0) over (partition by area order by level) lg
  9            from (select area,
 10                         ','||province||',' str
 11                    from homeland_china
 12                 )
 13          connect by instr(str, ',', 1, level) > 0
 14         )
 15   where province is not null and lv != lg
 16  /

AREA       PROVINCE
---------- -----------------------------------------------------------
东北        辽宁省
           吉林省
           黑龙江省
中南        河南省
           湖北省
           湖南省
           广东省
           广西壮族自治区
           海南省
华东        上海市
           江苏省
           浙江省
           安徽省
           福建省
           江西省
           山东省
华北        北京市
           天津市
           河北省
           山西省
           内蒙古自治区
港澳台      香港特别行政区
           澳门特别行政区
           台湾省
西北        陕西省
           甘肃省
           青海省
           宁夏回族自治区
           新疆维吾尔自治区
西南        重庆市
           四川省
           贵州省
           云南省
           西藏自治区

34 rows selected.

5.上面的SQL语句在10g中还可以进一步使用“正则表达式”技巧使之简化,具体SQL如下
sec@ora10g> break on area
sec@ora10g> col AREA for a10
sec@ora10g> select area,
  2         province
  3    from (select area,
  4                 regexp_substr(str, '[^,]+', 1, level) province,
  5                 level lv,
  6                 lag(level, 1, 0) over (partition by area order by level) lg
  7            from (select area,
  8                         ','||province str
  9                    from homeland_china
 10                 )
 11          connect by regexp_substr(str, '[^,]+', 1, level) is not null
 12         )
 13  where lv != lg;


AREA       PROVINCE
---------- -----------------------------------------------------------
东北        辽宁省
           吉林省
           黑龙江省
中南        河南省
           湖北省
           湖南省
           广东省
           广西壮族自治区
           海南省
华东        上海市
           江苏省
           浙江省
           安徽省
           福建省
           江西省
           山东省
华北        北京市
           天津市
           河北省
           山西省
           内蒙古自治区
港澳台      香港特别行政区
           澳门特别行政区
           台湾省
西北        陕西省
           甘肃省
           青海省
           宁夏回族自治区
           新疆维吾尔自治区
西南        重庆市
           四川省
           贵州省
           云南省
           西藏自治区

34 rows selected.

6.小结
以上SQL语句请在SQL*Plus命令行中完成。

Best regards to our homeland China.

Good luck.

-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-615173/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    7974342