帮助处理复杂的联接查询

发布于 2024-10-15 21:57:22 字数 723 浏览 2 评论 0原文

请记住，我使用的是 SQL 2000，

我有两个表。

tblAutoPolicyList 包含一个名为 PolicyIDList 的字段。

tblLossClaims 包含两个名为LossPolicyID 和LossPolicyID 的字段。 政策审查。

我正在编写一个存储过程，它将从 PolicyIDList 字段获取不同的 PolicyID ，并循环遍历 LossPolicyID 字段（如果找到匹配，则设置 <代码>PolicyReview 为“Y”）。

示例表布局：

PolicyIDList        LossPolicyID 
9651XVB19       5021WWA85, 4421WWA20, 3314WWA31, 1121WAW11, 2221WLL99       Y
5021WWA85       3326WAC35, 1221AXA10, 9863AAA44, 5541RTY33, 9651XVB19       Y
0151ZVB19       4004WMN63, 1001WGA42, 8587ABA56, 8541RWW12, 9329KKB08       N

我将如何编写存储过程（寻找逻辑而不是语法）？

请记住我使用的是 SQL 2000。

原文

Keep in mind I am using SQL 2000

I have two tables.

tblAutoPolicyList contains a field called PolicyIDList.

tblLossClaims contains two fields called LossPolicyID & PolicyReview.

I am writing a stored proc that will get the distinct PolicyID from PolicyIDList field, and loop through LossPolicyID field (if match is found, set PolicyReview to 'Y').

Sample table layout:

PolicyIDList        LossPolicyID 
9651XVB19       5021WWA85, 4421WWA20, 3314WWA31, 1121WAW11, 2221WLL99       Y
5021WWA85       3326WAC35, 1221AXA10, 9863AAA44, 5541RTY33, 9651XVB19       Y
0151ZVB19       4004WMN63, 1001WGA42, 8587ABA56, 8541RWW12, 9329KKB08       N

How would I go about writing the stored proc (looking for logic more than syntax)?

Keep in mind I am using SQL 2000.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

看海 2024-10-22 21:57:22

选择 LossPolicyID, * from tableName where charindex('PolicyID',LossPolicyID,1)>0

回复收藏 0 原文

无人接听 2024-10-22 21:57:22

基本上，想法是这样的：

'Unroll' tblLossClaims 并返回两列：一个 tblLossClaims 键（您没有提到任何内容，所以我猜它会为 LossPolicyID），Item = LossPolicyID 中的单个项目。
在 tblAutoPolicyList.PolicyIDList 中查找 unrolled.Item 的匹配项。
在 tblLossClaims.LossPolicyID 中查找不同 matched.LossPolicyID 的匹配项。
相应地更新tblLossClaims.PolicyReview。

主要更新可能如下所示：

UPDATE claims
   SET PolicyReview = 'Y'
  FROM tblLossClaims claims
  JOIN (
       SELECT DISTINCT unrolled.LossPolicyID
         FROM (
              SELECT LossPolicyID, Item = itemof(LossPolicyID)
                FROM unrolling_join
              ) unrolled
         JOIN tblAutoPolicyList
           ON unrolled.ID = tblAutoPolicyList.PolicyIDList
       ) matched
    ON matched.LossPolicyID = claims.LossPolicyID

您可以利用固定项目宽度和固定列表格式，从而无需 UDF 即可轻松拆分 LossPolicyID。我可以在数字表和 SUBSTRING() 的帮助下看到这一点。上面查询中的unrolling_join实际上是tblLossClaims与数字表连接。

以下是展开“放大”的定义：

...
(
SELECT LossPolicyID,
       Item = SUBSTRING(LossPolicyID,
                        (v.number - 1) * @ItemLength + 1,
                        @ItemLength)
  FROM tblLossClaims c
  JOIN master..spt_values v ON v.type = 'P'
   AND v.number BETWEEN 1 AND (LEN(c.LossPolicyID) + 2) / (@ItemLength + 2)
) unrolled
...

master..spt_values 是一个系统表，此处用作数字表。过滤器 v.type = 'P' 为我们提供了一个数字值从 0 到 2047 的行集，该行集范围缩小到从 1 到 LossPolicyID 中的项目数的数字列表代码>.最终v.number充当数组索引并用于剪切单个项目。

@ItemLength 当然只是LEN(tblAutoPolicyList.PolicyIDList)。我可能还会声明 @ItemLength2 = @ItemLength + 2 因此每次应用过滤器时都不会计算它。

基本上就是这样了，如果我没有遗漏什么的话。

Basically, the idea is this:

'Unroll' tblLossClaims and return two columns: a tblLossClaims key (you didn't mention any, so I guess it's going to be LossPolicyID) and Item = a single item from LossPolicyID.
Find matches of unrolled.Item in tblAutoPolicyList.PolicyIDList.
Find matches of distinct matched.LossPolicyID in tblLossClaims.LossPolicyID.
Update tblLossClaims.PolicyReview accordingly.

The main UPDATE can look like this:

UPDATE claims
   SET PolicyReview = 'Y'
  FROM tblLossClaims claims
  JOIN (
       SELECT DISTINCT unrolled.LossPolicyID
         FROM (
              SELECT LossPolicyID, Item = itemof(LossPolicyID)
                FROM unrolling_join
              ) unrolled
         JOIN tblAutoPolicyList
           ON unrolled.ID = tblAutoPolicyList.PolicyIDList
       ) matched
    ON matched.LossPolicyID = claims.LossPolicyID

You can take advantage of the fixed item width and the fixed list format and thus easily split LossPolicyID without a UDF. I can see this done with the help of a number table and SUBSTRING(). unrolling_join in the above query is actually tblLossClaims joined with the number table.

Here's the definition of unrolled 'zoomed in':

...
(
SELECT LossPolicyID,
       Item = SUBSTRING(LossPolicyID,
                        (v.number - 1) * @ItemLength + 1,
                        @ItemLength)
  FROM tblLossClaims c
  JOIN master..spt_values v ON v.type = 'P'
   AND v.number BETWEEN 1 AND (LEN(c.LossPolicyID) + 2) / (@ItemLength + 2)
) unrolled
...

master..spt_values is a system table that is used here as the number table. Filter v.type = 'P' gives us a rowset with number values from 0 to 2047, which is narrowed down to the list of numbers from 1 to the number of items in LossPolicyID. Eventually v.number serves as an array index and is used to cut out single items.

@ItemLength is of course simply LEN(tblAutoPolicyList.PolicyIDList). I would probably also declared @ItemLength2 = @ItemLength + 2 so it wasn't calculated every time when applying the filter.

Basically, that's it, if I haven't missed anything.

回复收藏 0 原文

雨后彩虹 2024-10-22 21:57:22

如果 PolicyIDList 字段是分隔列表，您必须首先分隔各个策略 ID，并创建一个包含所有结果的临时表。接下来，在 tblLossClaims 上使用 'where isn't (select * from #temptable tt where tt.PolicyID = LossPolicyID) 的更新查询。

根据表/数据的大小，您可能希望向临时表添加索引。

回复收藏 0 原文

~没有更多了~