久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

與多個(gè)表的分層查詢匹配具有挑戰(zhàn)性

hierarchical query match with multiple tables its challenging(與多個(gè)表的分層查詢匹配具有挑戰(zhàn)性)
本文介紹了與多個(gè)表的分層查詢匹配具有挑戰(zhàn)性的處理方法,對(duì)大家解決問題具有一定的參考價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)吧!

問題描述

我有這個(gè) business_table

I have this business_table

ref_ID      name    parent_id 
-----------------------------
ABC-0001    Amb     NULL 
PQR-899     boss    NULL
tgv-632     pick    NULL
yyy-888     xyz     NULL
kkk-456     ued     NULL

我想更新 business_table 的 parent_id

I want to update parent_id of business_table

parent_customer 是另一個(gè)表,列出了下面給出的 ref_ID 和 parent_id 的層次結(jié)構(gòu).

parent_customer is another table which list the hierarchy of ref_ID and parent_id given below.

更新business_table staps 的parent_id 是

To update the parent_id of business_table staps are

1) 檢查 business_table 的 ref_id 和 parent_customer 的 ref_id.例如.business_table 的 ref_ID ABC-0001 與 parent_customer ref_id 第一行 1 ref_id-ABC-0001 opr-656 匹配找到匹配

1) check ref_id of business_table with ref_id of parent_customer . eg. ref_ID ABC-0001 of business_table match with parent_customer ref_id 1st row 1 ref_id-ABC-0001 opr-656 match found

2) 然后檢查該匹配記錄的 parent_customer 的 parent_id,在這種情況下是 parent_id opr-656 檢查 match_table_CM 表

2) then check parent_id of parent_customer of that matched record which is in this case parent_id opr-656 check with match_table_CM table

match_table_CM 表在更新記錄之前列出了我們想要匹配的 id(我們正在檢查這個(gè),因?yàn)檫@是 CRM id 需要檢查 emplpoyee 是否存在)

match_table_CM table list the ids which we want to match before updating record (we are checking this because of this is CRM id need to check emplpoyee exist of not)

3) 未找到匹配項(xiàng),然后檢查 parent_customer 的 parent_id opr-656 與同一個(gè)表 parent_customer ref_id ,找到 ref_id opr-656 的第二條記錄然后選擇它的 parent_id ttK-668 檢查與 match_table_CM 匹配找到 1 ttK-668 然后用 business_table parent_id 更新其他明智的檢查,直到parent_customer ref_ID = parent_id (parent of all) 并更新該 id 即使沒有找到匹配所以在這種情況下如果沒有找到匹配那么 ttK-668 應(yīng)該是終于更新了

3)match not found then check with parent_id opr-656 of parent_customer with same table parent_customer ref_id , 2nd record found with ref_id opr-656 then pick its parent_id ttK-668 check with match_table_CM match found 1 ttK-668 then update with business_table parent_id other wise check till the parent_customer ref_ID = parent_id (parent of all) and update that id even if match not found so in this case if match not found then ttK-668 is should be updated at last

注意:- parent_customer 表列出了一個(gè)數(shù)據(jù)層次結(jié)構(gòu),其中當(dāng) ref_id 和 parent_id 相同時(shí),表示它是整個(gè)層次結(jié)構(gòu)的父級(jí).

note : - parent_customer table lists a hierarchy of data in which when both ref_id and parent_id are the same means it's the parent of the entire hierarchy.

例如:

4 PQR-899 PQR-899 這是層次結(jié)構(gòu)的最終父級(jí)

4 PQR-899 PQR-899 this is ultimate parent of hierarchy

父客戶

ID  ref_id     parent_id  
---------------------------
1   ABC-0001   opr-656
2   opr-656    ttK-668
3   ttK-668    ttK-668
4   PQR-899    PQR-899
5   kkk-565    AJY-567  
6   AJY-567    UXO-989
7   UXO-989    tgv-632
8   tgv-632    mnb-784 
9   mnb-784    qwe-525 
10  qwe-525    qwe-525
11  kkk-456    jjj-888

match_table_CM:

match_table_CM:

id    main_id
--------------
1     ttK-668
2     PQR-899
3     tgv-632
4     mnb-784

預(yù)期輸出

ref_ID      name    parent_id 
-----------------------------
ABC-0001    Amb     ttK-668                    
PQR-899     boss    PQR-899
tgv-632     pick    qwe-525
yyy-888     xyz     NULL
kkk-456     ued     jjj-888

推薦答案

這應(yīng)該返回預(yù)期的結(jié)果:

This should return the expected result:

WITH hierarchy AS
 ( -- all rows from source table
   SELECT b.ref_id, pc.parent_id, 
      0 AS match,
      1 AS lvl
   FROM business_table AS b
   LEFT JOIN parent_customer AS pc
     ON b.ref_id = pc.ref_id

   UNION ALL

   SELECT h.ref_id, pc.parent_id, 
      -- check if we found a match or reached top of hierarchy
      CASE WHEN mt.main_id IS NOT NULL OR pc.parent_id = pc.ref_id THEN 1 ELSE 0 END,
      lvl+1
   FROM hierarchy AS h
   JOIN parent_customer AS pc 
     ON pc.ref_id = h.parent_id -- going up in the hierarchy
   LEFT JOIN match_table_CM AS mt
     ON mt.main_id = pc.ref_id
   WHERE h.match = 0 -- no match yet
     AND lvl < 10 -- just in case there's an endless loop due to bad data
 )
SELECT * FROM hierarchy AS h
WHERE lvl = 
 ( -- return the last row, matching or not
   SELECT Max(lvl)
   FROM hierarchy AS h2
   WHERE h.ref_id = h2.ref_id
 );

使用 EXISTS 重寫,因?yàn)?SQL Server 在遞歸部分不支持外部聯(lián)接:

Rewrite using EXISTS because SQL Server doesn't support Outer Joins in the recursive part:

WITH hierarchy AS
 ( -- all rows from source table
   SELECT b.ref_id, pc.parent_id, 
      0 AS match,
      1 AS lvl
   FROM business_table AS b
   LEFT JOIN parent_customer AS pc
     ON b.ref_id = pc.ref_id

   UNION ALL

   SELECT h.ref_id, pc.parent_id, 
      -- check if we found a match or reached top of hierarchy
      CASE WHEN exists
            ( select * 
              from match_table_CM AS mt
              where mt.main_id = pc.ref_id
            ) OR pc.parent_id = pc.ref_id
           THEN 1
           ELSE 0
      END,
      lvl+1
   FROM hierarchy AS h
   JOIN parent_customer AS pc 
     ON pc.ref_id = h.parent_id -- going up in the hierarchy
   WHERE h.match = 0 -- no match yet
     AND lvl < 10 -- just in case there's an endless loop due to bad data
 )
SELECT * FROM hierarchy AS h
WHERE lvl = 
 ( -- return the last row, matching or not
   SELECT Max(lvl)
   FROM hierarchy AS h2
   WHERE h.ref_id = h2.ref_id
 );

優(yōu)化器的計(jì)劃看起來很糟糕,因此再次重寫以使用窗口聚合而不是相關(guān)子查詢:

The optimizer's plan looked bad, so another rewrite to use a Windowed Aggregate instead of a Correlated Subquery:

WITH  hierarchy AS
 ( -- all rows from source table
   SELECT b.ref_id, pc.parent_id, 
      0 AS match,
      1 AS lvl
   FROM business_table AS b
   LEFT JOIN parent_customer AS pc
     ON b.ref_id = pc.ref_id

   UNION ALL

   SELECT h.ref_id, pc.parent_id, 
      -- check if we found a match or reached top of hierarchy
      CASE WHEN exists
            ( select * 
              from match_table_CM AS mt
              where mt.main_id = pc.ref_id
            ) OR pc.parent_id = pc.ref_id
           THEN 1
           ELSE 0
      END,
      lvl+1
   FROM hierarchy AS h
   JOIN parent_customer AS pc 
     ON pc.ref_id = h.parent_id -- going up in the hierarchy
   WHERE h.match = 0 -- no match yet
     AND lvl < 10 -- just in case there's an endless loop due to bad data
 )
select *
from 
 ( 
   SELECT h.*,
      max(lvl) over (partition by ref_id) as maxlvl
   FROM hierarchy AS h
 ) as dt
WHERE lvl = maxlvl
;

這篇關(guān)于與多個(gè)表的分層查詢匹配具有挑戰(zhàn)性的文章就介紹到這了,希望我們推薦的答案對(duì)大家有所幫助,也希望大家多多支持html5模板網(wǎng)!

【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題,如果有圖片或者內(nèi)容侵犯了您的權(quán)益,請(qǐng)聯(lián)系我們刪除處理,感謝您的支持!

相關(guān)文檔推薦

Modify Existing decimal places info(修改現(xiàn)有小數(shù)位信息)
The correlation name #39;CONVERT#39; is specified multiple times(多次指定相關(guān)名稱“CONVERT)
T-SQL left join not returning null columns(T-SQL 左連接不返回空列)
remove duplicates from comma or pipeline operator string(從逗號(hào)或管道運(yùn)算符字符串中刪除重復(fù)項(xiàng))
Change an iterative query to a relational set-based query(將迭代查詢更改為基于關(guān)系集的查詢)
concatenate a zero onto sql server select value shows 4 digits still and not 5(將零連接到 sql server 選擇值仍然顯示 4 位而不是 5)
主站蜘蛛池模板: 日韩一级一区 | 欧美一区二区免费视频 | 国产精品影视 | 久久久91精品国产一区二区精品 | 欧美在线一区二区三区 | 国产精品三级 | 爱操av | 午夜网站视频 | 精品国产乱码久久久久久闺蜜 | 亚洲一区二区三区在线免费观看 | 一级毛毛片 | 亚洲精品成人av久久 | 国产精品一码二码三码在线 | 中文字幕在线一区 | 日韩精品一区二区三区中文在线 | 精品视频在线免费观看 | 狠狠色综合欧美激情 | 狠狠综合久久av一区二区小说 | 久久91精品| 天天激情综合 | 中文字幕一区在线 | 一区二区三区在线观看免费视频 | 北条麻妃国产九九九精品小说 | 黄色免费观看 | 在线免费观看黄色av | 国产激情一区二区三区 | 自拍偷拍第一页 | 精品国产一区二区三区成人影院 | www.成人免费视频 | 天天爽综合网 | 国产一区不卡在线观看 | 精品久久久久久 | 欧美性大战xxxxx久久久 | 51ⅴ精品国产91久久久久久 | 成人国产免费视频 | 国产精品视频在线播放 | 亚洲男人天堂av | 性xxxxx | 久久久久久久久久久高潮一区二区 | 亚洲欧美中文日韩在线v日本 | 黄色片在线观看网址 |