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

Question

查询;

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子句中的 。

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

Answer 1

使用..

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 

Answer 2

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

GROUP BY CharID,CharName16