oracle常用SQL语句

1.查询重复数据行：

 

select code

         from TableName

        group by code

        having count(code) > 1;

 

2.oracle 中查询某一列后，将多列数据转换为字符串

 

方法一：

 

 

--依次创建以下函数
CREATE OR REPLACE FUNCTION stragg(input varchar2) RETURN varchar2
  PARALLEL_ENABLE
  AGGREGATE USING string_agg_type;
--1
CREATE OR REPLACE TYPE "STRING_AGG_TYPE" as object
(
  total varchar2(4000),
  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
    return number,
  member function ODCIAggregateIterate(self  IN OUT string_agg_type,
                                       value IN varchar2) return number,
  member function ODCIAggregateTerminate(self        IN string_agg_type,
                                         returnValue OUT varchar2,
                                         flags       IN number)
    return number,
  member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                     ctx2 IN string_agg_type) return number
)
--2
create or replace type body string_agg_type is
  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
    return number is
  begin
    sctx := string_agg_type(null);
    return ODCIConst.Success;
  end;
  member function ODCIAggregateIterate(self  IN OUT string_agg_type,
                                       value IN varchar2) return number is
  begin
    self.total := self.total || ',' || value;
    return ODCIConst.Success;
  end;
  member function ODCIAggregateTerminate(self        IN string_agg_type,
                                         returnValue OUT varchar2,
                                         flags       IN number) return number is
  begin
    returnValue := ltrim(self.total, ',');
    return ODCIConst.Success;
  end;
  member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                     ctx2 IN string_agg_type) return number is
  begin
    self.total := self.total || ctx2.total;
    return ODCIConst.Success;
  end;
end;

---------------------------------------
--测试如下：
create table t_number
(id  number)

insert into t_number(id)
values(1);
commit;
insert into t_number(id)
values(2);
commit;
insert into t_number(id)
values(3);
commit;
insert into t_number(id)
values(4);
commit;
insert into t_number(id)
values(5);
commit;

--修改前：
select * from t_number;

   id
1  1
2  2
3  3
4  4
5  5

--修改后：
select stragg(distinct id) id from t_number;

   id
1  1,2,3,4,5

 

方法二：(此方法有字符串长度的限制)

 

注意：测试数据库表同上

 

 select max(sys_connect_by_path(id, ',')) as book_types

   from (select a.id, rownum ro

           from (select distinct u.id

                   from t_number u

                 

                 ) a) newtab

  start with newtab.ro = 1

 connect by prior newtab.ro = newtab.ro - 1;

 

 

 

 

 

3. 运行中创建表：create table rowconcat as select distinct BUSS_TYPE from t_tybook_before_bookspecial;

 

4.  Oracle高级查询之OVER (PARTITION BY ..)

 

引自【http://blog.csdn.net/ayou2008/article/details/7179001】

rank()/dense_rank() over(partition by e.deptno order by e.sal desc)语法:

over:  在什么条件之上。

partition by e.deptno:  按部门编号划分（分区）。
order by e.sal desc:  按工资从高到低排序（使用rank()/dense_rank() 时，必须要带order by否则非法）
rank()/dense_rank():  分级
整个语句的意思就是：在按部门划分的基础上，按工资从高到低对雇员进行分级，“级别”由从小到大的数字表示（最小值一定为1）。 

那么rank()和dense_rank()有什么区别呢？
rank():  跳跃排序，如果有两个第一级时，接下来就是第三级。
dense_rank():  连续排序，如果有两个第一级时，接下来仍然是第二级。

 

5. Oracle函数Rank Over Partition使用实例详解（一）

 

引自【http://zhaisx.iteye.com/blog/774165】

 

 

6. Oracle语法之OVER(PARTITION BY)及开窗函数【转载】

   引自【http://zou-qiang.iteye.com/blog/1306246】

 

 

7.查询每个月的第一天和最后一天

          select last_day(add_months(sysdate,-1))+1 as firstday ,last_day(sysdate) as lastday from dual;