主页 / dba / 问题 / 310927
Accepted
GeoH
Asked: 2022-04-14 08:42:41 +0800 CST

我有一个表，其中每个 project_id 生成多行

  • 772

我的下表显示了使用 CASE 语法过滤列数据以区分资本和费用的行和列。问题是每个project_ID 我得到多行。我需要每个 project_ID 一行： 在此处输入图像描述

我需要输出为：

在此处输入图像描述 这是我的 Oracle SQl（包括上面未显示的许多其他列）：

SELECT
    pi.project_id,
    pi.project_projectnumber, 
    pi.project_projectname, 
    cst.cost1,

    CASE WHEN bi.code = '01-00-000' THEN  cst.ubr_ROM_Approved
         ELSE NULL
    END AS CAPITAL_ROM,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubr_ROM_Approved
         ELSE NULL
    END AS EXPENSE_ROM,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_CD_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_CD,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_SD_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_SD,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_DD_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_DD,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_Budget_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_Budget,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_CD_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_CD,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_SD_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_SD,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_DD_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_DD,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_Budget_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_Budget


FROM turnerb_upi_view pi
INNER JOIN turnerb_sys_project_info_view pv ON (pv.pid = pi.project_id)
INNER JOIN turnerb_us_p1_view prj ON (prj.pid = pi.project_id)
INNER JOIN turnerb_budget_summary_viewb cst ON (cst.projectid = pi.project_id)
INNER JOIN turnerb_ubr_view rom ON (rom.project_id = pi.project_id)
INNER JOIN turnerb_ubacf_view badj ON (badj.project_id = pi.project_id)
INNER JOIN turnerb_budgetitem bi ON (bi.budgetid = cst.budgetid)

WHERE pi.project_status = '1' 
AND rom.status ='ROM_Approved' 
AND (badj.status = 'SD_Approved' 
     OR badj.status = 'CD_Approved' 
     OR badj.status = 'DD_Approved' 
     OR badj.status = 'Budget_Approved')

GROUP BY 
    pi.project_id,
    pi.project_projectnumber, 
    pi.project_projectname, 
    bi.code,
    cst.cost1,
    cst.ubr_ROM_Approved,
    cst.ubacf_CD_Approved,
    cst.ubacf_SD_Approved,
    cst.ubacf_DD_Approved,
    cst.ubacf_Budget_Approved

ORDER BY project_projectnumber;
oracle case

2 个回答

  1. 您希望在输出中唯一的列或表达式必须包含在GROUP BY. 需要聚合的列，或构成要聚合的表达式的列，不得在GROUP BY.

    您的查询包含的列比快照中的简化示例多得多，并且您没有详细说明哪些列在结果集中应该是唯一的。因此，我将在这里猜测。在我看来，这些GROUP BY术语应该是您SELECT条款中的前四列：

    pi.project_id,
pi.project_projectnumber, 
pi.project_projectname, 
cst.cost1,

    您应该保留这些GROUP BY并删除所有其他。至于SELECT子句，所有的CASE表达式都需要用SUMs 包裹起来。以下是您需要应用于查询的所有更改：

    SELECT
    pi.project_id,
    pi.project_projectnumber, 
    pi.project_projectname, 
    cst.cost1,

    SUM(  /* same expression, but aggregated */
        CASE WHEN bi.code = '01-00-000' THEN  cst.ubr_ROM_Approved
             ELSE NULL
        END
    ) AS CAPITAL_ROM,  

    SUM(  /* same expression, but aggregated */
        CASE WHEN bi.code = '02-00-000' 
             THEN cst.ubr_ROM_Approved
             ELSE NULL
        END
    ) AS EXPENSE_ROM,  

    ... /* follow the above pattern for the rest of the columns */

FROM ... /* no changes here */

WHERE ... /* no changes here */

GROUP BY
    /* only these four need to be GROUP BY terms */
    pi.project_id,
    pi.project_projectnumber, 
    pi.project_projectname, 
    cst.cost1

ORDER BY project_projectnumber;
    • 1
  2. Best Answer

    要获得您需要的输出，只需要

    SELECT project_id, SUM(capital_rom), SUM (expense_rom), ...
   ...
   GROUP BY project_id

    我没有看到你想要的输出和你的陈述之间的联系......

    • -1

相关问题