SQL查询比较oracle中同一表中的多个列

debugcn 发表于 Dev

天使

我需要在同一张表中找到具有数据差异的emplid。表由50-60列组成。.我需要检查是否有任何列的上一行数据有变化，emplid应该被提取，以及是否有新雇员也需要被提取。

我已经创建了一个基本查询，并且可以正常工作，但是需要某种方式来达到相同的目的，因为我不想写每个列名。

我的查询：

select 
    emplid 
from 
    ps_custom_tbl t, ps_custom_tbl prev_t 
where 
    prev_t.emplid = t.emplid 
    and t.effdt = (select max effdt from ps_custom_tbl t2 
                   where t2.emplid = t.emplid) 
    and prev_t.effdt = (select max(effdt) from ps_custom_tbl prev_t2 
                        where emplid = prev_t.emplid and effdt < t.effdt)
    and (t.first_name prev_t.first_name Or t.last_name prev_t.last_name …. 50 columns);

您能建议另一种实现同一目标的方法吗？

莫妮卡·莱万多斯卡（Monika Lewandowska）

您可以使用MINUS。

如果no_data则相同，如果有一些记录-表示之间存在差异

create table emp as select * from hr.employees;

insert into emp select employee_id+1000, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, 
 decode(department_id ,30,70, department_id)
 from hr.employees;

select first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id  
from emp where employee_id <= 1000
minus
select first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id  
from emp where employee_id > 1000;

但是您必须列出所有列，因为如果您有不同的日期或ID，则也会进行比较。但是，在SELECT子句中列出列然后为每个WHERE条件编写更容易。也许会有所帮助。

-或者，如果要使用不同的表格并希望比较所有列，

drop table emp;
create table emp as select * from hr.employees;
    
create table emp2  as 
select employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, 
 decode(department_id ,30,70, department_id) department_id
 from hr.employees;

select * from emp
minus 
select * from emp2;

----添加日期标准

-是的，您可以添加日期条件并使用分析功能检查哪个–是较新的，哪个是

然后比较一个。如下所示：

drop table emp;
create table emp as select * from hr.employees;
        
insert into emp 
        select
           employee_id, 
           first_name, 
           last_name, 
           email, 
           phone_number, 
           hire_date+1, 
           job_id, 
           salary, 
           commission_pct, 
           manager_id, 
           decode(department_id ,30,70, department_id)
        from hr.employees;
        
     with data as   --- thanks to WITH you retrieve data only once
            (select employee_id, first_name, last_name, email, phone_number, 
                hire_date, 
                row_number() over(partition by employee_id order by hire_date desc) rn  -- distinguish newer and older record, 
               job_id, salary, commission_pct, manager_id, department_id  
        from emp)
        select employee_id, first_name, last_name, email, phone_number, department_id from data where rn  = 1
        MIUNUS--- find the differences
        select employee_id, first_name, last_name, email, phone_number, department_id from data where rn = 2;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。