SQL查询INNER JOIN与多LEFT JOIN表SUM时text字段GROUP BY报错问询

问题根因 #

SQL Server中的text/ntext/image属于已弃用的大字段类型，不支持排序、比较操作，因此无法直接放入GROUP BY子句。此外你当前采用的「先关联所有表再聚合」的写法本身也存在扩展性问题：一旦SELECT新增字段，就要同步在GROUP BY中补充对应字段，很容易触发各类兼容错误。

立即解决方法 #

方案1：转换text类型（快速兼容原有写法） #

如果prod.REMARKS字段实际存储内容长度不超过8000字节，直接在SELECT和GROUP BY语句中把text类型转成支持GROUP BY的VARCHAR(MAX)（ntext转NVARCHAR(MAX)）即可：

-- SELECT部分修改为
CAST(prod.REMARKS AS VARCHAR(MAX)) AS DESCRIPTION
-- GROUP BY部分对应修改为
GROUP BY sub.SUBSTITUTECODE, prod.CODE, prod.DESCRIPTION, CAST(prod.REMARKS AS VARCHAR(MAX))

方案2：预聚合一对多表（更推荐，兼容复杂场景） #

你只需要对COLORSIZEQTYS表做求和计算，完全可以先单独聚合该表得到和主表主键一一对应的结果，再和其他一对一关联的表关联，完全不需要把主表的非聚合字段放到GROUP BY中，自然也不会遇到text字段的报错。
修改后的SQL示例：

WITH qtys_agg AS (
    SELECT 
        ITEID,
        COLORCODE,
        SUM(CASE WHEN ISNULL(SIZE1, 0) < 0 THEN 0 ELSE ISNULL(SIZE1, 0) END) AS sum_size1,
        SUM(CASE WHEN ISNULL(SIZE2, 0) < 0 THEN 0 ELSE ISNULL(SIZE2, 0) END) AS sum_size2,
        -- 补充SIZE3到SIZE24的SUM逻辑
        SUM(CASE WHEN ISNULL(SIZE25, 0) < 0 THEN 0 ELSE ISNULL(SIZE25, 0) END) AS sum_size25
    FROM COLORSIZEQTYS
    WHERE QTYMODE = 1
    GROUP BY ITEID, COLORCODE
)
SELECT sub.SUBSTITUTECODE AS SKU,
       prod.CODE,
       prod.DESCRIPTION AS TITLE,
       prod.REMARKS AS DESCRIPTION, -- 不需要转换类型，也不需要GROUP BY
       prod.DESCR2 AS SHORTDESCRIPTION,
       manuf.DESCR AS MANUFACTURER,
       CONCAT(UPPER(LTRIM(RTRIM(cat1.DESCR))), '>', UPPER(LTRIM(RTRIM(cat2.DESCR))), '>', UPPER(LTRIM(RTRIM(cat3.DESCR)))) AS CATEGORIES,
       CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,3), CASE WHEN ISNULL(prod.FWHSPRICE, 0) = 0 THEN ISNULL(prod.RTLPRICE, 0) ELSE ISNULL(prod.FWHSPRICE, 0) END)) AS REGULARPRICE,
       CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,3), ISNULL(prod.FLDFLOAT3, 0))) AS SALEPRICE,
       CHOOSE(sub.SIZEPOS, szlist.SIZE1, szlist.SIZE2, szlist.SIZE3, szlist.SIZE4, szlist.SIZE5, szlist.SIZE6, szlist.SIZE7, szlist.SIZE8, szlist.SIZE9, szlist.SIZE10, szlist.SIZE11, szlist.SIZE12, szlist.SIZE13, szlist.SIZE14, szlist.SIZE15, szlist.SIZE16, szlist.SIZE17, szlist.SIZE18, szlist.SIZE19, szlist.SIZE20, szlist.SIZE21, szlist.SIZE22, szlist.SIZE23, szlist.SIZE24, szlist.SIZE25) AS SIZE,
       CHOOSE(sub.SIZEPOS, qtys_agg.sum_size1, qtys_agg.sum_size2, qtys_agg.sum_size3, qtys_agg.sum_size4, qtys_agg.sum_size5, qtys_agg.sum_size6, qtys_agg.sum_size7, qtys_agg.sum_size8, qtys_agg.sum_size9, qtys_agg.sum_size10, qtys_agg.sum_size11, qtys_agg.sum_size12, qtys_agg.sum_size13, qtys_agg.sum_size14, qtys_agg.sum_size15, qtys_agg.sum_size16, qtys_agg.sum_size17, qtys_agg.sum_size18, qtys_agg.sum_size19, qtys_agg.sum_size20, qtys_agg.sum_size21, qtys_agg.sum_size22, qtys_agg.sum_size23, qtys_agg.sum_size24, qtys_agg.sum_size25) AS QUANTITIES,
       col.COLORDESCR AS COLOR,
       prod.FLTID3 AS SIZEGUIDE,
       ISNULL(prod.FLDSTRING1, 0) AS SLIDERNEWPRODUCT,
       ISNULL(prod.FLDFLOAT5, 0) AS SLIDERCUSTOM1,
       ISNULL(prod.FLDFLOAT6, 0) AS SLIDERCUSTOM2,
       prod.zWebLISTSEO AS SEOTITLE,
       prod.zWEBDESCRSEO AS SEODESCRIPTION,
       prod.FLDSTRING2 AS SKROUTZWEIGHT
FROM SUBSTITUTE AS sub
INNER JOIN MATERIAL AS prod ON prod.ID = sub.ITEID AND prod.FLTID1 = 1 AND prod.COMID = 12 AND prod.CODE NOT IN ('FX8707 ', '749747-010 ', '7111653 ', 'ZY9021-12W ', 'ZY9021-12R ', '033123-08B ', '30VERMDBLU ', '053003-10 ', 'M20-73354-10 ', '935088-05 ', '935088-02 ', '216603-RS053 ', '194342-01 ', '1E03550 ', '000024555 ', '071010-01 ', '071933-01 ', 'W9-66333-10 ', 'W9-66333-26 ', 'W9-66338-10 ', '573571-01 ', 'H087Y ', 'FV3407 ', 'D012L ', '358038-04 ', '358038-02 ', '114449-KK001 ', 'BV3636-010 ', 'RC5051PT-28633W ', '106312-06 ', 'DC4054-068 ', '370488-11 ', 'H68096 ', 'AT1801-406 ', 'AT1801-600 ', 'CD3199-455 ', '000925701 ', 'G71616 ', 'CZ8646 ', '42051-BLUE ', '42051-ORANGE ', '42051-YELLOW ', '42054-BLUE ', 'BA5901-610 ', 'F9505-300')
LEFT JOIN ITECOLOR AS col ON col.COLORCODE = sub.COLORCODE AND col.ITEID = sub.ITEID
LEFT JOIN MANUFACTURER AS manuf ON manuf.CODEID = prod.MNFID
LEFT JOIN ITEMCATEGORY AS cat1 ON cat1.CODEID = prod.ICTID AND cat1.COMID = 12
LEFT JOIN ITEMGROUP2 AS cat2 ON cat2.CODEID = prod.IGSID AND cat2.COMID = 12
LEFT JOIN ITEMGROUP AS cat3 ON cat3.CODEID = prod.IGPID AND cat3.COMID = 12
LEFT JOIN SIZELIST AS szlist ON szlist.CODEID = prod.MAINSZLID AND szlist.COMID = 12
LEFT JOIN qtys_agg ON qtys_agg.ITEID = sub.ITEID AND qtys_agg.COLORCODE = sub.COLORCODE

复杂场景通用处理规则 #

• 所有需要聚合的一对多表都优先单独做预聚合（子查询/CTE），得到和主表主键一一对应的结果后再和其他表关联，从根源上避免需要把大量非聚合字段放入GROUP BY的问题。
• 长期建议将库中所有text/ntext/image类型替换为VARCHAR(MAX)/NVARCHAR(MAX)/VARBINARY(MAX)，这几个替代类型支持所有常用字符串操作，也可直接用于GROUP BY，微软早在SQL Server 2005就已宣布旧类型会在未来版本移除，提前替换可避免后续更多兼容问题。
• 如果确实需要在GROUP BY中包含大字段，也可以直接用主表主键作为GROUP BY的唯一条件，其他非聚合字段用MAX()/MIN()包裹（同一个主键下的非聚合字段值唯一，聚合函数不会改变实际结果），同样可以避开大字段GROUP BY的报错。

内容的提问来源于stack exchange，提问作者Faye D.