Select empname 姓名,empcode 人员编码,orgcode 机构编码,userid 身份证号,empstatus,zhxgsj 变动时间 From (
select distinct u.xm EMPNAME,
u.jh empcode,
u.ksdbm orgcode,
u.sfzh userid,
0 empstatus,
u.bdsj zhxgsj
from pcs_jy_bd@zhcx u
where
u.jh||u.bdsj in (select u.jh||max(u.bdsj) from pcs_jy_bd@zhcx u Where u.jh Is Not Null group by u.jh) And u.bdlx<>2)语句查询后,会出现不同的人员编码,但姓名和身份证号是一样的,想在此基础上再次进行筛选userid,请教如何写?举个例子:
empname empcode orgcode userid empstatus zhxgsj
张三 0001 000001 130000000000000000 0 20101122111111
张三 0002 000001 130000000000000000 0 20101211121114我就想要0002的记录
select distinct u.xm EMPNAME,
u.jh empcode,
u.ksdbm orgcode,
u.sfzh userid,
0 empstatus,
u.bdsj zhxgsj
from pcs_jy_bd@zhcx u
where
u.jh||u.bdsj in (select u.jh||max(u.bdsj) from pcs_jy_bd@zhcx u Where u.jh Is Not Null group by u.jh) And u.bdlx<>2)语句查询后,会出现不同的人员编码,但姓名和身份证号是一样的,想在此基础上再次进行筛选userid,请教如何写?举个例子:
empname empcode orgcode userid empstatus zhxgsj
张三 0001 000001 130000000000000000 0 20101122111111
张三 0002 000001 130000000000000000 0 20101211121114我就想要0002的记录
Select empname 姓名,
empcode 人员编码,
orgcode 机构编码,
userid 身份证号,
empstatus,
zhxgsj 变动时间
From (select distinct u.xm EMPNAME,
u.jh empcode,
u.ksdbm orgcode,
u.sfzh userid,
0 empstatus,
u.bdsj zhxgsj
from pcs_jy_bd@zhcx u
where u.jh || u.bdsj in (select u.jh || max(u.bdsj)
from pcs_jy_bd@zhcx u
Where u.jh Is Not Null
group by u.jh)
And u.bdlx <> 2)
where empcode = '0002'
--------这样不可以吗?
Select empname 姓名,max(empcode) 人员编码,max(orgcode) 机构编码,max(userid) 身份证号,max(empstatus) empstatus,max(zhxgsj) 变动时间 From (
select u.xm EMPNAME,
u.jh empcode,
u.ksdbm orgcode,
u.sfzh userid,
0 empstatus,
u.bdsj zhxgsj
from pcs_jy_bd@zhcx u
where
u.jh||u.bdsj in (select u.jh||max(u.bdsj) from pcs_jy_bd@zhcx u Where u.jh Is Not Null group by u.jh) And u.bdlx<>2)
group by EMPNAME
From
(select u.xm EMPNAME,
u.jh empcode,
u.ksdbm orgcode,
u.sfzh userid,
0 empstatus,
u.bdsj zhxgsj,
row_number() over(partition by u.jh order by u.bdsj desc) rn
from pcs_jy_bd@zhcx u)
where rn=1
Select empname 姓名,empcode 人员编码,orgcode 机构编码,userid 身份证号,empstatus,zhxgsj 变动时间
From
(select u.xm EMPNAME,
u.jh empcode,
u.ksdbm orgcode,
u.sfzh userid,
0 empstatus,
u.bdsj zhxgsj,
u.bdlx,
row_number() over(partition by u.jh order by u.bdsj desc) rn
from pcs_jy_bd@zhcx u)
where rn=1 and bdlx<>2
能同时在jh筛选后也userid筛选吗
empname empcode orgcode userid empstatus zhxgsj
张三 0002 000001 130000000000000000 0 20101122111111
张三 0001 000001 130000000000000000 0 20101211121114我就想要0001的记录
From
(select u.xm EMPNAME,
u.jh empcode,
u.ksdbm orgcode,
u.sfzh userid,
0 empstatus,
u.bdsj zhxgsj,
u.bdlx,
row_number() over(partition by u.jh order by u.bdsj) rn
from pcs_jy_bd@zhcx u)
where rn=1 and bdlx<>2
jh升序取第一条嘛。
Select empname 姓名,empcode 人员编码,orgcode 机构编码,userid 身份证号,empstatus,zhxgsj 变动时间 From (
select distinct u.xm EMPNAME,
u.jh empcode,
u.ksdbm orgcode,
u.sfzh userid,
0 empstatus,
u.bdsj zhxgsj
from pcs_jy_bd@zhcx u
where
u.xm||u.bdsj in (select u.xm||max(u.bdsj) from pcs_jy_bd@zhcx u Where u.jh Is Not Null group by u.xm) And u.bdlx<>2)
0001 也好 0002 也好,都要有个确定的判断条件,这都不说清楚,让人怎么帮你分析?
不好意思,是我没说明白,系统人员编码应该唯一的,姓名会有重复所以不能按姓名,在人员编码筛选后本不应该有我说的问题,可他们数据表中确实存在这种现象,所以在按人员编码筛选后,再按身份证号进行相似的筛选。按你的方法改了下,你看下就知道什么意思了,顺便帮我看下能更简洁不能Select empname,empcode,orgcode,userid,empstatus,zhxgsj
From
(Select empname,empcode,orgcode,userid,empstatus,zhxgsj,row_number() over(partition by userid order by zhxgsj desc) rm
From
(select u.xm EMPNAME,
u.jh empcode,
u.ksdbm orgcode,
u.sfzh userid,
0 empstatus,
u.bdsj zhxgsj,
u.bdlx,
row_number() over(partition by u.jh order by u.bdsj desc) rn
from pcs_jy_bd@zhcx u)
where rn=1 and bdlx<>2 And empcode Is Not Null )
Where rm=1 And userid Is Not Null