内连接和许多左连接表的总和值

2022-03-13 00:00:00 sum left-join tsql sql-server inner-join

我有一个复杂的查询，包含一个INNER JOINed表和多个LEFT JOINed表到一个主表。其中一个表有多行与主表的每一行相对应，我希望在SELECT查询中包括这些行的特定字段的SUM()。

当然，sql server开始逐个通知我

未包含在聚合函数或GROUP BY子句中

，所以我开始在GROUP BY子句中添加它们，直到我遇到一个文本字段.在这种情况下，我收到错误

不能比较或排序text、ntext和image数据类型，使用IS NULL或LIKE运算符时除外。

那么，在这种情况下，我怎样才能克服那个障碍呢？

另外，下面还有更复杂的SELECTed字段的情况，那么我该如何克服这种情况呢？

下面我包含整个查询，如果需要，我可以提供一个过于简化的DB提琴，至少在主表和这个LEFT JOINed的一对多表之间.

SELECT sub.SUBSTITUTECODE AS SKU,
       prod.CODE,
       prod.DESCRIPTION AS TITLE,
       prod.REMARKS AS DESCRIPTION, -- <---- This is the text field...
       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, SUM(CASE WHEN ISNULL(qtys.SIZE1, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE1, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE2, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE2, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE3, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE3, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE4, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE4, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE5, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE5, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE6, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE6, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE7, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE7, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE8, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE8, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE9, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE9, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE10, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE10, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE11, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE11, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE12, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE12, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE13, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE13, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE14, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE14, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE15, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE15, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE16, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE16, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE17, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE17, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE18, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE18, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE19, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE19, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE20, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE20, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE21, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE21, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE22, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE22, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE23, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE23, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE24, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE24, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE25, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE25, 0) END)) 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 COLORSIZEQTYS AS qtys ON qtys.ITEID = sub.ITEID AND qtys.COLORCODE = sub.COLORCODE AND qtys.QTYMODE = 1 -- <---- This is the one-to-many table
GROUP BY sub.SUBSTITUTECODE, prod.CODE, prod.DESCRIPTION, prod.REMARKS;

解决方案

您可以从查询中删除分组，然后使用outer apply

我不会重现您的整个查询，但大意是

select...
Choose(sub.SIZEPOS, qtys.s1, qtys.s2...) as Quantities
from SUBSTITUTE as sub
...
outer apply (
    SUM(case when IsNull(qtys.SIZE1, 0) < 0 then 0 else IsNull(qtys.SIZE1, 0) end) s1,
    SUM(case when IsNull(qtys.SIZE2, 0) < 0 then 0 else IsNull(qtys.SIZE2, 0) end) s2...
    from COLORSIZEQTYS qtys 
    where qtys.ITEID = sub.ITEID and qtys.COLORCODE = sub.COLORCODE and qtys.QTYMODE = 1
)qtys

相关文章