SQL Server中多条件关联匹配的Claim计数去重问题

• Claim.source 对应 rules.systemType
• conditions.ruleId 对应 rules.ruleId
• 一条规则可包含多个条件，查询要求Claim必须匹配某规则的所有条件才被统计

期望结果 #

ruleId | claimsCount | systemType
-------+-------------+------------
1      | 1           | AAA
2      | 1           | AAA
3      | 4           | PPP
4      | 1           | AAA

问题描述 #

Rule 2包含两个条件，当前查询会将匹配该规则的Claim统计两次，需实现仅统计一次的效果。

表结构与测试数据 #

CREATE TABLE [dbo].[Claim]
(
    [claimId] [int] NOT NULL,
    [payerId] [nvarchar](50) NOT NULL,
    [CreatedDate] [datetime] NULL,
    [source] [nvarchar](3) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Claim] 
    ADD PRIMARY KEY CLUSTERED ([claimId] ASC)
GO

ALTER TABLE [dbo].[Claim] 
    ADD DEFAULT (GETDATE()) FOR [CreatedDate]
GO

INSERT INTO claim VALUES (101, '12345', GETDATE(),'AAA')
INSERT INTO claim VALUES (102, '54321', '2025-03-01 18:22:27.687','AAA')
INSERT INTO claim VALUES (103, '12345', GETDATE(),'PPP')
INSERT INTO claim VALUES (104, '54321', GETDATE(),'PPP')
INSERT INTO claim VALUES (105, '12345', '2025-03-01 18:22:27.687','PPP')
INSERT INTO claim VALUES (106, '55555', '2025-02-01 18:22:27.687','PPP')

CREATE TABLE [dbo].[rules] 
(
    [ruleId] [int] NOT NULL,
    [systemType] [nvarchar](5) NOT NULL,
    [enabled] [varchar](1) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[rules] 
    ADD PRIMARY KEY CLUSTERED ([ruleId] ASC)
GO

INSERT INTO rules VALUES (1, 'AAA', 'Y')
INSERT INTO rules VALUES (2, 'AAA', 'Y')
INSERT INTO rules VALUES (3, 'PPP', 'Y')
INSERT INTO rules VALUES (4, 'AAA', 'Y')
INSERT INTO rules VALUES (5, 'PPP', 'Y')

CREATE TABLE [dbo].[conditions]
(
    [condId] [int] NOT NULL,
    [ruleId] [int] NULL,
    [conditionalType] [nvarchar](25) NOT NULL,
    [conditionalOperator] [nvarchar](25) NOT NULL,
    [conditionalValue] [nvarchar](25) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[conditions] 
    ADD PRIMARY KEY CLUSTERED ([condId] ASC)
GO

INSERT INTO conditions VALUES (1, 1, 'payerId', 'equals', '12345')

INSERT INTO conditions VALUES (2, 2, 'payerId', 'equals', '54321')
INSERT INTO conditions VALUES (3, 2, 'createdDate', 'greater_than_or_equal', '2025-03-01')

INSERT INTO conditions VALUES (4, 3, 'payerId', 'equals', '54321')
INSERT INTO conditions VALUES (5, 4, 'createdDate', 'equals', '2025-03-06')
INSERT INTO conditions VALUES (6, 5, 'createdDate', 'equals', '2025-02-01')

当前环境与失效查询 #

使用SQL Server Developer版本及Management Studio，多次调整以下查询语句均无法得到预期结果：

SELECT
    rul.ruleId,
    COUNT(clm.claimId) AS 'claimCount',
    --clm.claimId,
    rul.systemType
FROM
    claim clm
INNER JOIN
    rules rul ON clm.source = rul.systemType 
INNER JOIN
    conditions con ON rul.ruleId = con.ruleId 
                   AND ((con.conditionalType = 'payerId' AND  
                         con.conditionalValue = clm.payerId) 
                        OR
                        ((con.conditionalType = 'createdDate' 
                          AND con.conditionalOperator = 'equals' 
                          AND CONVERT(VARCHAR(10), con.conditionalValue, 101) = CONVERT(VARCHAR(10), clm.CreatedDate, 101)) 
                        OR
                        (con.conditionalType = 'createdDate' 
                         AND con.conditionalOperator = 'greater_than_or_equal' 
                         AND CONVERT(VARCHAR(10), con.conditionalValue, 101) >= CONVERT(VARCHAR(10), clm.CreatedDate, 101)) 
                        OR
                        (con.conditionalType = 'createdDate' 
                         AND con.conditionalOperator = 'less_than_or_equal'  
                         AND CONVERT(VARCHAR(10), con.conditionalValue, 101) <= CONVERT(VARCHAR(10), clm.CreatedDate, 101))) 
    )  
WHERE
    rul.enabled = 'Y'
GROUP BY
    rul.ruleId, rul.systemType--, clm.claimId
ORDER BY 
    rul.ruleId;

内容的提问来源于stack exchange，提问作者kalconX13