如何编写case语句，使具有两个不同值的ID在标志列中仅显示一行

我有一个用于从多个表中提取数据的多个连接的查询。作为选择的一部分，我有多个case语句。我被其中一个困住了。我需要添加一个条件，如果Chris和John的PCT为100，并且PCT的'0'或'NULL'行不应该显示，那么我只能为Chris和John各获得一行标记'Y'。

DeptIDEmployee101克里斯100Y101克里斯N

您所描述的是过滤，并且需要WHERE子句。我们可以使用ROW_NUMBER()根据您描述的优先级规则枚举行，然后使用该信息进行过滤。

您没有显示当前查询，所以我假设它来自CTE:

with res as (... your query ...)
select *
from (
select r.*,
row_number() over(
partition by deptid, employee
order by case when pct = 100 and utility_pct = 'Y' then 0 else 1 end, pct, utility_pct
) rn
from res r
) r
where rn = 1

查询将结果限制为每个部门/员工元组一行;当有pct = 100 and utility_pct = 'Y'的行时，选择它，否则我们得到pct和utility_pct最小的行(这可以通过修改窗口函数的order by子句的末尾来适应)。

既然有一个标签'greatest-n-per-group'您可以使用GREATEST()函数筛选行:

Select *
From   tbl t
Where  PCT = GREATEST( (Select PCT From 
(Select DEPT_ID, EMPLOYEE, Max(Nvl(PCT, -1)) "PCT" From tbl Group By DEPT_ID, EMPLOYEE) 
Where DEPT_ID = t.DEPT_ID And EMPLOYEE = t.EMPLOYEE)  )

与您的样本数据:

WITH
tbl AS
(
Select 101 "DEPT_ID", 'Chris' "EMPLOYEE",  100 "PCT", 'Y' "UTILITY_PCT" From Dual Union All
Select 101 "DEPT_ID", 'Chris' "EMPLOYEE", Null "PCT", 'N' "UTILITY_PCT" From Dual Union All
Select 101 "DEPT_ID", 'Sam'   "EMPLOYEE",    0 "PCT", 'N' "UTILITY_PCT" From Dual Union All
Select 101 "DEPT_ID", 'John'  "EMPLOYEE",  100 "PCT", 'Y' "UTILITY_PCT" From Dual Union All
Select 101 "DEPT_ID", 'John'  "EMPLOYEE", Null "PCT", 'N' "UTILITY_PCT" From Dual 
)

…结果是:

<表类>DEPT_ID员工PCTUTILITY_PCTtbody><<tr>101克里斯100Y101山姆0N101约翰100Y

相关内容

最新更新

热门标签：