sql - 从 SQL 查询中的其他表值中检索子对象记录
问题描述
在 Service_line_TABLE 中,line_on_structure 列是“TRUE”,然后检索所有子记录。如果不只是重试服务线的对象记录。
预期结果表在附图中给出。
这是我尝试过的查询,但不起作用。
SELECT S.contract_id, S.contract_name, L.line_id, O.object_id
FROM Service_Line L, Service_Contract S, Object O
WHERE ( L.contract_id = S.contract_id AND O.object_id = L.object_id ) AND
( ( L.line_on_structure = 'True' AND O.object_id IN ( SELECT object_id
FROM Object O
START WITH O.object_id = L.object_id
CONNECT BY PRIOR O.object_id = O.belongs_to_object AND
O.contract = O.belongs_to_site ) ) OR
( L.line_on_structure = 'False' AND O.object_id IN (
SELECT object_id
FROM Object O
WHERE O.object_id = L.object_id) ) )
解决方案
Recursive CTE
您可以使用而不是查询来实现此目的。
WITH mycte(object_id,parent_object_id,line_name,line_id,contract_id,name)
AS (
SELECT o.object_id,o.parent_object_id,s.line_name,s.line_id,s.contract_id,c.name
FROM ServiceLine s join object o on s.object_id = o.object_id
join contract c on c.contract_id=s.contract_id
WHERE s.Line_on_structure = 'TRUE' and o.parent_object_id IS NULL
UNION ALL
SELECT o.object_id,o.parent_object_id,m.line_name,m.line_id,m.contract_id,m.name
FROM mycte m
JOIN object o
ON m.object_id = o.parent_object_id )
SELECT CONTRACT_ID,NAME,LINE_ID,LINE_NAME,OBJECT_ID
FROM mycte m
UNION ALL
SELECT s.contract_id,c.name,s.line_id,s.line_name,o.object_id
FROM ServiceLine s join object o on s.object_id = o.object_id
join contract c on c.contract_id=s.contract_id
WHERE s.Line_on_structure = 'FALSE' and o.parent_object_id IS NULL
输出:
+------------+--------------+---------+-----------+-----------+
| CONTRACT_ID| NAME | LINE_ID | LINE_NAME | OBJECT_ID |
+------------+--------------+---------+-----------+-----------+
| 100001 | ABC_CONTRACT | 0001 | XYZ | OBJ1 |
| 100001 | ABC_CONTRACT | 0001 | XYZ | OBJ2 |
| 100001 | ABC_CONTRACT | 0001 | XYZ | OBJ3 |
| 100001 | ABC_CONTRACT | 0001 | XYZ | OBJ4 |
| 100001 | ABC_CONTRACT | 0001 | XYZ | OBJ5 |
| 100001 | ABC_CONTRACT | 0002 | DFS | AAA1 |
+------------+--------------+---------+-----------+-----------+
推荐阅读
- android - Android 6.0.1 谷歌地图异常 IllegalAccessError
- swift - How to suspend a work item on the main queue
- c# - rdlc 中的自定义字体被特殊字符弄乱了
- java - spring-boot Maven: How to create executable jar with main class?
- javascript - Graphql react-apollo IntrospectionFragmentMatcher
- r - R合并功能重复“按”错误
- regex - Distinguishing and substituting decimals in Perl
- azure - 如何使用 Azure CLI 创建 VM 指定 Azure Linux OS 磁盘类型
- orm - AdonisJS 在使用 ORM 时仅使用 api 捕获数据库插入错误
- .htaccess - 使用 htaccess 将 URL php 重写为 html?