SQLite - 使用不同的相關自引用表計算一個表中的引用行

media_subscriptions
+----+--------------------------+
| id | target_metadata_item_id  |
+----+--------------------------+
| 30 | 19039                    |
| 31 | NULL                     |
| 32 | NULL                     |
| 33 | NULL                     |
| 34 | NULL                     |
| 35 | NULL                     |
| 36 | NULL                     |
| 37 | 18976                    |
+----+--------------------------+

metadata_items
+-------+-----------------+-----------+
| id    | guid            | parent_id |
+-------+-----------------+-----------+
| 18976 | id://265        | NULL      |
| 18977 | id://265/1      | 18976     |
| 19039 | id://841        | NULL      |
| 19040 | id://841/1      | 19039     |
| 19041 | id://841/1/1    | 19040     |
| 19224 | id://841/1/2    | 19040     |
| 19966 | id://265/1/1    | 18977     |
| 19967 | id://290        | NULL      |
| 19968 | id://376        | NULL      |
+-------+-----------------+-----------+

我有上面的兩個數據表。該parent_id列定義 metadata_items 中的每一行如何與任何其他行相關，這也可以通過每一行的guid. parent_id可以由具有孩子的根 ID 嵌套，並且該孩子有孩子（如果有意義的話）。target_metadata_item_id來自的列與表media_subscriptions的列相關。我想根據搜尋從表中獲取具有關聯記錄的任何記錄。最終，如果有匹配的記錄，計數應該只為 1，如果沒有，則為 0，但我正在使用以防表中存在重複記錄，然後我只是在其他地方檢查它是否返回.id``metadata_items``COUNT(*)``metadata_items``media_subscriptions``guid``COUNT(*)``media_subscriptions``> 0

我正在嘗試完成的一些範例：

• guid='id://841'將返回 1，因為它的根父 id (19039) 被id30 in引用media_subscriptions。
• guid='id://841/1/1'將返回 1，因為它的根父 id (19039) 被id30 in引用media_subscriptions。
• guid='id://290'將返回 0，因為它的根父 id (19967) 沒有被media_subscriptions.

這就是我現在所擁有的，這顯然沒有按我的預期工作（放輕鬆，我是數據庫管理的菜鳥，並且剛剛開始學習）。

SELECT COUNT(*) FROM `metadata_items` AS `t1`
LEFT JOIN `metadata_items` AS `t2` ON t1.id = t2.parent_id
INNER JOIN `media_subscriptions` AS `subscriptions` ON t1.id = subscriptions.target_metadata_item_id
WHERE t1.guid='id://841'

這正確地返回 1，但是如果我切換最後一行，WHERE t1.guid='id://265/1/1'它會返回 0，我希望它返回 1。顯然我這樣做非常不正確，但我真的不確定如何去獲得我的位置需要。

測試

WITH RECURSIVE cte AS
(
SELECT id, guid, parent_id, id _id_, guid _guid_, parent_id _parent_id_
FROM metadata_items
UNION ALL
SELECT cte.id, cte.guid, cte.parent_id, src.id, src.guid, src.parent_id
FROM cte
JOIN metadata_items src ON cte._parent_id_ = src.id
)
SELECT cte.id, cte.guid, cte.parent_id, MAX(target_metadata_item_id) IS NOT NULL 
FROM cte 
LEFT JOIN media_subscriptions src ON cte._id_ = src.target_metadata_item_id
GROUP BY cte.id, cte.guid, cte.parent_id
ORDER BY cte.id;

小提琴

如果您只需要一條單獨記錄的數據，則添加適當的 WHERE。

引用自：https://dba.stackexchange.com/questions/265424