问题：

select列表中的列无效，因为它包含在聚合函数或GROUP BY子句中

雍志文

2023-03-14

我知道这个主题已经讨论了类似的问题，我已经看过了答案，但不幸的是，我只是不知道如何应用于这个查询。我知道将'pe.short_name'添加到GROUP BY也不是处理这个问题的最佳方法。非常感谢任何建议。

列'pe.short_name'在select列表中无效,因为它包含在聚合函数或GROUP BY子句中.`

    SELECT CASE 
        WHEN SUBSTRING(s.description, 13, 1) = '.'
            THEN LEFT(s.description, 17)
        ELSE LEFT(s.description, 12)
        END WBSCode
    ,pe.short_name
    ,pn.description ProjectName
    ,(
        SELECT description
        FROM structure
        WHERE structure_code = pe.code346
        ) ActivePortfolio
    ,CASE 
        WHEN ast.Description = 'WAIO'
            THEN anc.Description
        ELSE site.Description
        END SiteFunction
    ,CASE 
        WHEN SUBSTRING(jv.description, 5, 1) = ' '
            THEN LEFT(jv.description, 4)
        WHEN SUBSTRING(jv.description, 7, 1) = ' '
            THEN LEFT(jv.description, 6)
        ELSE jv.Description
        END AS JointVenture
    ,(
        SELECT description
        FROM structure
        WHERE structure_code = pe.code79
        ) AssetClassification
    ,(
        SELECT description
        FROM structure
        WHERE structure_code = pe.code390
        ) InvestmentSize
    ,(
        SELECT RIGHT(import_code, 6)
        FROM structure
        WHERE structure_code = pe.code79
        ) AssetClassCode
    ,CAST(p.period_start AS DATE) Period_Start
    ,SUM(CASE 
            WHEN be.currency_code = 'AUD'
                AND @Currency = 'AUD'
                THEN be.amount
            WHEN be.currency_code = 'USD'
                AND @Currency = 'USD'
                THEN be.amount
            WHEN be.currency_code = 'AUD'
                AND @Currency = 'USD'
                THEN be.amount * ip.pfn_exch_rate_on_date(p.period_start, be.currency_code)
            WHEN be.currency_code = 'USD'
                AND @Currency = 'AUD'
                THEN be.amount * (ip.pfn_exch_rate_on_date(p.period_start, be.currency_code) / ip.pfn_exch_rate_on_date(p.period_start, 'AUD'))
            END) Amount
FROM fm_budget_entry be
JOIN fm_period p ON be.period_id = p.period_id
JOIN fm_budget_line bl ON be.line_id = bl.line_id
    AND bl.version_id = @FinancialVersion
JOIN fm_budget_line_attrib la ON la.line_id = bl.line_id
    AND la.line_attrib_code = 'Wbs138'
JOIN structure s ON la.line_attrib_value = s.structure_code
JOIN fm_account fa ON bl.account_code = fa.account_code
JOIN planning_entity pe ON pe.planning_code = bl.structure_code
JOIN structure ast ON ast.structure_code = pe.code713
    AND ast.Description IN (@Assets)
JOIN structure pn ON pn.structure_code = pe.planning_code
LEFT JOIN structure site ON site.structure_code = pe.code37
LEFT JOIN structure_tree tr ON tr.dsc_code = pe.planning_code
    AND tr.anc_Depth = 5
LEFT JOIN structure anc ON anc.structure_code = tr.anc_code
LEFT JOIN fm_version v ON v.version_id = bl.version_id
LEFT JOIN structure pc ON pc.structure_code = s.father_code
LEFT JOIN structure jv ON pc.father_code = jv.structure_code
    AND jv.father_code <> 'Wbs138Root'
WHERE fa.account_type_code IN (@FundingType)
    AND p.period_id BETWEEN @StartDate
        AND @FinishDate
    AND pe.code346 IN (@ActivePortfolio)
GROUP BY CASE 
        WHEN SUBSTRING(s.description, 13, 1) = '.'
            THEN LEFT(s.description, 17)
        ELSE LEFT(s.description, 12)
        END
    ,pn.description
    ,pe.code346
    ,CASE 
        WHEN ast.Description = 'WAIO'
            THEN anc.Description
        ELSE site.Description
        END
    ,CASE 
        WHEN SUBSTRING(jv.description, 5, 1) = ' '
            THEN LEFT(jv.description, 4)
        WHEN SUBSTRING(jv.description, 7, 1) = ' '
            THEN LEFT(jv.description, 6)
        ELSE jv.Description
        END
    ,pe.code79
    ,pe.code390
    ,CAST(p.period_start AS DATE)
ORDER BY 1
    ,CAST(p.period_start AS DATE)

共有1个答案

侯兴为

2023-03-14

试试看：

SELECT CASE 
        WHEN SUBSTRING(s.description, 13, 1) = '.'
            THEN LEFT(s.description, 17)
        ELSE LEFT(s.description, 12)
        END WBSCode
    ,pe.short_name
    ,pn.description ProjectName
    ,(
        SELECT description
        FROM structure
        WHERE structure_code = pe.code346
        ) ActivePortfolio
    ,CASE 
        WHEN ast.Description = 'WAIO'
            THEN anc.Description
        ELSE site.Description
        END SiteFunction
    ,CASE 
        WHEN SUBSTRING(jv.description, 5, 1) = ' '
            THEN LEFT(jv.description, 4)
        WHEN SUBSTRING(jv.description, 7, 1) = ' '
            THEN LEFT(jv.description, 6)
        ELSE jv.Description
        END AS JointVenture
    ,(
        SELECT description
        FROM structure
        WHERE structure_code = pe.code79
        ) AssetClassification
    ,(
        SELECT description
        FROM structure
        WHERE structure_code = pe.code390
        ) InvestmentSize
    ,(
        SELECT RIGHT(import_code, 6)
        FROM structure
        WHERE structure_code = pe.code79
        ) AssetClassCode
    ,CAST(p.period_start AS DATE) Period_Start
    ,SUM(CASE 
            WHEN be.currency_code = 'AUD'
                AND @Currency = 'AUD'
                THEN be.amount
            WHEN be.currency_code = 'USD'
                AND @Currency = 'USD'
                THEN be.amount
            WHEN be.currency_code = 'AUD'
                AND @Currency = 'USD'
                THEN be.amount * ip.pfn_exch_rate_on_date(p.period_start, be.currency_code)
            WHEN be.currency_code = 'USD'
                AND @Currency = 'AUD'
                THEN be.amount * (ip.pfn_exch_rate_on_date(p.period_start, be.currency_code) / ip.pfn_exch_rate_on_date(p.period_start, 'AUD'))
            END) Amount
FROM fm_budget_entry be
JOIN fm_period p ON be.period_id = p.period_id
JOIN fm_budget_line bl ON be.line_id = bl.line_id
    AND bl.version_id = @FinancialVersion
JOIN fm_budget_line_attrib la ON la.line_id = bl.line_id
    AND la.line_attrib_code = 'Wbs138'
JOIN structure s ON la.line_attrib_value = s.structure_code
JOIN fm_account fa ON bl.account_code = fa.account_code
JOIN planning_entity pe ON pe.planning_code = bl.structure_code
JOIN structure ast ON ast.structure_code = pe.code713
    AND ast.Description IN (@Assets)
JOIN structure pn ON pn.structure_code = pe.planning_code
LEFT JOIN structure site ON site.structure_code = pe.code37
LEFT JOIN structure_tree tr ON tr.dsc_code = pe.planning_code
    AND tr.anc_Depth = 5
LEFT JOIN structure anc ON anc.structure_code = tr.anc_code
LEFT JOIN fm_version v ON v.version_id = bl.version_id
LEFT JOIN structure pc ON pc.structure_code = s.father_code
LEFT JOIN structure jv ON pc.father_code = jv.structure_code
    AND jv.father_code <> 'Wbs138Root'
WHERE fa.account_type_code IN (@FundingType)
    AND p.period_id BETWEEN @StartDate
        AND @FinishDate
    AND pe.code346 IN (@ActivePortfolio)
GROUP BY CASE 
        WHEN SUBSTRING(s.description, 13, 1) = '.'
            THEN LEFT(s.description, 17)
        ELSE LEFT(s.description, 12)
        END
    ,pe.short_name
    ,pn.description
    ,pe.code346
    ,CASE 
        WHEN ast.Description = 'WAIO'
            THEN anc.Description
        ELSE site.Description
        END
    ,CASE 
        WHEN SUBSTRING(jv.description, 5, 1) = ' '
            THEN LEFT(jv.description, 4)
        WHEN SUBSTRING(jv.description, 7, 1) = ' '
            THEN LEFT(jv.description, 6)
        ELSE jv.Description
        END
    ,pe.code79
    ,pe.code390
    ,CAST(p.period_start AS DATE)
ORDER BY 1
    ,CAST(p.period_start AS DATE)

类似资料：

select列表中的列无效，因为它不包含在聚合函数或GROUP BY子句中

我们有一个表，它将捕获每个的刷卡记录。我正在尝试编写一个查询，以获取今天第一次刷取的非重复员工记录列表。我们正在列中保存刷卡日期信息。下面是我的查询，它抛出异常。获取错误：列“interface.dbo.vwempSwipeDetail.Employee First Name”在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。有什么需要帮忙的吗？提前道谢。
列“*”在select列表中无效，因为它不包含在聚合函数或GROUP BY子句中

我有一个如下所示的查询但当我执行上面的sql时,出现了一个错误：预期结果：上面的SQL有什么问题？
Hibernate SQLServer2017列在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中

好的，我正在使用hibernate在我的SpringBoot应用程序中运行下面的SQL本机查询，请注意这个查询在SQL Management Studio中运行良好，并返回一些结果。在我的IDE中，我可以使用JDBC驱动程序连接到我的SQL Server2017，我仍然可以运行这个查询并返回一些结果，现在是奇怪的部分，当相同的查询在应用程序中运行时，我会得到以下错误：我实际上得到了适当的输出，那
在选择列表中，“列原因”无效，因为该列原因未包含在聚合函数或GROUP BY子句中。

问题内容：我有一个错误- 选择列表中的“ Employee.EmpID”列无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。这种情况符合Bill Karwin的回答。上面的更正，适合ExactaBox的回答- 原始问题- 对于SQL查询- 我不明白为什么会收到此错误。我要做的就是加入表格，然后将特定位置的所有员工分组在一起。我认为我对自己的问题有部分解释。告诉我是否还可
该列在选择列表中无效，因为该列未包含在聚合函数或GROUP BY子句中

问题内容：我正在尝试返回一个表格，其中包含使用嵌套集模型表示的层次结构中节点的深度，我正在按照本教程进行操作，但是“查找节点的深度”部分中使用的查询对我不起作用：http：//mikehillyer.com/articles/managing- hierarchical-data-in-mysql/ 运行此查询，我得到一个错误“ 列’CompanyGroup.GroupName’在选择列表中无效
JPA2CriteriaQuery在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中

这不会：

select列表中的列无效，因为它包含在聚合函数或GROUP BY子句中

共有1个答案

相关问答

相关文章

相关阅读

相关工具

相关文档