חיתוך בין רשימות - ilDBA Portal

חיתוך בין רשימות

07/11/2011 | פורסם על ידי

פוסט של משה טייכר בשם שליפת מילים מתוך טקסט בTSQL שהתפרסם לאחרונה עודד אותי לפרסם תגובה עקיפה בפוסט הסרת תווים מיותרים בו הצעתי דרך טובה יותר (לטעמי) לבצע מטלה מקדימה שלו, ואילו הפוסט הזה הוא בעקבות פטנט יפה שלמדתי ממנו – ביצוע Split בעזרת XML, ואשר סייע לי לפתור באופן אלגנטי בעייה שנתקלתי בה בעבר.

נתחיל מבעייה פשוטה יותר שטיפלתי בה בעקיפין מספר פעמים (אופציית Multi-Value ב-Reporting Services, יחס של רבים לרבים ללא טבלת עזר) ואציג אותה כאן בצורה מסודרת: נניח שיש לנו טבלת מכירות של מוכרים (מי מכר מה וכמה) וטבלת עמלות שמגדירה לכל מוכר עבור אילו פריטים יקבל עמלה:

If Object_Id('tempdb..#T_Mehirot','U') Is Not Null Drop Table #T_Mehirot;
Go

Create Table #T_Mehirot(ID Int Identity,
 Moher Varchar(10),
 Prit Varchar(10),
 Camut Int);
Go

Insert
Into  #T_Mehirot
Select      'Hila','Milk',10 Union All
Select      'Hila','Bread',5 Union All
Select      'Hila','Butter',2 Union All
Select      'Hila','Butter',1 Union All
Select      'Gal','Milk',6 Union All
Select      'Gal','Milk',1 Union All
Select      'Gal','Bread',3 Union All
Select      'Gal','Butter',2;
Go

Select      *
From  #T_Mehirot;
Go

If Object_Id('tempdb..#T_Amalot','U') Is Not Null Drop Table #T_Amalot;
Go

Create Table #T_Amalot(Moher Varchar(10),
 Pritim Varchar(Max));
Go

Insert
Into  #T_Amalot
Select      'Hila','Milk,Butter,Bread' Union All
Select      'Gal','Bread';
Go

Select      *
From  #T_Amalot;
Go

כפי שאפשר להבחין- טבלת העמלות (השניה) כוללת רשימה בצורת מחרוזת מופרדת בפסיקים, ולכאורה יש לבצע לה Split כדי שיהיה ניתן ליצור Join בין טבלת המכירות (הראשונה) לבינה. למעשה ניתן לעשות זאת בצורה פשוטה ויעילה יותר כך-

Select      *
From  #T_Mehirot M
Inner Join #T_Amalot A
On M.Moher=A.Moher
And ','+A.Pritim+',' Like '%,'+M.Prit+',%';

הוספתי לרשימה פסיקים משני הצדדים, וחיפשתי התאמות לפריטים הנמכרים בעזרת Like (וגם כאן- פסיקים משני הצדדים).

הבעייה הקשה יותר היא מה לעשות כשיש שתי עמודות עם רשימות מופרדות בפסיקים, ואז לא מדובר בשאלה יש/אין התאמה, אלא מה מתאים; ובמילים פשוטות יותר- להכין רשימת חיתוך של שתי הרשימות עם האיברים המשותפים:

If Object_Id('tempdb..#Tbl','U') Is Not Null Drop Table #Tbl;
Go

Create Table #Tbl(ID Int Identity,
 L1 Varchar(Max),
 L2 Varchar(Max));
Go

Insert
Into  #Tbl
Select      '1,3,5,7,9','1,2,3,4,5' Union All
Select      'Avi,Batya,Gal,Dan','Gal,Dan,Batya,Avi' Union All
Select      '','abc,def,ghi' Union All
Select      ' xxx,yyy','xxx, yyy';
Go

Select      *
From  #Tbl;
Go
במקרה זה מדובר בדוגמה טכנית ללא סיפור כיסוי מקצועי: בשורה הראשונה יש התאמה חלקית בין הרשימות, בשניה התאמה מלאה אך בסדר שונה, בשלישית רשימה ריקה ורשימה עם תוכן, וברביעית רשימות דומות אבל עם רווחים מובילים שאמורים למנוע התאמות.
במקרה זה אנחנו לא רק נאלצים לכאורה לבצע Split, אלא חייבים; אבל כדי לא להסתבך ביצירת אובייקטים כמו פונקציה המבצעת Split וכו' אשתמש בפטנט שלמדתי מהפוסט הנ"ל לביצוע Split בעזרת XML, אבצע Join בין הרשימות, ואהפוך את רשימת החיתוך חזרה למחרוזת – שוב בעזרת XML.
במילים פשוטות: שימוש כפול ב-XML לביצוע Split של רשימה לסט, וביצוע שרשור של סט לרשימה:
With T As
(Select   *,
Cast('<Root><S>'+Replace(L1,',','</S><S>')+'</S></Root>' As XML) XML1,
Cast('<Root><S>'+Replace(L2,',','</S><S>')+'</S></Root>' As XML) XML2
From      #Tbl)
Select    *,
Stuff((Select ','+L1 As [text()]
From      (Select   S1.value('.','Varchar(MAX)') L1
From      XML1.nodes('//Root/S') Records1(S1)
Inner Join XML2.nodes('//Root/S') Records2(S2)
On S1.value('.','Varchar(MAX)')=S2.value('.','Varchar(MAX)')) T2
Order By T2.L1
For XML Path('')),1,1,'') L
From      T
Order By ID;

ההמרה ל-XML נעשית בתוך ה-CTE,
ה-Join מתבצע בין שתי הרשימות שהומרו לסטים,
הסט המקבל הופך חזרה למחרוזת בעזרת For XML Path,
והתוצאה מוצגת בעמודה הימנית L.

נתראה ברשומה הבאה!

גרי רשף.
לרשומה המקורית ולרשומות נוספות של גרי ניתן להיכנס לבלוג שלו: gerireshef.wordpress.com
ניתן גם לפנות במייל GeriReshef@gmail.com

The following two tabs change content below.

גרי רשף

Latest posts by גרי רשף (see all)

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

הפונקציה COALESCE

כולם מכירים את הפונקציה NVL, ומשתמשים בה לא מעט. לעומת זאת, הפונקציה COALESCE, השייכת לאותה משפחה של פונקציות, פחות מוכרת, למרות שהיא קיימת כבר מגרסה 9i ועולה ביכולותיה על NVL הישנה והטובה. מצד שני, אף [...]

טיפ: איך לזכור ב-SQLPLUS שאתה בסביבת היצור

כמה פעמים קרה לנו שעבדנו עם sqlplus  מול סביבת יצור ביחד עם עוד כמה חלונות פתוחים ועם קצת לחץ? בקיצור כרגיל… מכוון שהכול כל כך לחוץ, הרצנו איזה פקודה הרסנית, ומיד הצטערנו עליה מאוד: אוי! הרצתי [...]
עבודה

עבודה תחת סביבת FireWall

אורי לרנר בטיפ קצר ושימושי על עבודה בסביבת אורקל [...]
Indexes

Indexes in SQL Server 2

ערן שקד ממשיך בסדרת מאמרים על אינדקסים בSQL [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: