我不明白为什么“它不包含在聚合函数或GROUP BY子句中”。错误

问题描述：

SELECT ch.CharID,ch.CharName16, it.OptLevel, obj.ReqLevel1, item.ItemClass 
FROM _Items as it    
LEFT JOIN [dbo].[_Inventory] as inv ON it.ID64 = inv.ItemID 
LEFT JOIN [dbo].[_Char] as ch ON inv.CharID = ch.CharID   
LEFT JOIN [dbo].[_RefObjCommon] as obj ON it.RefItemID = obj.ID   
LEFT JOIN [dbo].[_RefObjItem] as item ON obj.Link = item.ID   
LEFT JOIN [dbo].[_BindingOptionWithItem] as adv ON it.ID64 = adv.nItemDBID    
    WHERE ch.CharName16 IS NOT NULL 
     AND CodeName128 NOT LIKE '%stone%' 
     AND CharName16 NOT LIKE '%]%' 
     AND inv.Slot < 7

查询工作正常，但;

INSERT INTO ItemPoints (
    CharID 
    , CharName16 
    , OptLevel 
    , ReqLevel1 
    , ItemClass 
    , TotalPoint 
    ) 
SELECT CharID 
    , CharName16 
    , SUM(OptLevel) AS OptLevel 
    , SUM(ReqLevel1) AS ReqLevel1 
    , SUM(ItemClass) AS ItemClass 
    , SUM(OptLevel) + SUM(ReqLevel1) + SUM(ItemClass) AS TotalPoint 
FROM (
    SELECT ch.CharID, ch.CharName16, it.OptLevel, obj.ReqLevel1, item.ItemClass 
     FROM _Items as it    
     LEFT JOIN [dbo].[_Inventory] as inv ON it.ID64 = inv.ItemID 
     LEFT JOIN [dbo].[_Char] as ch ON inv.CharID = ch.CharID   
     LEFT JOIN [dbo].[_RefObjCommon] as obj ON it.RefItemID = obj.ID   
     LEFT JOIN [dbo].[_RefObjItem] as item ON obj.Link = item.ID   
     LEFT JOIN [dbo].[_BindingOptionWithItem] as adv ON it.ID64 = adv.nItemDBID    
    WHERE ch.CharName16 IS NOT NULL 
     AND CodeName128 NOT LIKE '%stone%' 
     AND inv.Slot < 7   
    ) tbl 
GROUP BY CharName16

但是sql server给出这个错误;

列'tbl.CharID'在选择列表中无效，因为它不是包含在集合函数或GROUP BY子句中的。

我不明白这个错误，我该如何解决？

您可能需要GROUP BY甚至CharID。 – dario 2015-03-19 10:29:31

您有没有在'INSERT'中故意忽略'AND CharName16 NOT LIKE'％]％''？的 – NickyvV 2015-03-19 10:32:19

可能重复[什么“无效，未包含在聚合函数”的消息呢？]（http://stackoverflow.com/questions/18258704/what-does-the-invalid-not-contained-in-无论是聚合功能消息米） – Tanner 2015-04-29 13:39:02

答

使用..

GROUP BY CharID,CharName16

和尝试。

INSERT INTO ItemPoints (
     CharID 
     , CharName16 
     , OptLevel 
     , ReqLevel1 
     , ItemClass 
     , TotalPoint 
     ) SELECT ch.CharID, ch.CharName16, SUM(it.OptLevel), SUM(obj.ReqLevel1), SUM(item.ItemClass), 
(SUM(it.OptLevel)+ SUM(obj.ReqLevel1) + SUM(item.ItemClass)) total 
      FROM _Items as it    
      LEFT JOIN [dbo].[_Inventory] as inv ON it.ID64 = inv.ItemID 
      LEFT JOIN [dbo].[_Char] as ch ON inv.CharID = ch.CharID   
      LEFT JOIN [dbo].[_RefObjCommon] as obj ON it.RefItemID = obj.ID   
      LEFT JOIN [dbo].[_RefObjItem] as item ON obj.Link = item.ID   
      LEFT JOIN [dbo].[_BindingOptionWithItem] as adv ON it.ID64 = adv.nItemDBID    
     WHERE ch.CharName16 IS NOT NULL 
      AND CodeName128 NOT LIKE '%stone%' 
      AND inv.Slot < 7 group By ch.CharID, ch.CharName16

哦，我的上帝。这很容易：D谢谢。 – Dtractus 2015-03-19 10:31:33

答

必须通过一定的所有值添加到group by条款至极的select子句中使用，而不聚集功能，使您的组：

GROUP BY CharID,CharName16

我不明白为什么“它不包含在聚合函数或GROUP BY子句中”。错误

相关推荐