oracle查询重复记录的第一条

查询重复记录的第一条

select a.*
from
(select bat22.*,
       row_number() over(partition by MAT12ID, MAT12NAME order by bat22id desc) rn
from bat22) a
where a.rn=1;

rn代表行号 总是获取行号为1的数据

group by是分组函数,partition by是分区函数(像sum()等是聚合函数),注意区分。
``
先对cno 中相同的进行分区,在cno 中相同的情况下对degree 进行排序

oracle 利用 decode extract,add_months 查询六个月内公司员工考勤


select D.userId,D.month,SUM(D.clockInNum)AS clockInNum,sum(D.clockOutNum)as clockOutNum,SUM(D.NUMS)AS total,D.nickName,D.deptName,D.userType from (
SELECT
ia.user_id as userId ,
    EXTRACT(MONTH FROM add_months( SYSDATE, -10 )) AS month,
    SUM(DECODE( EXTRACT( MONTH FROM ia.ATTENDANCE_DATE ), EXTRACT( MONTH FROM add_months( SYSDATE, -10 )), 1, 0 )) AS nums,
    SUM(DECODE( EXTRACT( MONTH FROM ia.ATTENDANCE_DATE ), EXTRACT( MONTH FROM add_months( SYSDATE, -10 )), decode(sdd.DICT_LABEL,'请假', 1, '矿工', 1, 0), 0 )) AS clockOutNum,
SUM(DECODE( EXTRACT( MONTH FROM ia.ATTENDANCE_DATE ), EXTRACT( MONTH FROM add_months( SYSDATE, -10 )), decode(sdd.DICT_LABEL,'外出', 1, '出差', 1, '正常', 1,'迟到', 1,'早退', 1, 0), 0 )) AS clockInNum,
su.nick_name AS nickName,
    sd.dept_name AS deptName,
    ia.user_type AS userType 
FROM
    in_attendance ia
    LEFT JOIN sys_user su ON su.user_id = ia.user_id
    LEFT JOIN sys_dept sd ON sd.dept_id = su.dept_id
    LEFT JOIN SYS_DICT_DATA sdd ON sdd.dict_code = ia.ATTENDANCE_STATUS 
WHERE
    ia.del_flag = 0 
GROUP BY
    su.nick_name,sd.dept_name,ia.user_type,sdd.DICT_LABEL,ia.user_id
    )D
    GROUP BY D.nickName,D.deptName,D.userType,D.month,D.userId

多条数据合并为一条

select sys.stragg(a.isdropout) isdropout from yhb a where a.times=1;
逗号分隔
select trim(both ',' from sys.stragg(to_char(a.isdropout)||NVL2(a.isdropout, ',', ''))) as isdropout from yhb a where a.times=1;

select 获取时间差

ceil((To_date(a.END_TIME , 'yyyy-mm-dd hh24-mi-ss') - To_date(a.START_TIME, 'yyyy-mm-dd hh24-mi-ss'))) as hours,

查询树状结构 (这块直接粘贴的 https://blog.csdn.net/zxysshgood/article/details/81671836?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.channel_param

) 感谢 作者
这几天看sql看到了有关connect by和level的关键字用法,以前都用mysql也没用过这个关键字,感觉挺有用的,就学了下,整理一下学习过程。

  **首先,connect by和level都是为了快速的查询层级关系的关键字,在代理关系中,或者权限关系中,经常会有层层嵌套的场景,比如,同行数据的第一个字段是ID,第二个字段是parentID,parentID表示他的上级ID是谁。**
  1. 如一条数据,ID为1,parentID为0,表示没有上级,即顶层,grade 为老板

  2. 第二条数据,ID为2,parentID为1,表示上级为1,grade 为总监

  3. 第三条数据,ID为3,parentID为2,表示上级为2,grade 为经理

    这就是一个三级的层级关系事例。
    

    在这种情况下,如果想知道某个:比如 一级代理的所有二级代理和三级代理的有关行是谁,就很麻烦了。如果使用mysql数据库,就有两种方法

MYSQL

** 第一种是在java代码或者别的service层,或工具层中编写有关层级关系解析的方法代码,然后把解析的数据放在redis或者map中(如果不放在redis 或者map中,数据量大的时候,每次调用关系都要重新算一遍是很大的消耗)。**

** 第二种是在mysql数据库中编写相对的存储过程,在select 查询的时候调用自己封装好的方法,获取相应的数据库信息。**

但是oracle 中,则很贴心的提供了对应的方法来解决上述的问题,这就是关键字:***connect by ***

*** 其实简单来说,其基本语法是***

** select …,level from tablename**

** start with 条件1
connect by prior 父字段id=子字段id
where 条件3;**

** 例子:**

** SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3173
CONNECT BY PRIOR a.FUNCID = a.PARENTID;**

** 以上述模板为例,LEVEL是查询的层级,START WITH a.FUNCID=3173,表示从该条件开始寻找层级关系,CONNECT BY PRIOR a.FUNCID=a.PARENTID 代表,优先按照a.FUNCID当做基础,该例中(PARENTID的值,绑定的是FUNCID),所以查找FUNCID=3173的所有儿子孙子,及其层级(儿子LEVEL是2,孙子LEVEL是3)**

** 如果变成CONNECT BY a.FUNCID= PRIOR a.PARENTID,则代表起始为a.FUNCID=3173,优先按照PARENTID查找,即寻找FUNCID=3173这条数据的所有父节点行**

** 这里有点难以理解,但是其实就是如果,PRIOR 放在父字段端,就是寻找STRAT WITH 标注节点的所有的子节点,如果PRIOR 放在子字段端,就是寻找STRAT WITH 标注节点的所有的父节点**

** 因为一个儿子只有一个父亲,而一个父亲可能有多个儿子,所以当PRIOR放在父字段端的时候,可能有多个层级族,因为在找儿子。当放在子字段端的时候,最多只会有一个层级族,因为,他在找爸爸。**

关于 connect by 和level 还有startwith 的详细介绍和延伸,我在学习过程中,找到了两个很好的博客,在此就不转载了,直接转网址了,以供以后自己学习和看此篇博客的人学习。

   oracle connect by用法篇:[https://blog.csdn.net/wang_yunj/article/details/51040029/](https://blog.csdn.net/wang_yunj/article/details/51040029/)

   Oracle中start with...connect by子句的用法:[http://www.blogjava.net/xzclog/archive/2010/03/05/314642.html](http://www.blogjava.net/xzclog/archive/2010/03/05/314642.html)

    看完这两篇博客,应该是能懂很多东西的,我这里在尝试和学习的过程中,也做了一些事例,如果在有oracle数据库,且想很快形象的学习有关这块知识,也可以直接在oracle中执行我的事例代码,配上注释,应该能简单不少。

# oracle-按月、日、时分组查询数据,为空的数据补零

------月


`select` `nvl(t1.tvalue, 0) ``"data1"``, t2.datevalue ``"name"`

`from` `(``select` `sum``(t.TSAI03) tvalue, TO_CHAR(t.TSAI01, ``'yyyy-mm'``) timevalue`

`from` `TSA009 t`

`where` `TO_CHAR(t.TSAI01, ``'YYYY-MM-DD'``) ``like` `'2012%'`

`and` `t.unit_code ``like` `'411500A0050000'`

`group` `by` `TO_CHAR(t.TSAI01, ``'yyyy-mm'``)) t1,`

`(``select` `'2012-'` `|| lpad(``level``, 2, 0) datevalue`

`from` `dual`

`connect` `by` `level` `< 13) t2`

`where` `t1.timevalue(+) = t2.datevalue`

`order` `by` `t2.datevalue`

 |

-----日

| 

`select` `nvl(t1.tvalue, 0)` `"data1"``, t2.datevalue` `"name"`

`from` `(``select` `sum``(t.TSAI03) tvalue,`

`TO_CHAR(t.TSAI01,` `'yyyy-mm-dd'``) timevalue`

`from` `TSA009 t`

`where` `TO_CHAR(t.TSAI01,` `'YYYY-MM-DD'``)` `like` `'2012-04%'`

`and` `t.unit_code` `like` `'411500A0050000'`

`group` `by` `TO_CHAR(t.TSAI01,` `'yyyy-mm-dd'``)) t1,`

`(``select` `'2012-04-'` `|| lpad(``level``, 2, 0) datevalue`

`from` `dual`

`connect` `by` `level` `< (``select` `to_number(substr(last_day(to_date(``'2012-04-10'``,`

`'yyyy-mm-dd'``)),`

`0,`

`2))`

`from` `dual) + 1) t2`

`where` `t1.timevalue(+) = t2.datevalue`

`order` `by` `t2.datevalue`

 |

----时

| 

`select` `nvl(t1.tvalue, 0)` `"data1"``, t2.datevalue` `"name"`

`from` `(``select` `sum``(t.TSAJ03) tvalue,`

`TO_CHAR(t.TSAJ01,` `'yyyy-mm-dd hh24'``) timevalue`

`from` `TSA010 t`

`where` `TO_CHAR(t.TSAJ01,` `'YYYY-MM-DD'``)` `like` `'2012-04-10%'`

`and` `t.unit_code` `like` `'411500A0050000'`

`group` `by` `TO_CHAR(t.TSAJ01,` `'yyyy-mm-dd hh24'``)) t1,`

`(``select` `'2012-04-10 '` `|| lpad(``level``, 2, 0) datevalue`

`from` `dual`

`connect` `by` `level` `< 25) t2`

`where` `t1.timevalue(+) = t2.datevalue`

`order` `by` `t2.datevalue`

# oracle 中(+)是什么意思

原文内容:
oracle中的(+)是一种特殊的用法,(+)表示外连接,并且总是放在非主表的一方。

例如

左外连接:select A.a,B.a from A LEFT JOIN B ON A.b=B.b;

等价于select A.a,B.a from A,B where A.b = B.b(+);

再举个例子,这次是右外连接:select A.a,B.a from A RIGHT JOIN B ON A.b=B.b;

等价于select A.a,B.a from A,B where A.b (+) = B.b;

个人补充:

数据表的连接有:
1、内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现
2、[外连接]: 包括
(1)左外连接
(2)右外连接
(3)全外连接(左右两表都不加限制)
3、[自连接]连接发生在一张基表内)

有 (+) 的一方代表有可以为空,即副表

oracle中的(+)是一种特殊的用法,(+)表示外连接,并且总是放在非主表的一方。例如左外连接:select A.a,B.a from A LEFT JOIN B [ON] A.b=B.b;等价于select A.a,B.a from A,B where A.b = B.b(+);再举个例子,这次是右外连接:

select A.a,B.a from A RIGHT JOIN B  on A.b=B.b;
等价于
select A.a,B.a from A,B where A.b (+) = B.b;

# Decode与NVL和NVL2区别

Decode

decode(条件,值1,翻译值1,值2,翻译值2,...,缺省值) 该函数与程序中的 If...else if...else 意义一样

NVL

格式:NVL( string1, replace_with)

功能:如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL。

注意事项:string1和replace_with必须为同一数据类型,除非显式的使用TO_CHAR函数进行类型转换。

select nvl(sum(t.dwxhl),1) from tb_jhde t 就表示如果sum(t.dwxhl) = NULL 就返回 1

Oracle在NVL函数的功能上扩展,提供了NVL2函数

NVL2

**nvl2 **(E1, E2, E3) 的功能为:如果E1为NULL,则函数返回E3,否则返回E2

结合Decode 和 NVL等函数 常常结合使用,例如

select monthid,decode(nvl(sale,6000),6000,'NG','OK') from output

扩展知识

sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1,

如果取较小值就是
select monthid,decode(sign(sale-6000),-1,sale,6000) from output,即达到取较小值的目的。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 203,772评论 6 477
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,458评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 150,610评论 0 337
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,640评论 1 276
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,657评论 5 365
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,590评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,962评论 3 395
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,631评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,870评论 1 297
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,611评论 2 321
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,704评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,386评论 4 319
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,969评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,944评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,179评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 44,742评论 2 349
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,440评论 2 342

推荐阅读更多精彩内容