代码之家 › 专栏 › 技术社区 › 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

推荐文章

Nezir · WHERE and子句不返回值

7 年前

user285594 · MySQL-如何在where子句中应用以匹配多个值?

7 年前

Shambhu · 未使用'where'子句,但出现错误:“where子句中的未知列<col\u name>”

7 年前

Selvam · Teradata SQL-条件取决于WHERE子句中的条件

7 年前

NormX · 在窗体访问上打印预览到当前记录

7 年前

fishy101 · where子句中的差异-in和not in

7 年前

Nidhin Kumar · 带或的Sqlite Where子句显示已删除的记录

7 年前

geeth · 使用ActiveRecord计算id匹配的行数,另一列匹配两个值之一

7 年前

dmbb · 是否可以添加第二个where条件来选择相同但来自其他日期范围的数据?

7 年前

Jessica Warren · 使用proc sql,其中至少有1列是一个值

7 年前