代码之家 › 专栏 › 技术社区 › Amit Singh Parihar

PL/SQL:需要在select查询中创建条件计算列

calculated-columns where-clause conditional plsql

Amit Singh Parihar · 技术社区 · 7 年前

我试图在select查询中创建条件计算列。逻辑如下所示

   Select  FT.NAME, 
    COUNT(DISTINCT PAGEID) as Total_Forms, 
    COUNT(DISTINCT PAGEID WHERE FT."Column1?" =1) as Expected_forms,
    COUNT(DISTINCT PAGEID WHERE FT."Column1?" =1 AND FT."FORM STATUS" in ('C','I') as Actual_forms,
    COUNT(DISTINCT DATAPAGEID WHERE FT."IS FORM EXPECTED1?" =1)/
    COUNT(DISTINCT DATAPAGEID WHERE FT."IS FORM EXPECTED1?" =1 AND FT."FORM STATUS" in ('C','I'))
     as Percentage
   FROM
       (Subquery) FT
   Group by FT.Name

  name      Total_forms   Expected_forms   Actual Forms  Percentage
  abc        943          811              781           96.54%
  pqr        900          800              600           75.00%

然而,我无法在PL/SQL查询中表达这一点。非常感谢您的帮助。

1 回复 | 直到 7 年前

krokodilko 7 年前

此语法错误:

COUNT(DISTINCT PAGEID WHERE FT."Column1?" =1) as Expected_forms,

使用 CASE Expression 相反:

  SELECT
    COUNT(DISTINCT PAGEID) as Total_Forms, 
    COUNT(DISTINCT CASE WHEN FT."Column1?" =1 THEN PAGEID END ) as Expected_forms,
    COUNT(DISTINCT CASE WHEN FT."Column1?" =1 AND FT."FORM STATUS" in ('C','I') 
                   THEN PAGEID END ) as Actual_forms,
    COUNT(DISTINCT CASE WHEN FT."IS FORM EXPECTED1?" =1 THEN DATAPAGEID  END)/
    COUNT(DISTINCT CASE WHEN FT."IS FORM EXPECTED1?" =1 AND FT."FORM STATUS" in ('C','I') 
                   THEN DATAPAGEID  END) as Percentage
   FROM
       (Subquery) FT
   Group by FT.Name

推荐文章

user2263025 · Azure SQL数据仓库计算列错误

7 年前

Serge Kashlik · 如何在SQL中计算并添加新列

7 年前

Zmnako Awrahman beloncfy · 创建新列:将当前年度季度除以去年季度减1

7 年前

user4172070 · 考虑时间衰减因子,使用其他两列计算新列

7 年前

NonProgrammer · 计算SQL Server 2008R2的同比增长

7 年前

Murray Foxcroft · ALLSELECTED未应用于计算列

7 年前

jester · 创建新列并使用r中的条件计算每个元素

7 年前

Shahar Shokrani · 如何基于另一列定义计算的受约束列

7 年前

Cosmin · 将两个不同表中的几列乘以多个速率(mysql)

7 年前

Dforne · DAX-在计算列中显示前1个计数

7 年前