SQL Server SQL语句-更新记录

Question

我有一个数据，数据如下：

​

​

我需要使用column id, region, company, dept, subdept and amountsepend更新Matching_id和Matching_Type。逻辑是这样的：

Sum AmountSepend by Region, Company, Dept and SubDept。如果sum为0，则Matching_Type为'Match‘，Matching_id为匹配记录的id的组合，否则为'Not Match’，Matching_id为id。**SUM表示无论AmountSepend为正还是负，相同条件下所有记录的总和。

另一个重要的标准是，如果交易是单记录，意味着按地区、公司、部门和SubDept分组的总计数为1，则匹配类型不匹配，Matching_UID为id，而不管AmountSepend为0或正/负值。示例id 8。

以下是输出：

​

这里是表和数据脚本

CREATE TABLE [dbo].[StackoverflowQuest](
[id] [int] NOT NULL,
[Region] [varchar](50) NULL,
[Company] [varchar](50) NULL,
[Dept] [varchar](50) NULL,
[SubDept] [varchar](50) NULL,
[AmountSepend] [float] NULL,
[Matching_id] [varchar](100) NULL,
[Matching_Type] [varchar](100) NULL
) ON [PRIMARY]

我怎么能达到这样的效果呢？如有任何帮助/提示，我们将不胜感激

Answer 1

CREATE TABLE #Table(Id INT,Region VARCHAR(100),Company INT,Dept INT,SubDept       
INT,AmtSpend INT,MatchingId VARCHAR(100),MatchingType VARCHAR(100))

INSERT INTO #Table(Id ,Region ,Company , Dept  ,SubDept ,AmtSpend )
SELECT 1,'NAM',12378,1,NULL,900 UNION ALL
SELECT 2,'NAM',12378,1,NULL,-900 UNION ALL
SELECT 3,'NAM',12370,1,23,1000 UNION ALL
SELECT 4,'ASA',1234,9,12,5000 UNION ALL
SELECT 5,'NAM',12370,1,23,-1000 UNION ALL
SELECT 6,'ASA',1234,9,12,800 UNION ALL
SELECT 7,'ASA',1234,9,12,-600 UNION ALL
SELECT 8,'ASA',12311,6,NULL,200

UPDATE #Table SET MatchingId = MatchIds,MatchingType = 'Match'
FROM 
(
  SELECT T2.Company,STUFF( ( SELECT ',' + CAST(T3.Id AS VARCHAR) FROM #Table    
  T3 WHERE T2.Company = T3.Company FOR XML PATH('')),1,1,'') MatchIds
  FROM #Table T2
  JOIN 
    (
    SELECT T1.Company Company,SUM(T1.AmtSpend) Total
    FROM #Table T1
    GROUP BY T1.Company
    HAVING SUM(T1.AmtSpend) = 0
  )A ON A.Company = T2.Company
    GROUP BY T2.Company
 ) A 
WHERE A.Company = #Table.Company


UPDATE #Table SET MatchingId = CAST(Id AS VARCHAR),MatchingType = 'Not  
Match' WHERE ISNULL(MatchingId,'') = ''

SELECT * FROM #Table