时间戳

首页 > 解决方案 > 我需要向我的应用程序添加不属于 GROUPBY 语句的其他列

sql - 我需要向我的应用程序添加不属于 GROUPBY 语句的其他列

问题描述

我需要一些有关我的 SQL 命令的帮助。我有一个 VB.net 应用程序，它使用几个 sql 表来获得最终结果。我需要将 TableC 中的一些额外列添加到我的应用程序中，但这些列不是 GROUPBY 函数的一部分。

我试图在代码中添加简单的选择功能，但我总是得到同样的错误：

$"
SET NOCOUNT ON; 

SELECT CONVERT(VARCHAR(10), r.PossibleDate, 120) as [Date], 
**SELECT r.GStart as GStart,**
SUM(ISNULL(r.ElecCheckIn, 0)) as CheckIn, 
SUM(ISNULL(r.ElecCheckOut, 0)) as CheckOut, 
SUM(ISNULL(r.JPAmount, 0)) as AttAmountJP, 
SUM(ISNULL(r.MeteredAttAmountCC, 0)) as AttAmountCC, 
SUM(ISNULL(r.MeteredMachAmount, 0)) as MachAmount, 
SUM(ISNULL(r.MeteredAttAmount, 0)) as AttAmount, 
SUM(ISNULL(r.ElecCheckIn, 0) - ISNULL(r.ElecCheckOut, 0) - ISNULL(r.JPAmount, 0) - ISNULL(r.MeteredAttAmountCC, 0) - ISNULL(r.MeteredMachAmount, 0) - ISNULL(r.MeteredAttAmount, 0)) as NetWin
FROM dbo.CDS_TableA sm (NOLOCK) 
INNER JOIN dbo.bb_tableB st (NOLOCK) 
ON sm.TableB_Id=st.SlotB_Id 
AND sm.TableBRevision=st.TabelBRevision 
INNER JOIN dbo.TableC r (NOLOCK) 
ON sm.TableA_ID=r.TableA_ID 
AND r.PossibleDate BETWEEN '{dtStart.Value.ToString("yyyy-MM-dd")} 00:00:00' AND '{dtEnd.Value.ToString("yyyy-MM-dd")} 23:59:59'
AND r.Period_ID=4 
INNER JOIN dbo.BB_TableD rh (NOLOCK) 
ON sm.TableA_ID=rh.TableA_ID 
AND r.PossibleDate=rh.PossibleDate 
AND sm.Revision=rh.Revision 
WHERE sm.OnFloorFlag = 1 
AND sm.Calc_ID NOT IN (2,5) 
GROUP BY r.PossibleDate 
ORDER BY r.PossibleDate;

我总是收到一个错误，即 GStart 不包含在聚合函数或 GROUP BY 子句中。

标签： sqlvb.net

解决方案

只需JOIN将聚合级别转换为单元级别，这可以通过 CTE 来促进。运行下面的整个语句，包括WITH子句。

WITH agg AS (
    SELECT CONVERT(VARCHAR(10), r.PossibleDate, 120) as [Date], 
           SUM(ISNULL(r.ElecCheckIn, 0)) as CheckIn, 
           SUM(ISNULL(r.ElecCheckOut, 0)) as CheckOut, 
           SUM(ISNULL(r.JPAmount, 0)) as AttAmountJP, 
           SUM(ISNULL(r.MeteredAttAmountCC, 0)) as AttAmountCC, 
           SUM(ISNULL(r.MeteredMachAmount, 0)) as MachAmount, 
           SUM(ISNULL(r.MeteredAttAmount, 0)) as AttAmount, 
           SUM(ISNULL(r.ElecCheckIn, 0) -
               ISNULL(r.ElecCheckOut, 0) - 
               ISNULL(r.JPAmount, 0) - 
               ISNULL(r.MeteredAttAmountCC, 0) - 
               ISNULL(r.MeteredMachAmount, 0) - 
               ISNULL(r.MeteredAttAmount, 0)) as NetWin
   FROM dbo.CDS_TableA sm (NOLOCK) 
   INNER JOIN dbo.bb_tableB st (NOLOCK) 
           ON sm.TableB_Id =s t.SlotB_Id 
          AND sm.TableBRevision=st.TabelBRevision 
   INNER JOIN dbo.TableC r (NOLOCK) 
           ON sm.TableA_ID= r.TableA_ID 
          AND r.PossibleDate BETWEEN '{dtStart.Value.ToString("yyyy-MM-dd")} 00:00:00' 
                                 AND '{dtEnd.Value.ToString("yyyy-MM-dd")} 23:59:59'
          AND r.Period_ID=4 
   INNER JOIN dbo.BB_TableD rh (NOLOCK) 
           ON sm.TableA_ID=rh.TableA_ID 
          AND r.PossibleDate=rh.PossibleDate 
          AND sm.Revision=rh.Revision 
        WHERE sm.OnFloorFlag = 1 
          AND sm.Calc_ID NOT IN (2,5) 
     GROUP BY r.PossibleDate 
)

SELECT r.GStart as GStart, agg.*    --- ADD OTHER r FIELDS
FROM dbo.TableC r
INNER JOIN agg ON CONVERT(VARCHAR(10), r.PossibleDate, 120) = agg.[Date]
ORDER BY r.PossibleDate

旁白：虽然我一无所知vb.net，但我知道在应用程序层运行 SQL，并且您上面的日期串联应该是参数化值，这是编程行业的最佳实践。请参阅如何创建参数化 SQL 查询？我为什么要？另外，NOLOCK请谨慎使用。

推荐阅读