我在下面定義了兩個表（請注意，Regions表是遞回的，并且遞回可能有多個級別）。
地區
| ID | 父ID | 姓名 |
|---|---|---|
| 1 | 空值 | 歐洲聯盟 |
| 2 | 1 | 德國 |
| 3 | 1 | 法國 |
城市
| ID | 姓名 | 區域標識 |
|---|---|---|
| 1 | 柏林 | 2 |
| 2 | 漢堡 | 2 |
| 3 | 巴黎 | 3 |
| 4 | 好的 | 3 |
我想看看特定地區有多少個城市。所需的輸出如下：
| 地區 | 城市計數 |
|---|---|
| 歐洲聯盟 | 4 |
| 德國 | 2 |
| 法國 | 2 |
此查詢為我提供了每個子區域中的城市數量，但如何加入遞回表以獲取父（在本例中EU）區域？
select R.Name, count(C.Id)
from Regions R
join Cities C on C.RegionId = R.Id
group by R.Name
having count(C.Id) > 1
我試圖簡化我面臨的現實世界的問題，這顯然是簡化。
uj5u.com熱心網友回復：
這似乎是你所追求的。您可以使用 rCTE 通過層次結構移動到根，但每次迭代都會保留某些資訊；在這種情況下，原始節點的名稱。然后你仍然可以JOIN在RegionID：
WITH rCTE AS(
SELECT R.ID,
R.ParentID,
R.[Name]
FROM dbo.Regions R
UNION ALL
SELECT R.ID,
R.ParentID,
C.[Name]
FROM dbo.Regions R
JOIN rCTE C ON R.ParentID = C.ID)
SELECT r.[Name],
COUNT(*) AS CityCount
FROM rCTE r
JOIN dbo.Cities C ON r.ID = C.RegionID
GROUP BY r.[Name];
資料庫<>小提琴
uj5u.com熱心網友回復：
您可以使用遞回 CTE 來展平您的regions樹：
with flatregions as
(
select t.ID, t.ParentID, t.Name, 1 as lvl
from regions t
union all
select t.ID, tt.ParentID, tt.Name, t.lvl 1
from flatregions t
inner join regions tt on tt.ID = t.ParentID
)
select * FROM flatregions;
| ID | 家長編號 | 姓名 | 等級 |
|---|---|---|---|
| 1 | 歐洲聯盟 | 1 | |
| 2 | 1 | 德國 | 1 |
| 3 | 1 | 法國 | 1 |
| 3 | 歐洲聯盟 | 2 | |
| 2 | 歐洲聯盟 | 2 |
然后在JOIN您的cities表中使用該 CTE ：
with flatregions as
(
select t.ID, t.ParentID, t.Name, 1 as lvl
from regions t
union all
select t.ID, tt.ParentID, tt.Name, t.lvl 1
from flatregions t
inner join regions tt on tt.ID = t.ParentID
)
select R.Name, count(C.Id) as CityCount
from flatregions R
join Cities C on C.RegionId = R.Id
group by R.Name
having count(C.Id) > 1
| 地區 | 城市計數 |
|---|---|
| 歐洲聯盟 | 4 |
| 德國 | 2 |
| 法國 | 2 |
看到這個 db<>fiddle。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/365086.html
標籤：sql sql-server 递归查询