Maciej Los
Если вы хотите создать древовидную структуру следующим образом:
- country1
- region1
+ region2
- hotel1
- hotel2
- country2
+ region1
- hotel1
- hotel2
- region2
--and so on...
вы можете достичь этого с помощью приведенного ниже скрипта:
--variables (type of table)
DECLARE @hotels TABLE (CountryCode VARCHAR(10), RegionCode VARCHAR(10), HotelCode VARCHAR(10))
DECLARE @places TABLE(PlaceID INT, PlaceCode VARCHAR(10), ParentID INT)
--insert source data
INSERT INTO @hotels(CountryCode, RegionCode, HotelCode)
VALUES('IN', 'DEL', 'DL65'), ('IN', 'DEL', 'DL12'),
('IN', 'AGR', 'AG47'), ('SG', 'JAI', 'JA30'), ('SG', 'SIN', 'SI21')
--insert data into destination table
INSERT INTO @places(PlaceID, PlaceCode, ParentID)
SELECT T.PlaceID, T.PlaceCode, NULL AS ParentID
FROM
(
SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY Deep, PC) PlaceID, PC AS PlaceCode, Deep
FROM
(
SELECT DISTINCT CountryCode AS PC, COUNT(CountryCode) AS Cnt, 1 AS Deep
FROM @hotels
WHERE CountryCode IS NOT NULL
GROUP BY CountryCode
UNION ALL
SELECT DISTINCT RegionCode AS PC, COUNT(RegionCode) AS Cnt, 2 AS Deep
FROM @hotels
WHERE RegionCode IS NOT NULL
GROUP BY RegionCode
UNION ALL
SELECT DISTINCT HotelCode AS PC, COUNT(HotelCode) AS Cnt, 3 AS Deep
FROM @hotels
WHERE HotelCode IS NOT NULL
GROUP BY HotelCode
) DT
) T
--update ParentID Of RegionCode for CountryCode
UPDATE t1 SET ParentID = t2.ParentID
FROM @places t1 INNER JOIN
(
SELECT p.PlaceID As ParentID, h.RegionCode
FROM @places p INNER JOIN @hotels h ON p.PlaceCode = h.CountryCode
) t2 ON t1.PlaceCode = t2.RegionCode
--update ParentID Of HotelCode for RegionCode
UPDATE t1 SET ParentID = t2.ParentID
FROM @places t1 INNER JOIN
(
SELECT p.PlaceID As ParentID, h.HotelCode
FROM @places p INNER JOIN @hotels h ON p.PlaceCode = h.RegionCode
) t2 ON t1.PlaceCode = t2.HotelCode
--display hierarchical data
;WITH CTE AS
(
SELECT 1 AS LoopNo, PlaceID, CONVERT(VARCHAR(MAX), PlaceCode) AS FullPlaceCode
FROM @places
WHERE ParentID IS NULL
UNION ALL
SELECT LoopNo + 1 AS LoopNo, p.PlaceID, CONVERT(VARCHAR(MAX), CONCAT(c.FullPlaceCode, '->', p.PlaceCode)) AS FullPlaceCode
FROM CTE c INNER JOIN @places p ON c.PlaceID = p.ParentID
)
SELECT PlaceID AS LastPlaceID, FullPlaceCode AS Hierarchy
FROM
(
SELECT PlaceID, FullPlaceCode, DENSE_RANK() OVER(ORDER BY LoopNo DESC) AS RowNo
FROM CTE
--OPTION (MAXRECURSION 0)
--uncomment above line if you'll get an error message about recursive query exceeded the number of ...
) T
WHERE RowNo = 1
ORDER BY PlaceID
Результат (последнего
SELECT заявление):
LastPlaceID Hierarchy
7 IN->AGR->AG47
8 IN->DEL->DL12
9 IN->DEL->DL65
10 SG->JAI->JA30
11 SG->SIN->SI21