MySQL Pivot: סיבוב שורות לעמודות

Mysql Pivot Rotating Rows Columns



טבלת מסדי נתונים יכולה לאחסן סוגי נתונים שונים ולפעמים עלינו להפוך נתונים ברמת השורה לנתונים ברמת העמודה. ניתן לפתור בעיה זו באמצעות הפונקציה PIVOT (). פונקציה זו משמשת לסיבוב שורות של טבלה לערכי עמודה. אך פונקציה זו נתמכת על ידי מעט מאוד שרתי מסדי נתונים, כגון אורקל או שרת SQL. אם אתה רוצה לבצע את אותה משימה בטבלת מסד הנתונים של MySQL, עליך לכתוב את שאילתת SELECT באמצעות משפט CASE כדי לסובב את השורות לעמודות. המאמר מציג את הדרך לביצוע המשימה של פונקציית PIVOT () בתוך טבלאות מסד נתונים MySQL קשורות.

תְנַאִי מוּקדָם:

עליך ליצור מסד נתונים וכמה טבלאות קשורות שבהן שורות של טבלה אחת יומרו לעמודות כמו פונקציית PIVOT (). הפעל את משפטי ה- SQL הבאים ליצירת מסד נתונים בשם ' unidb 'וליצור שלוש טבלאות בשם' סטודנטים ',' קורסים 'ו-' תוֹצָאָה '. סטודנטים ו תוֹצָאָה הטבלאות יהיו קשורות במערכת יחסים אחת לרבים ו קורסים ו תוצאות הטבלאות יהיו קשורות במערכת יחסים של אחד לרבים כאן. צור הצהרה של תוֹצָאָה הטבלה מכילה שני אילוצי מפתח זרים עבור השדות, std_id , ו מזהה קורס .







צור unidb;
USE unidb;

צור תלמידים בלוח(
תְעוּדַת זֶהוּתמפתח ראשוני INT,
שם varchar(חמישים)לא ריק,
מחלקת VARCHAR(חֲמֵשׁ עֶשׂרֵה)לא ריק);

צור קורסים בטבלה(
קורס_זהות VARCHAR(עשרים)מפתח ראשי,
שם varchar(חמישים)לא ריק,
אשראי SMALLINT NOT NULL);

צור תוצאה בלוח(
std_id INT NOT NULL,
קורס_זהות VARCHAR(עשרים)לא ריק,
סוג_סוג VARCHAR(עשרים)לא ריק,
סימנים SMALLINT NOT NULL,
מפתח זר(std_id)סטודנטים הפניות(תְעוּדַת זֶהוּת),
מפתח זר(מזהה קורס)קורסי הפניות(מזהה קורס),
מפתח ראשי(std_id, course_id, mark_type));

הכנס כמה רשומות סטודנטים, קורסים ותוצאה שולחנות. יש להכניס את הערכים לטבלאות על סמך המגבלות שנקבעו בעת יצירת הטבלה.



הכנס לערכים של סטודנטים
( '1937463','הארפר לי','CSE'),
( '1937464','גרסיה מרקס','CSE'),
( '1937465','פורסטר, א.מ.','CSE'),
( '1937466','ראלף אליסון','CSE');

הוספת כניסות לערכים
( 'CSE-401','תכנות מונחה עצמים',3),
( 'CSE-403','מבנה נתונים',2),
( 'CSE-407','תכנות יוניקס',2);

הכנס לערכי התוצאה
( '1937463','CSE-401','בחינה פנימית',חֲמֵשׁ עֶשׂרֵה),
( '1937463','CSE-401','מבחן אמצע',עשרים),
( '1937463','CSE-401','בחינה סופית',35),
( '1937464','CSE-403','בחינה פנימית',17),
( '1937464','CSE-403','מבחן אמצע',חֲמֵשׁ עֶשׂרֵה),
( '1937464','CSE-403','בחינה סופית',30),
( '1937465','CSE-401','בחינה פנימית',18),
( '1937465','CSE-401','מבחן אמצע',2. 3),
( '1937465','CSE-401','בחינה סופית',38),
( '1937466','CSE-407','בחינה פנימית',עשרים),
( '1937466','CSE-407','מבחן אמצע',22),
( '1937466','CSE-407','בחינה סופית',40);

פה, תוֹצָאָה הטבלה מכילה מספר ערכים זהים עבור std_id , סוג_סמן ו מזהה קורס עמודות בכל שורה. כיצד להמיר שורות אלה לעמודות בטבלה זו להצגת הנתונים בפורמט מסודר יותר מוצג בחלק הבא של מדריך זה.



סובב שורות לעמודות באמצעות משפט CASE:

הפעל את משפט SELECT הפשוט הבא כדי להציג את כל הרשומות של תוֹצָאָה שולחן.





בחר*מתוך התוצאה;

התפוקה מציגה את ציוני ארבעת התלמידים לשלושה סוגי בחינות של שלושה קורסים. אז הערכים של std_id , מזהה קורס ו סוג_סמן חוזרים על עצמם מספר פעמים עבור התלמידים, הקורסים וסוגי הבחינות השונים.



הפלט יהיה קריא יותר אם ניתן לכתוב את שאילתת SELECT בצורה יעילה יותר באמצעות משפט CASE. SELECT הבא עם המשפט CASE יהפוך את הערכים החוזרים של השורות לשמות העמודות ויציג את תוכן הטבלאות בפורמט מובן יותר עבור המשתמש.

בחר result.std_id, result.course_id,
מקס(מקרה כאשר result.mark_type ='בחינה פנימית'לאחר מכן result.marks סיום) 'בחינה פנימית',
מקס(מקרה כאשר result.mark_type ='מבחן אמצע'לאחר מכן result.marks סיום) 'מבחן אמצע',
מקס(מקרה כאשר result.mark_type ='בחינה סופית'לאחר מכן result.marks סיום) 'בחינה סופית'
מתוך התוצאה
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;

הפלט הבא יופיע לאחר הפעלת המשפט לעיל שהוא קריא יותר מהפלט הקודם.

סובב שורות לעמודות באמצעות CASE ו- SUM ():

אם אתה רוצה לספור את המספר הכולל של כל קורס של כל תלמיד מהטבלה, עליך להשתמש בפונקציה המצרפת סְכוּם() קבוצה לפי std_id ו מזהה קורס עם הצהרת CASE. השאילתה הבאה נוצרת על ידי שינוי השאילתה הקודמת באמצעות הפונקציה SUM () ופסקת GROUP BY.

בחר result.std_id, result.course_id,
מקס(מקרה כאשר result.mark_type ='בחינה פנימית'לאחר מכן result.marks סיום) 'בחינה פנימית',
מקס(מקרה כאשר result.mark_type ='מבחן אמצע'לאחר מכן result.marks סיום) 'מבחן אמצע',
מקס(מקרה כאשר result.mark_type ='בחינה סופית'לאחר מכן result.marks סיום) 'בחינה סופית',
סְכוּם(result.marks) כפי שסה'כ
מתוך התוצאה
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;

הפלט מציג עמודה חדשה בשם סה'כ זה מציג את סכום הציונים של כל סוגי הבחינות של כל קורס שהשיג כל סטודנט מסוים.

סובב שורות לעמודות במספר טבלאות:

שתי השאילתות הקודמות מוחלות על תוֹצָאָה שולחן. טבלה זו קשורה לשתי הטבלאות האחרות. אלו הם סטודנטים ו קורסים . אם אתה רוצה להציג את שם התלמיד במקום מזהה התלמיד ושם הקורס במקום מזהה הקורס, עליך לכתוב את שאילתת SELECT באמצעות שלוש טבלאות קשורות, סטודנטים , קורסים ו תוֹצָאָה . שאילתת SELECT הבאה נוצרת על ידי הוספת שלושה שמות טבלאות לאחר סעיף FORM והגדרת תנאים מתאימים בסעיף WHERE לאחזור הנתונים משלוש הטבלאות וליצור פלט מתאים יותר משאילתות SELECT הקודמות.

בחר שם סטודנטיםכפי ש 'שם תלמיד', קורסים.שםכפי ש 'שם קורס',
מקס(מקרה כאשר result.mark_type ='בחינה פנימית'לאחר מכן result.marks סיום) 'CT',
מקס(מקרה כאשר result.mark_type ='מבחן אמצע'לאחר מכן result.marks סיום) 'בֵּינוֹנִי',
מקס(מקרה כאשר result.mark_type ='בחינה סופית'לאחר מכן result.marks סיום) 'סופי',
סְכוּם(result.marks) כפי שסה'כ
מתלמידים, קורסים, תוצאה
WHERE result.std_id = students.id ו- result.course_id = courses.course_id
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;

הפלט הבא ייווצר לאחר ביצוע השאילתה לעיל.

סיכום:

כיצד תוכל ליישם את הפונקציונליות של הפונקציה Pivot () ללא תמיכה בפונקציה Pivot () ב- MySQL מוצג במאמר זה על ידי שימוש בנתוני דמה. אני מקווה שהקוראים יוכלו להפוך כל נתונים ברמת השורה לנתונים ברמת העמודות באמצעות שאילתת SELECT לאחר קריאת מאמר זה.