sql - SQL Join - 无法消除笛卡尔结果
问题描述
我不太清楚如何消除我加入的笛卡尔结果。我以为我已经成功创建了一个子查询，但显然不是。我正在使用一张桌子。我正在尝试根据交易代码 50 并在 12 月编写一个反映上一年数字的列。
不幸的是，当我在任何其他月份有交易 50 时，我会在该 50 上得到笛卡尔结果。
我认为笛卡尔结果是由于与我的“开”标准多次匹配，所以我真的试图限制它，如你所见。它没有像我预期的那样工作。
这是我的代码 - 如何删除笛卡尔结果？
SELECT DataList.[State]
,DataList.[Symbol]
,DataList.[ClaimNumber]
,DataList.[AgentState]
,DataList.[Agent]
,DataList.[Adjuster]
,DataList.[Transactions]
,DataList.[WrittenDate]
,DataList.[EffectiveDate]
,DataList.[LossDate]
,DataList.[ReportedDate]
,sum(DataList.[Amount]) as Amount
,sum(EOYResvs.[EOYRESVAmount]) as EOYResvAmount
,DataList.[MajorPeril]
,DataList.[Class]
,DataList.[AmountCover]
,DataList.[DedAmount]
,DataList.[DedType]
,DataList.[Cause]
,DataList.[LossType]
,DataList.[EarthquakeDedPerc]
,DataList.[WindHailDed]
,DataList.[Territory]
,DataList.[County]
,DataList.[LocationState]
,DataList.[Zip5]
,DataList.[AdjustIND]
,DataList.[ClaimCode]
,DataList.[FileNumber]
FROM
[Database].[dbo].[Info] DataList
LEFT JOIN
(SELECT
[State]
,[Symbol]
,[ClaimNumber]
,[AgentState]
,[Agent]
,[Adjuster]
,[Transactions]
,[WrittenDate]
,[EffectiveDate]
,[LossDate]
,[ReportedDate]
,[Amount] as EOYRESVAmount
,[MajorPeril]
,[Class]
,[AmountCover]
,[DedAmount]
,[DedType]
,[Cause]
,[LossType]
,[EarthquakeDedPerc]
,[WindHailDed]
,[Territory]
,[County]
,[LocationState]
,[Zip5]
,[AdjustIND]
,[ClaimCode]
,[FileNumber]
FROM
[Database].[dbo].[Info]
WHERE
DATEPART(MONTH, [WrittenDate]) = 12
AND [Transactions] = 50
GROUP BY
[State], [Symbol], [ClaimNumber],
[AgentState], [Agent], [Adjuster], [Transactions],
[WrittenDate], [EffectiveDate], [LossDate], [ReportedDate],
[Amount], [MajorPeril], [Class], [AmountCover], [DedAmount],
[DedType], [Cause], [LossType], [EarthquakeDedPerc],
[WindHailDed], [Territory], [County], [LocationState],
[Zip5], [AdjustIND], [ClaimCode], [FileNumber]) EOYResvs
ON datepart(Year, DataList.[WrittenDate]) - datepart(Year, EOYResvs.[WrittenDate]) = 1 AND
DataList.[State] = EOYResvs.[State] AND
DataList.[Symbol] = EOYResvs.[Symbol] AND
DataList.[ClaimNumber] = EOYResvs.[ClaimNumber] AND
DataList.[AgentState] = EOYResvs.[AgentState] AND
DataList.[Agent] = EOYResvs.[Agent] AND
DataList.[Adjuster] = EOYResvs.[Adjuster] AND
DataList.[Transactions] = EOYResvs.[Transactions] AND
DataList.[EffectiveDate] = EOYResvs.[EffectiveDate] AND
DataList.[LossDate] = EOYResvs.[LossDate] AND
DataList.[ReportedDate] = EOYResvs.[ReportedDate] AND
DataList.[MajorPeril] = EOYResvs.[MajorPeril] AND
DataList.[Class]=EOYResvs.[Class] and
DataList.[AmountCover]=EOYResvs.[AmountCover] and
DataList.[DedAmount]=EOYResvs.[DedAmount] and
DataList.[DedType]=EOYResvs.[DedType] and
DataList.[Cause]=EOYResvs.[Cause] and
DataList.[LossType]=EOYResvs.[LossType] and
DataList.[EarthquakeDedPerc]=EOYResvs.[EarthQuakeDedPerc] and
DataList.[WindHailDed]=EOYResvs.[WindHailDed] and
DataList.[Territory]=EOYResvs.[Territory] and
DataList.[County]=EOYResvs.[County] and
DataList.[LocationState]=EOYResvs.[LocationState] and
DataList.[Zip5]=EOYResvs.[Zip5] and
DataList.[FileNumber]=EOYResvs.[FileNumber]
Group By
DataList.[State]
,DataList.[Symbol]
,DataList.[ClaimNumber]
,DataList.[AgentState]
,DataList.[Agent]
,DataList.[Adjuster]
,DataList.[Transactions]
,DataList.[WrittenDate]
,DataList.[EffectiveDate]
,DataList.[LossDate]
,DataList.[ReportedDate]
,DataList.[MajorPeril]
,DataList.[Class]
,DataList.[AmountCover]
,DataList.[DedAmount]
,DataList.[DedType]
,DataList.[Cause]
,DataList.[LossType]
,DataList.[EarthquakeDedPerc]
,DataList.[WindHailDed]
,DataList.[Territory]
,DataList.[County]
,DataList.[LocationState]
,DataList.[Zip5]
,DataList.[AdjustIND]
,DataList.[ClaimCode]
,DataList.[FileNumber]
Order by DataList.[ClaimNumber], DataList.[WrittenDate]
输出数据样本
这是一个数据样本。除了事务代码为 50 的字段上的笛卡尔结果外，此输出是完美的。数量是错误的，因为它们已乘以 EOYResvs 查询的结果数
WrittenDate EffectiveDate LossDate ReportedDate Amount EOYResvAmount MajorPeril Class AmountInsurance DeductibleAmount DeductibleType Cause LossType EarthquakeDedPerc WindHailDeductible Territory County LocationState Zip5 AdjustIND ClaimCode PolicyNumber
24 A 15360 24 4244 9524 50 12/31/2018 10/5/2018 12/1/2018 12/10/2018 5000 NULL 4400 815622 0 250 0 0 9 0 250 283 87 24 63462 NULL NULL 522434
24 A 15360 24 4244 9524 50 1/31/2019 10/5/2018 12/1/2018 12/10/2018 5000 5000 4400 815622 0 250 0 0 9 0 250 283 87 24 63462 NULL NULL 522434
24 A 15360 24 4244 9524 50 2/28/2019 10/5/2018 12/1/2018 12/10/2018 5000 5000 4400 815622 0 250 0 0 9 0 250 283 87 24 63462 NULL NULL 522434
24 A 15360 24 4244 9524 50 3/31/2019 10/5/2018 12/1/2018 12/10/2018 5000 5000 4400 815622 0 250 0 0 9 0 250 283 87 24 63462 NULL NULL 522434
24 A 15360 24 4244 9524 51 4/25/2019 10/5/2018 12/1/2018 12/10/2018 1432.38 NULL 4400 815622 0 250 0 0 9 0 250 283 87 24 63462 NULL NULL 522434
24 PA 15361 24 1338 9473 55 12/11/2018 8/9/2018 12/6/2018 12/10/2018 5 NULL 2900 786111 0 500 0 0 20 0 500 104 58 24 64658 P NULL 125425
24 PA 15361 24 1338 9473 55 12/13/2018 8/9/2018 12/6/2018 12/10/2018 162.5 NULL 400 786111 0 0 0 0 3 0 0 104 58 24 64658 A NULL 125425
24 PA 15361 24 1338 9473 51 12/17/2018 8/9/2018 12/6/2018 12/10/2018 832.94 NULL 400 786111 0 0 0 0 3 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 55 12/21/2018 8/9/2018 12/6/2018 12/10/2018 162.5 NULL 5000 786111 0 0 0 0 20 0 0 104 58 24 64658 A NULL 125425
24 PA 15361 24 1338 9473 55 12/26/2018 8/9/2018 12/6/2018 12/10/2018 162.5 NULL 2900 786111 0 500 0 0 20 0 500 104 58 24 64658 A NULL 125425
24 PA 15361 24 1338 9473 50 12/31/2018 8/9/2018 12/6/2018 12/10/2018 12000 NULL 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 55 1/8/2019 8/9/2018 12/6/2018 12/10/2018 -162.5 NULL 400 786111 0 0 0 0 3 0 0 104 58 24 64658 A NULL 125425
24 PA 15361 24 1338 9473 55 1/9/2019 8/9/2018 12/6/2018 12/10/2018 605.55 NULL 400 786111 0 0 0 0 3 0 0 104 58 24 64658 A NULL 125425
24 PA 15361 24 1338 9473 50 1/31/2019 8/9/2018 12/6/2018 12/10/2018 948 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 50 2/28/2019 8/9/2018 12/6/2018 12/10/2018 948 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 50 3/31/2019 8/9/2018 12/6/2018 12/10/2018 948 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 50 4/30/2019 8/9/2018 12/6/2018 12/10/2018 948 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 55 5/3/2019 8/9/2018 12/6/2018 12/10/2018 12.6 NULL 400 786111 0 0 0 0 3 0 0 104 58 24 64658 A NULL 125425
24 PA 15361 24 1338 9473 50 5/31/2019 8/9/2018 12/6/2018 12/10/2018 52500 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 50 6/30/2019 8/9/2018 12/6/2018 12/10/2018 52500 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 50 7/31/2019 8/9/2018 12/6/2018 12/10/2018 52500 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 50 8/31/2019 8/9/2018 12/6/2018 12/10/2018 1548 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 50 9/30/2019 8/9/2018 12/6/2018 12/10/2018 1548 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 50 10/31/2019 8/9/2018 12/6/2018 12/10/2018 1548 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 50 11/30/2019 8/9/2018 12/6/2018 12/10/2018 1548 12000 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 51 12/5/2019 8/9/2018 12/6/2018 12/10/2018 932 NULL 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 PA 15361 24 1338 9473 51 12/10/2019 8/9/2018 12/6/2018 12/10/2018 616 NULL 100 786111 0 0 0 0 1 0 0 104 58 24 64658 NULL NULL 125425
24 A 15362 24 4244 9524 50 12/31/2018 10/5/2018 12/1/2018 12/10/2018 5000 NULL 4400 783722 0 250 0 0 9 0 250 283 87 24 63462 NULL NULL 522434
24 A 15362 24 4244 9524 51 1/24/2019 10/5/2018 12/1/2018 12/10/2018 1851.34 NULL 4400 783722 0 250 0 0 9 0 250 283 87 24 63462 NULL NULL 522434
24 PA 15363 24 1361 9519 51 12/11/2018 9/1/2018 11/29/2018 12/10/2018 1309.5 NULL 2900 783223 0 500 0 0 20 0 500 245 10 24 65203 NULL NULL 107908
24 PA 15363 24 1361 9519 51 12/17/2018 9/1/2018 11/29/2018 12/10/2018 152.69 NULL 2900 783223 0 500 0 0 20 0 500 245 10 24 65203 NULL NULL 107908
24 PA 15364 24 3359 9431 55 12/14/2018 9/26/2018 12/7/2018 12/10/2018 177 NULL 4800 700720 0 500 0 0 9 0 500 290 42 24 64740 A NULL 117274
24 PA 15364 24 3359 9431 51 12/27/2018 9/26/2018 12/7/2018 12/10/2018 4972.52 NULL 4800 700720 0 500 0 0 9 0 500 290 42 24 64740 NULL NULL 117274
24 PA 15364 24 3359 9431 52 2/11/2019 9/26/2018 12/7/2018 12/10/2018 1324 NULL 4800 700720 0 500 0 0 9 0 500 290 42 24 64740 NULL NULL 117274
```
解决方案
这是你加入的奇怪标准。这似乎是由于您的表格过于通用造成的。之所以称为信息，是因为它既不是交易，也不是索赔，也不是文件，而是可以包含一件事或另一件事，甚至是组合的东西？这使得查询数据变得困难。
看来你想得到这样的东西：
SELECT
i.*,
isum.sum_eoyresvamount
FROM database.dbo.info i
LEFT JOIN
(
SELECT
<all columns except for WrittenDate and Transactions>,
DATEPART(YEAR, writtenDate) AS written_year,
SUM(eoyresvamount) AS sum_eoyresvamount
FROM database.dbo.info
WHERE DATEPART(MONTH, writtenDate) = 12
AND transactions = 50
GROUP BY <all columns except for WrittenDate and Transactions>
) isum ON isum.written_year = DATEPART(YEAR, i.writtendate) - 1
AND isum.state = i.state
AND <compare all other columns except for WrittenDate and Transactions>
ORDER BY i.claimnumber, i.writtendate;
推荐阅读
- regex - 在 grep/pgrep 过滤器中不使用 -v 不是“x”或“y”？
- ansible - 如何使用 Ansible 从另一个列表中删除子列表
- php - 邮递员中的PHP服务器响应低于1000MS，但不是通过设备应用程序
- kubernetes - 如何指定 ServiceAccount 由 kubernetes python sdk 中的 config.load_incluster_config() 获取
- python - 我应该使用夹具来执行设置功能吗？
- python - StackingClassifier 导致“ValueError：输入形状错误”错误
- python - 使用 Python 将 ssh 客户端与 Paramiko 连接到 Dell idrac 失败键盘交互式身份验证
- java - 测试帐号中是否仅存在一个连字符
- java - 在 O(1) 中检查 LinkedHashMap 中 2 个不同键的顺序
- asp.net-core-mvc - 使用 OpenIDConnect 时如何注销 ClaimsIdentity 用户