SQL中基于两列的SUMing

Question

第一个帖子很抱歉!我:

<表类> 参与者价值规则期tbody><<tr>詹姆斯0规则12022-12-1詹姆斯0规则12022-01-1詹姆斯1规则12022-02-1詹姆斯1规则22022-12-1詹姆斯1规则22022-01-1詹姆斯1规则22022-02-1

以下建议对你有帮助

SELECT [TR1].[PARTICIPANT], SUM([TR1].[VALUE]) AS [VALUE], [TR1].[Period]
FROM 
(
SELECT [PARTICIPANT], SUM([VALUE]) AS [VALUE], [RULE], [Period]
FROM [TABLE]
WHERE [RULE]='RULE 1'
GROUP BY [PARTICIPANT], [RULE], [Period]
) [TR1] LEFT JOIN
(
SELECT [PARTICIPANT], SUM([VALUE]) AS [VALUE], [RULE], [Period]
FROM [TABLE]
WHERE [RULE]='RULE 2'
GROUP BY [PARTICIPANT], [RULE], [Period]
) [TR2]
ON [TR1].[PARTICIPANT] = [TR2].[PARTICIPANT] AND 
[TR1].[Period] = [TR2].[Period]
WHERE [TR1].[VALUE] >= COALESCE([TR2].[VALUE], 0)
GROUP BY [TR1].[PARTICIPANT], [TR1].[Period]

看起来您想要按PERIOD进行条件聚合，然后仅按PARTICIPANT进行分组

SELECT
t.PARTICIPANT,
COUNT(CASE WHEN t.Rule1 >= t.Rule2 THEN 1 END) Rule1MoreThan
FROM (
SELECT
t.PARTICIPANT,
t.PERIOD,
SUM(CASE WHEN t.[RULE] = 'RULE 1' THEN t.VALUE END) Rule1,
SUM(CASE WHEN t.[RULE] = 'RULE 2' THEN t.VALUE END) Rule2
FROM [TABLE] t
GROUP BY
t.PARTICIPANT,
t.PERIOD
) t
GROUP BY
t.PARTICIPANT;

,db&lt的在小提琴