Function Row_Number() (ต่อ)
คราวที่แล้ว ว่า ไปถึงตัวอย่าง โจทย์ แล้วนะครับ คราวนี้ มาทำความเข้าใจถึง วิธีการกันครับ
ที่ผมว่า ตาราง สอง ตาราง มีความสัมพันธ์ กัน ตามโจทย์ คือ CustomerID และ ครั้งที่ (จะหมายถึง Record ที่) ใน Database นั่นเอง ถ้ากำหนดให้ การ Key ข้อมูล ต้องเรียงตามลำดับ จาก ก่อนไปหลัง
หรือเรียงลำดับตามวันที่ แบบ ascending นั้นเอง แต่ เนื่องจาก Field ลำดับที่ไม่มี เราสามารถใช้ Function
Row_number() (* เฉพาะ SQL 2005 ขึ้นไป) ในการสร้าง Logical Field นี้ โดย ผมจะสร้าง view ขึ้นอีก
2 views สำหรับเพิ่ม Field [No] ขึ้น ให้กับทั้งสอง ตาราง
ดังนี้
-- สร้าง view สำหรับ CustomerRisk
CREATE VIEW [app].[vCustomerRisk]
AS
SELECT TOP (100) PERCENT
C.AccountNo,
row_number() OVER(partition by C.AccountNo order by C.RiskDate ) AS [No],
C.RiskDate , C.RiskDate, C.RiskLevel
FROM CustomerRisk
ORDER BY C.AccountNo, C.RiskDate
GO
ผลลัพธ์ ของ view คือ
-- สร้าง view สำหรับ CustomerProfile
CREATE VIEW [app].[vCustomerProfile]
AS
SELECT TOP (100) PERCENT
C.AccountNo,
row_number() OVER(partition by C.AccountNo order by C.ProfileDate) AS [No],
C.RiskDate , C.ProfileDate, C.ProfileDetail
FROM CustomerProfile
ORDER BY C.AccountNo, C.RiskDate
GO
ผลลัพธ์ของ view คือ
หลังจาก สร้าง 2 view แล้ว เราจะสามารถ สร้าง Report ตามโจทย์ที่กำหนด ได้ดังนี้
1. สร้าง combination Table (logical) ที่จะ join ทั้ง 2 view เข้ามา concept คือสร้าง Query ที่มี
2 Field คือ CustomerID และ No โดยมีข้อแม้ ว่า Field No หรือ ครั้งที่ ต้องมีทุกครั้ง วิธีการคือ
select ทั้ง 2 field จาก 2 view ที่สร้างขึ้น แล้วมา Union กัน
SELECT R.AccountNo, R.No FROM [app].[vCustomerRisk]
UNION
SELECT P.AccountNo, P.No FROM [app].[vCustomerProfile]
ผลลัพธ์ จากการ Union จะได้ ผลลัพธ์ ดังนี้
จากนั้น นำ View ที่สร้างขึ้นมา Join เข้าไปเพื่อให้ได้ Output ดังนี้
SELECT A.AccountNo, A.No, R.RiskDate,R.RiskLevel, P.ProfileDate, P.ProfileDetail
FROM
(SELECT R.AccountNo, R.No FROM [app].[vCustomerRisk]
UNION
SELECT P.AccountNo, P.No FROM [app].[vCustomerProfile]) AS A LEFT OUTER JOIN
[app].[vCustomerRisk] AS R ON R.AccountNo=A.AccountNo AND R.No=A.No
LEFT OUTER JOIN
[app].[vCustomerProfile] AS P ON P.AccountNo=A.AccountNo AND P.No=A.No
โดยผลลัพธ์ จากการเีรียก คำสั่งนี้คือ
จะเห็นว่า เราสามารถประยุกต์ ใช้งาน Function row_number() ของ SQL 2005 ได้
โดยรายละเอียดการใช้ Function row_number() สามารถ ดู Help ของ
Function นี่ได้ ตาม Link
http://msdn.microsoft.com/en-us/library/ms186734(classic).aspx
หมายเหตุ ใน Edition Compact Edition เข้าใจว่าไม่สามารถใช้ได้
ที่ผมว่า ตาราง สอง ตาราง มีความสัมพันธ์ กัน ตามโจทย์ คือ CustomerID และ ครั้งที่ (จะหมายถึง Record ที่) ใน Database นั่นเอง ถ้ากำหนดให้ การ Key ข้อมูล ต้องเรียงตามลำดับ จาก ก่อนไปหลัง
หรือเรียงลำดับตามวันที่ แบบ ascending นั้นเอง แต่ เนื่องจาก Field ลำดับที่ไม่มี เราสามารถใช้ Function
Row_number() (* เฉพาะ SQL 2005 ขึ้นไป) ในการสร้าง Logical Field นี้ โดย ผมจะสร้าง view ขึ้นอีก
2 views สำหรับเพิ่ม Field [No] ขึ้น ให้กับทั้งสอง ตาราง
ดังนี้
-- สร้าง view สำหรับ CustomerRisk
CREATE VIEW [app].[vCustomerRisk]
AS
SELECT TOP (100) PERCENT
C.AccountNo,
row_number() OVER(partition by C.AccountNo order by C.RiskDate ) AS [No],
C.RiskDate , C.RiskDate, C.RiskLevel
FROM CustomerRisk
ORDER BY C.AccountNo, C.RiskDate
GO
ผลลัพธ์ ของ view คือ
| CustomerID | No | RiskDate | RiskLevel |
| 1 | 1 | 15/1/2009 | Level1 |
| 1 | 2 | 1/2/2009 | Level1 |
| 1 | 3 | 15/2/2009 | Level2 |
| 1 | 4 | 28/2/2009 | Level3 |
| 2 | 1 | 15/1/2009 | Level1 |
| 2 | 2 | 1/2/2009 | Level1 |
-- สร้าง view สำหรับ CustomerProfile
CREATE VIEW [app].[vCustomerProfile]
AS
SELECT TOP (100) PERCENT
C.AccountNo,
row_number() OVER(partition by C.AccountNo order by C.ProfileDate) AS [No],
C.RiskDate , C.ProfileDate, C.ProfileDetail
FROM CustomerProfile
ORDER BY C.AccountNo, C.RiskDate
GO
ผลลัพธ์ของ view คือ
| CustomerID | No | ProfileDate | ProfileDetail |
| 1 | 1 | 15/1/2009 | Detail1 |
| 1 | 2 | 8/2/2009 | Details2 |
| 2 | 1 | 5/3/2009 | Detail1 |
หลังจาก สร้าง 2 view แล้ว เราจะสามารถ สร้าง Report ตามโจทย์ที่กำหนด ได้ดังนี้
1. สร้าง combination Table (logical) ที่จะ join ทั้ง 2 view เข้ามา concept คือสร้าง Query ที่มี
2 Field คือ CustomerID และ No โดยมีข้อแม้ ว่า Field No หรือ ครั้งที่ ต้องมีทุกครั้ง วิธีการคือ
select ทั้ง 2 field จาก 2 view ที่สร้างขึ้น แล้วมา Union กัน
SELECT R.AccountNo, R.No FROM [app].[vCustomerRisk]
UNION
SELECT P.AccountNo, P.No FROM [app].[vCustomerProfile]
ผลลัพธ์ จากการ Union จะได้ ผลลัพธ์ ดังนี้
| CustomerID | No |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 2 | 2 |
จากนั้น นำ View ที่สร้างขึ้นมา Join เข้าไปเพื่อให้ได้ Output ดังนี้
SELECT A.AccountNo, A.No, R.RiskDate,R.RiskLevel, P.ProfileDate, P.ProfileDetail
FROM
(SELECT R.AccountNo, R.No FROM [app].[vCustomerRisk]
UNION
SELECT P.AccountNo, P.No FROM [app].[vCustomerProfile]) AS A LEFT OUTER JOIN
[app].[vCustomerRisk] AS R ON R.AccountNo=A.AccountNo AND R.No=A.No
LEFT OUTER JOIN
[app].[vCustomerProfile] AS P ON P.AccountNo=A.AccountNo AND P.No=A.No
โดยผลลัพธ์ จากการเีรียก คำสั่งนี้คือ
| CustomerID | No | RiskDate | RiskLevel | ProfileDate | ProfileDetail |
| 1 | 1 | 15/1/2009 | Level1 | 15/1/2009 | Detail1 |
| 1 | 2 | 1/2/2009 | Level1 | 8/2/2009 | Details2 |
| 1 | 3 | 15/2/2009 | Level2 | ||
| 1 | 4 | 28/2/2009 | Level3 | ||
| 2 | 1 | 15/1/2009 | Level1 | 5/3/2009 | Detail1 |
| 2 | 2 | 1/2/2009 | Level1 |
จะเห็นว่า เราสามารถประยุกต์ ใช้งาน Function row_number() ของ SQL 2005 ได้
โดยรายละเอียดการใช้ Function row_number() สามารถ ดู Help ของ
Function นี่ได้ ตาม Link
http://msdn.microsoft.com/en-us/library/ms186734(classic).aspx
หมายเหตุ ใน Edition Compact Edition เข้าใจว่าไม่สามารถใช้ได้
ความคิดเห็น
แสดงความคิดเห็น