SQL Server中RID遇到相同行时的合计数量

Question

我已经尝试了几次选择，但我未能得到我想要显示的确切答案。我希望从这里得到灵感。下面是我选择into后的最后一个温度

数量8710889

试试这样-

SELECT rid,  lotid = STUFF(
(SELECT ',' + lotid 
FROM data_table t1
WHERE t1.rid = t2.rid
FOR XML PATH (''))
, 1, 1, '') 
,t2.savid
,sum(t2.qty ) AS qty
FROM data_table t2
WHERE t2.savid <> ''
GROUP BY rid,t2.savid;

您可以在新版本的SQL Server中使用STRING_AGG和正常聚合

SELECT
t.RID,
LOTID = STRING_AGG(t.LOTID, ','),
t.SAVID,
QTY = SUM(t.QTY)
FROM data_table t
WHERE t.SAVID <> ''
GROUP BY
t.RID,
t.SAVID;