sql - Oracle sql 创建查询以仅返回一个“员工”的值

Question

我需要帮助如何创建查询以仅针对一个 emp 获得结果。Emp 是查询的参数。

CREATE table abc(
        abc_id number,
        year number,
        month number,
        emp_1 varchar2(100),
        hours_1 number,
        emp_2 varchar2(100),
        hours_2 number,
        emp_3 varchar2(100),
        hours_3 number,
        emp_4 varchar2(100),
        hours_4 number,
        emp_5 varchar2(100),
        hours_5 number
    )


Insert into abc (abc_id,year,month,emp_1,hours_1,emp_2,hours_2,emp_3,hours_3,emp_4,hours_4,emp_5,hours_5) values (1,2021,6,'PELE',8,'PELE',8,'PELE',8,'PELE',8,'PELE',8);
Insert into abc (abc_id,year,month,emp_1,hours_1,emp_2,hours_2,emp_3,hours_3,emp_4,hours_4,emp_5,hours_5) values (2,2021,6,'MESSI',8,'MESSI',8,'RONALDO',8,'RONALDO',8,'RONALDO',8);
Insert into abc (abc_id,year,month,emp_1,hours_1,emp_2,hours_2,emp_3,hours_3,emp_4,hours_4,emp_5,hours_5) values (3,2021,6,'RONALDO',8,'RONALDO',8,null,null,null,null,null,null);
Insert into abc (abc_id,year,month,emp_1,hours_1,emp_2,hours_2,emp_3,hours_3,emp_4,hours_4,emp_5,hours_5) values (4,2021,6,'PELE',8,'PELE',8,'MESSI',7,null,null,null,null);
commit;

如何创建查询以获得结果：

得到所有emp是'MESSI'的地方。

标签： sqloracle

Answer 1

这可能是一个选项（:par_emp表示您将在其中输入员工姓名的参数）：

select abc_id, year, month, :par_emp as emp, 
  case when emp_1 = :par_emp then hours_1 end hours_1,
  case when emp_2 = :par_emp then hours_2 end hours_2,
  case when emp_3 = :par_emp then hours_3 end hours_3,
  case when emp_4 = :par_emp then hours_4 end hours_4,
  case when emp_5 = :par_emp then hours_5 end hours_5
From abc 
where :par_emp in (emp_1, emp_2, emp_3, emp_4, emp_5)
order by abc_id;

---

根据您使用的工具，:par_emp 可能会有所不同。为了说明这一点，我使用 SQL*Plus 和替换变量：

SQL> select abc_id, year, month, '&&par_emp' as emp,
  2    case when emp_1 = '&&par_emp' then hours_1 end hours_1,
  3    case when emp_2 = '&&par_emp' then hours_2 end hours_2,
  4    case when emp_3 = '&&par_emp' then hours_3 end hours_3,
  5    case when emp_4 = '&&par_emp' then hours_4 end hours_4,
  6    case when emp_5 = '&&par_emp' then hours_5 end hours_5
  7  From abc
  8  where '&&par_emp' in (emp_1, emp_2, emp_3, emp_4, emp_5)
  9  order by abc_id;

 ABC_ID  YEAR  MONTH EMP      HOURS_1    HOURS_2    HOURS_3    HOURS_4    HOURS_5
------- ----- ------ ----- ---------- ---------- ---------- ---------- ----------
      2  2021      6 MESSI          8          8
      4  2021      6 MESSI                                7

SQL>

PS谢谢你的测试用例！