oracle判断语句-查询部门的工会

摘要：
需求：如何查询不同级别部门的工会

-1其他问题

1 .网上的一种case when 是再case后面加上一个字段，后来发现这种方法不行

select t1.id id,t1.name name1,
case   t1.PARENT_ID
when null then t1.id 
else t1.PARENT_ID 
end PARENT1,

2.关于自连接
2.1.首先自连接其实就是笛卡尔连接
2.2.自连接和非自连接其实是一样的，只是表相同而已
2.3 左连接和右连接其实都是自连接的子集
2.4三张表的连接 ，运算顺序可以任意
3.嵌套select 一般用于这种情况

<#if dept_id??>
and bti.dept_id=(select id from sys_p_department where name=[dept_id])

不能用于这种情况
select id from sys_p_department where name=bti.name])
4.层级查询里面的start with 动态设置的问题
http://www.itpub.net/thread-1710588-1-1.html

0 判断语句

https://blog.csdn.net/yangzjchn/article/details/81019449
http://www.voidcn.com/article/p-rnjumhws-hr.html

1.NVL 空值这种判断语句 也用过 但是这种方法有缺陷

1.思路：

本来想要oracle 层级的方法去解决，但是我实在是找不到如何让start with 的条件 放在连接里面动态获取。
最后的方案：用判断和连接
1.先构建一张表 保存不同部门的工会，表的结构如下
1 2 3 对于子节点
2 3 3 对于父节点
3 3 3 对于爷爷节点

2.然后和其他的表连接查询即可

2 核心代码（用case when）：

查询部门的父节点
//最后的方案
//
1 2 3 对于子节点
2 3 3 对于父节点
3 3 3 对于爷爷节点

select t1.id id,t1.name name1,
    case 
    when t1.PARENT_ID is null then t1.id 
    else t1.PARENT_ID 
    end PARENT1,
    case 
    when t2.name is null then t1.name 
    else t2.name
    end name2,
    case
    when t2.PARENT_ID is null and t1.PARENT_ID is null  then t1.id 
    when t2.PARENT_ID is null and t1.PARENT_ID is not null  then t1.PARENT_ID 
    else t2.PARENT_ID
    end parent3,
    case 
    when t3.name is null and t2.name is null then t1.name 
    when t3.name is null and t2.name is not null then t1.name 
    else t3.name
    end name3
    from SYS_P_DEPARTMENT_3 t1
    left join  SYS_P_DEPARTMENT_3 t2 on t1.PARENT_ID=t2.id
    left join  SYS_P_DEPARTMENT_3 t3 on t2.PARENT_ID=t3.id

3. 完整代码

<?xml version="1.0" encoding="UTF-8"?>

<query-config>
	<query id="Q_TRADE_LIST" type="pagelist" result="easyui">
		<stmt>
			<![CDATA[
			select bti.account,bti.name,ttt.name3 dept_name_2,to_char(bti.intrade_date,'yyyy-mm-dd') intrade_date,bti.birthday,
			decode(bti.trade_status,0,'未入会',1,'已入会',2,'已退会',3,'欠费被退会')trade_status,bti.leaguer_no,
			decode(bti.leaguer_type,1,'编制人员',2,'非编制人员',3,'类型3',4,'类型4',5,'类型5')leaguer_type,
			decode(spu.ryzt,0,'在职',1,'报到中',2,'离退休',3,'离校',4,'去世',4,'其他')ryzt,
			decode(bti.sex,1,'男',0,'女')sex,bti.card_no,bti.nation,bti.email,bti.post,bti.duties,bti.memo,
            bti.tele_phone,bti.trade_memo,
            	spc1.NAME political_status,
				spc2.NAME card_type,
				spc3.NAME qualifications,
				spc4.NAME degree
			from bi_trade_info bti
			left join
			(select tt1.id id,tt1.name name1,
			case
			when tt1.PARENT_ID is null then tt1.id
			else tt1.PARENT_ID
			end PARENT1,
			case
			when tt2.name is null then tt1.name
			else tt2.name
			end name2,
			case
			when tt2.PARENT_ID is null and tt1.PARENT_ID is null  then tt1.id
			when tt2.PARENT_ID is null and tt1.PARENT_ID is not null  then tt1.PARENT_ID
			else tt2.PARENT_ID
			end parent3,
			case
			when tt3.name is null and tt2.name is null then tt1.name
			when tt3.name is null and tt2.name is not null then tt2.name
			else tt3.name
			end name3
			from SYS_P_DEPARTMENT tt1
			left join  SYS_P_DEPARTMENT tt2 on tt1.PARENT_ID=tt2.id
			left join  SYS_P_DEPARTMENT tt3 on tt2.PARENT_ID=tt3.id
			)ttt on bti.dept_id=ttt.id
				left join sys_p_department spd on bti.dept_id=spd.id
			left join sys_p_user spu on bti.account=spu.account
			left join SYS_PA_CODE spc1 on BTI.POLITICAL_STATUS=SPC1.CODE_NUM and spc1.CODE_TABLE='BI_TRADE_INFO' and SPC1.CODE_FIELD='POLITICAL_STATUS'
            left join SYS_PA_CODE spc2 on BTI.CARD_TYPE =SPC2.CODE_NUM and spc2.CODE_TABLE='BI_TRADE_INFO' and SPC2.CODE_FIELD='CARD_TYPE'
            left join SYS_PA_CODE spc3 on BTI.QUALIFICATIONS =SPC3.CODE_NUM and spc3.CODE_TABLE='BI_TRADE_INFO' and SPC3.CODE_FIELD='QUALIFICATIONS'
            left join SYS_PA_CODE spc4 on BTI.DEGREE =SPC4.CODE_NUM and spc4.CODE_TABLE='BI_TRADE_INFO' and SPC4.CODE_FIELD='DEGREE'
            left join (select spd1.id id,spd1.PARENT_ID PARENT_ID,spd2."NAME"  from sys_p_department spd1, sys_p_department spd2 where spd1.parent_id=spd2.id)spd_spd on bti.dept_id=spd_spd.id
			where 1=1
			<#if dept_id??>
			  and bti.dept_id=(select id from sys_p_department where name=[dept_id])
			</#if>
			<#if account??>
			  and bti.account like([account])
			</#if>
			<#if name??>
			  and bti.name like([name])
			</#if>
			<#if card_no??>
			  and bti.card_no like([card_no])
			</#if>
			<#if leaguer_type??>
			  and bti.leaguer_type=[leaguer_type]
			</#if>
			<#if ryzt??>
			  and spu.ryzt=[ryzt]
			</#if>
			<#if sex??>
			  and bti.sex=[sex]
			</#if>
			<#if trade_status??>
			  and bti.trade_status=[trade_status]
			</#if>
			<#if start_date??>
			   and TO_CHAR(bti.intrade_date,'yyyy-mm-dd') >= [start_date]
			</#if>
			<#if end_date??>
			   and TO_CHAR(bti.intrade_date,'yyyy-mm-dd') <= [end_date]
			</#if>
			order by bti.intrade_date desc,bti.rowid desc
			]]>
		</stmt>
		<param name="paging">true</param>
		<param name="exp-cols">dept_name_2,account,name,sex,birthday,card_type,card_no,political_status,nation,tele_phone,email,qualifications,degree,post,duties,trade_status,intrade_date,leaguer_type,memo,ryzt</param>
		<param name="exp-names">所属工会,工号,姓名,性别,出生年月,证件类型,证件号,政治面貌,民族,电话,邮箱,学历,学位,岗位,职务职称,会籍状态,入会时间,会员类型,备注,人员状态</param>
		<param name="exp-file">投稿情况</param>
	</query>
</query-config>