多类查询 #

一、多类查询概述 #

1.1 OrientDB关联方式 #

text

关联方式：
├── LINK - 直接链接引用
├── EMBEDDED - 嵌入文档
├── Edge - 图边关系
└── 查询关联 - 子查询关联

1.2 与关系数据库的区别 #

特性	OrientDB	关系数据库
关联方式	LINK/EDGE	外键
JOIN性能	链接直接访问	需要JOIN操作
嵌套数据	原生支持	需要多表查询

二、链接查询 #

2.1 直接访问链接属性 #

sql

SELECT name, employer.name AS companyName FROM Person

2.2 多层链接访问 #

sql

SELECT 
    name,
    employer.name AS companyName,
    employer.address.city AS companyCity,
    employer.ceo.name AS ceoName
FROM Person

2.3 链接集合访问 #

sql

SELECT name, friends.name AS friendNames FROM Person
SELECT name, SIZE(friends) AS friendCount FROM Person

2.4 链接条件过滤 #

sql

SELECT FROM Person WHERE employer.name = 'ABC Corp'
SELECT FROM Person WHERE employer.address.city = 'Beijing'

2.5 链接聚合 #

sql

SELECT 
    employer.name AS company,
    COUNT(*) AS employeeCount,
    AVG(salary) AS avgSalary
FROM Person
GROUP BY employer

三、反向链接查询 #

3.1 使用in()函数 #

sql

SELECT 
    name AS companyName,
    in('WORKS_AT').name AS employees
FROM Company

3.2 使用out()函数 #

sql

SELECT 
    name AS personName,
    out('WORKS_AT').name AS companies
FROM Person

3.3 双向链接 #

sql

SELECT 
    name,
    in('KNOWS').name AS followers,
    out('KNOWS').name AS following
FROM Person

3.4 链接计数 #

sql

SELECT 
    name,
    SIZE(in('KNOWS')) AS followerCount,
    SIZE(out('KNOWS')) AS followingCount
FROM Person

四、LET关联查询 #

4.1 使用LET定义关联 #

sql

SELECT 
    p.name,
    $company.name AS companyName
FROM Person p
LET $company = (SELECT FROM Company WHERE @rid = p.employer)

4.2 多表关联 #

sql

SELECT 
    p.name AS personName,
    $company.name AS companyName,
    $department.name AS departmentName
FROM Person p
LET $company = (SELECT FROM Company WHERE @rid = p.employer),
    $department = (SELECT FROM Department WHERE @rid = p.department)

4.3 条件关联 #

sql

SELECT 
    p.name,
    $orders AS orders
FROM Person p
LET $orders = (SELECT FROM Order WHERE customer = p.@rid AND status = 'completed')
WHERE $orders.size() > 0

五、子查询关联 #

5.1 WHERE子查询 #

sql

SELECT FROM Person WHERE employer IN (
    SELECT FROM Company WHERE industry = 'Technology'
)

5.2 FROM子查询 #

sql

SELECT p.name, o.totalAmount
FROM (
    SELECT FROM Person WHERE status = 'active'
) p
LET o = (SELECT FROM Order WHERE customer = p.@rid)
WHERE $o.size() > 0

5.3 投影子查询 #

sql

SELECT 
    name,
    (SELECT COUNT(*) FROM Order WHERE customer = @rid) AS orderCount,
    (SELECT SUM(totalAmount) FROM Order WHERE customer = @rid) AS totalSpent
FROM Person

5.4 关联子查询 #

sql

SELECT 
    p.name,
    (SELECT name FROM Company WHERE @rid = p.employer)[0] AS companyName
FROM Person p

六、UNWIND展开 #

6.1 展开集合 #

sql

SELECT name, hobby FROM Person 
UNWIND hobbies AS hobby

6.2 展开链接集合 #

sql

SELECT 
    p.name AS personName,
    f.name AS friendName
FROM Person p
UNWIND p.friends AS friendRid
LET f = (SELECT FROM Person WHERE @rid = $friendRid)

6.3 展开后聚合 #

sql

SELECT hobby, COUNT(*) AS personCount
FROM Person
UNWIND hobbies AS hobby
GROUP BY hobby
ORDER BY personCount DESC

七、图关系查询 #

7.1 查询边 #

sql

SELECT 
    out.name AS fromPerson,
    in.name AS toPerson,
    since
FROM KNOWS
WHERE since > 2020

7.2 查询顶点的边 #

sql

SELECT 
    name,
    outE('KNOWS').since AS knowSince,
    inE('KNOWS').since AS knownSince
FROM Person

7.3 遍历关联 #

sql

SELECT 
    name,
    out('WORKS_AT').name AS companies,
    out('WORKS_AT').out('LOCATED_IN').name AS locations
FROM Person

八、嵌入文档查询 #

8.1 访问嵌入属性 #

sql

SELECT name, address.city, address.street FROM Person
SELECT name, phoneNumbers[0].number AS primaryPhone FROM Person

8.2 嵌入文档条件 #

sql

SELECT FROM Person WHERE address.city = 'Beijing'
SELECT FROM Person WHERE phoneNumbers CONTAINSALL [{'type': 'mobile'}]

8.3 嵌入文档聚合 #

sql

SELECT 
    address.city AS city,
    COUNT(*) AS count
FROM Person
GROUP BY address.city

九、多类JOIN模拟 #

9.1 INNER JOIN效果 #

sql

SELECT 
    p.name AS personName,
    c.name AS companyName
FROM Person p
LET c = (SELECT FROM Company WHERE @rid = p.employer)
WHERE $c.size() > 0

9.2 LEFT JOIN效果 #

sql

SELECT 
    p.name AS personName,
    IF($c.size() > 0, $c[0].name, NULL) AS companyName
FROM Person p
LET $c = (SELECT FROM Company WHERE @rid = p.employer)

9.3 RIGHT JOIN效果 #

sql

SELECT 
    IF($p.size() > 0, $p[0].name, NULL) AS personName,
    c.name AS companyName
FROM Company c
LET $p = (SELECT FROM Person WHERE employer = c.@rid)

9.4 FULL JOIN效果 #

sql

SELECT name, 'Person' AS type FROM Person
UNION ALL
SELECT name, 'Company' AS type FROM Company
WHERE @rid NOT IN (SELECT employer FROM Person WHERE employer IS NOT NULL)

十、复杂关联示例 #

10.1 订单-客户-产品关联 #

sql

SELECT 
    o.orderNumber,
    c.name AS customerName,
    p.name AS productName,
    oi.quantity,
    oi.price
FROM Order o
LET $customer = (SELECT FROM Customer WHERE @rid = o.customer),
    $items = (SELECT FROM OrderItem WHERE orderId = o.@rid)
UNWIND $items AS oi
LET $product = (SELECT FROM Product WHERE @rid = oi.productId)
LET c = $customer,
    p = $product

10.2 组织架构查询 #

sql

SELECT 
    e.name AS employeeName,
    $manager.name AS managerName,
    $department.name AS departmentName
FROM Employee e
LET $manager = (SELECT FROM Employee WHERE @rid = e.manager),
    $department = (SELECT FROM Department WHERE @rid = e.department)

10.3 社交网络查询 #

sql

SELECT 
    p.name AS personName,
    $friends.name AS friendNames,
    $mutual.size() AS mutualFriendCount
FROM Person p
LET $friends = (SELECT FROM Person WHERE @rid IN p.out('KNOWS')),
    $mutual = (
        SELECT FROM Person 
        WHERE @rid IN $friends.out('KNOWS') 
          AND @rid IN p.out('KNOWS')
    )

十一、性能优化 #

11.1 使用索引 #

sql

CREATE INDEX Person.employer LINK
CREATE INDEX Order.customer LINK

11.2 限制关联深度 #

sql

SELECT name, employer.name FROM Person
SELECT name, employer.name, employer.address.city FROM Person

11.3 使用LET缓存 #

sql

LET $companies = SELECT FROM Company WHERE industry = 'Tech'
SELECT p.name, $c.name AS companyName
FROM Person p
LET $c = (SELECT FROM $companies WHERE @rid = p.employer)
WHERE $c.size() > 0

11.4 避免N+1问题 #

sql

SELECT 
    p.name,
    p.employer.name AS companyName
FROM Person p

十二、实际应用示例 #

12.1 订单详情查询 #

sql

SELECT 
    o.id AS orderId,
    o.orderNumber,
    o.status,
    c.name AS customerName,
    c.email AS customerEmail,
    SUM(oi.quantity * oi.price) AS totalAmount,
    COUNT(oi) AS itemCount
FROM Order o
LET $customer = (SELECT FROM Customer WHERE @rid = o.customer),
    $items = (SELECT FROM OrderItem WHERE orderId = o.@rid)
LET c = $customer,
    oi = $items
GROUP BY o

12.2 员工信息查询 #

sql

SELECT 
    e.name AS employeeName,
    e.email,
    d.name AS departmentName,
    m.name AS managerName,
    c.name AS companyName,
    AVG(s.amount) AS avgSalary
FROM Employee e
LET $department = (SELECT FROM Department WHERE @rid = e.department),
    $manager = (SELECT FROM Employee WHERE @rid = e.manager),
    $company = (SELECT FROM Company WHERE @rid = e.company),
    $salaries = (SELECT FROM Salary WHERE employeeId = e.@rid)
LET d = $department,
    m = $manager,
    c = $company,
    s = $salaries

12.3 产品销售分析 #

sql

SELECT 
    p.name AS productName,
    p.category,
    COUNT(DISTINCT o) AS orderCount,
    SUM(oi.quantity) AS totalQuantity,
    SUM(oi.quantity * oi.price) AS totalRevenue
FROM Product p
LET $orderItems = (SELECT FROM OrderItem WHERE productId = p.@rid)
UNWIND $orderItems AS oi
LET $order = (SELECT FROM Order WHERE @rid = oi.orderId)
LET o = $order
GROUP BY p
ORDER BY totalRevenue DESC

十三、总结 #

多类查询要点：

方式	说明	示例
链接访问	直接访问链接属性	employer.name
反向链接	in()/out()函数	in(‘WORKS_AT’)
LET关联	定义关联变量	LET $c = …
子查询	嵌套查询关联	(SELECT FROM …)
UNWIND	展开集合	UNWIND hobbies

下一步，让我们学习子查询与嵌套！