Linq到SQL-加入,组和总和[英] LINQ to SQL - Join, Group, and Sum

本文是小编为大家收集整理的关于Linq到SQL-加入,组和总和的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有以下SQL,可以按预期工作:

SELECT  p.ACCT_ID AS [Acct], 
    a.ACCT_NAME AS [AcctName], 
    p.PD_NO AS [Period], 
    p.FY_CD AS [FY], 
    SUM(CUR_AMT) AS [CActual], 
    SUM(CUR_BUD_AMT) AS [CBudget], 
    SUM(YTD_AMT) AS [YActual], 
    SUM(YTD_BUD_AMT) as [YBudget] 
FROM BudgetTotals p 
INNER JOIN BudgetAccounts a 
    ON p.ACCT_ID = a.ACCT_ID WHERE p.ACCT_ID IN ('610','620','630','634','641','642','643','644','646','665','620','DFC','DFR','DGN','DTX') 
    AND FY_CD == "2013" 
    AND PD_NO == 12 
    AND POOL_NO == 23 
GROUP BY p.ACCT_ID, a.ACCT_NAME, p.PD_NO, p.FY_CD;

我试图将上述内容转换为linq,并使用以下内容:

var query = from p in db.POOL_SIE_SUPPORTs
            join c in db.ACCTs on p.ACCT_ID equals c.ACCT_ID
            where arr.Contains(p.ACCT_ID) && p.POOL_NO == 23 && p.FY_CD == "2013" && p.PD_NO == 12
            group p by p.ACCT_ID into s
            select new
            {
                Account = s.Key,
                AccountName = from acct in db.ACCTs select new { acct.ACCT_NAME },
                CTDActual = string.Format("{0:C}", s.Sum(y => y.CUR_AMT)),
                CTDBudget = string.Format("{0:C}", s.Sum(y => y.CUR_BUD_AMT)),
                YTDActual = string.Format("{0:C}", s.Sum(y => y.YTD_AMT)),
                YTDBudget = string.Format("{0:C}", s.Sum(y => y.YTD_BUD_AMT))
           };

如何获得查询来返回列AccountName?

推荐答案

这假设每个给定的acct_id只有一个acct_name,否则您将获得多个记录,就像您进行组时:

var query = from p in db.POOL_SIE_SUPPORTs
            join c in db.ACCTs on p.ACCT_ID equals c.ACCT_ID
            where arr.Contains(p.ACCT_ID) && p.POOL_NO == 23 && p.FY_CD == "2013" && p.PD_NO == 12
            group p by new { p.ACCT_ID, p.ACCT_NAME } into s
            select new
            {
                Account = s.Key.ACCT_ID,
                AccountName = s.Key.ACCT_NAME, 
                CTDActual = string.Format("{0:C}", s.Sum(y => y.CUR_AMT)),
                CTDBudget = string.Format("{0:C}", s.Sum(y => y.CUR_BUD_AMT)),
                YTDActual = string.Format("{0:C}", s.Sum(y => y.YTD_AMT)),
                YTDBudget = string.Format("{0:C}", s.Sum(y => y.YTD_BUD_AMT))
           };

本文地址:https://www.itbaoku.cn/post/1556803.html

问题描述

I have the following SQL that works as expected:

SELECT  p.ACCT_ID AS [Acct], 
    a.ACCT_NAME AS [AcctName], 
    p.PD_NO AS [Period], 
    p.FY_CD AS [FY], 
    SUM(CUR_AMT) AS [CActual], 
    SUM(CUR_BUD_AMT) AS [CBudget], 
    SUM(YTD_AMT) AS [YActual], 
    SUM(YTD_BUD_AMT) as [YBudget] 
FROM BudgetTotals p 
INNER JOIN BudgetAccounts a 
    ON p.ACCT_ID = a.ACCT_ID WHERE p.ACCT_ID IN ('610','620','630','634','641','642','643','644','646','665','620','DFC','DFR','DGN','DTX') 
    AND FY_CD == "2013" 
    AND PD_NO == 12 
    AND POOL_NO == 23 
GROUP BY p.ACCT_ID, a.ACCT_NAME, p.PD_NO, p.FY_CD;

I'm trying to convert the above to Linq to Sql with the following:

var query = from p in db.POOL_SIE_SUPPORTs
            join c in db.ACCTs on p.ACCT_ID equals c.ACCT_ID
            where arr.Contains(p.ACCT_ID) && p.POOL_NO == 23 && p.FY_CD == "2013" && p.PD_NO == 12
            group p by p.ACCT_ID into s
            select new
            {
                Account = s.Key,
                AccountName = from acct in db.ACCTs select new { acct.ACCT_NAME },
                CTDActual = string.Format("{0:C}", s.Sum(y => y.CUR_AMT)),
                CTDBudget = string.Format("{0:C}", s.Sum(y => y.CUR_BUD_AMT)),
                YTDActual = string.Format("{0:C}", s.Sum(y => y.YTD_AMT)),
                YTDBudget = string.Format("{0:C}", s.Sum(y => y.YTD_BUD_AMT))
           };

How can I get my query to return the column AccountName?

推荐答案

This assumes there is only one Acct_Name per given Acct_Id, otherwise you'll get multiple records just like when you do a group by:

var query = from p in db.POOL_SIE_SUPPORTs
            join c in db.ACCTs on p.ACCT_ID equals c.ACCT_ID
            where arr.Contains(p.ACCT_ID) && p.POOL_NO == 23 && p.FY_CD == "2013" && p.PD_NO == 12
            group p by new { p.ACCT_ID, p.ACCT_NAME } into s
            select new
            {
                Account = s.Key.ACCT_ID,
                AccountName = s.Key.ACCT_NAME, 
                CTDActual = string.Format("{0:C}", s.Sum(y => y.CUR_AMT)),
                CTDBudget = string.Format("{0:C}", s.Sum(y => y.CUR_BUD_AMT)),
                YTDActual = string.Format("{0:C}", s.Sum(y => y.YTD_AMT)),
                YTDBudget = string.Format("{0:C}", s.Sum(y => y.YTD_BUD_AMT))
           };