子查询与嵌套 #

子查询类型：
├── WHERE子查询 - 条件过滤
├── FROM子查询 - 数据源
├── SELECT子查询 - 投影计算
└── LET子查询 - 变量定义

子查询特点：
├── 可以嵌套多层
├── 支持关联子查询
├── 可返回单值或集合
└── 可在多个位置使用

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

SELECT FROM Person WHERE @rid NOT IN (
    SELECT customer FROM Order WHERE status = 'cancelled'
)

SELECT FROM Person p WHERE (
    SELECT COUNT(*) FROM Order WHERE customer = p.@rid
) > 0

SELECT FROM Employee WHERE salary > (
    SELECT AVG(salary) FROM Employee
)

SELECT FROM Person WHERE 
    employer IN (SELECT FROM Company WHERE industry = 'Tech')
    AND @rid NOT IN (SELECT person FROM Blacklist)

SELECT name, age FROM (
    SELECT FROM Person WHERE age > 30
)

SELECT name, city FROM (
    SELECT FROM Person WHERE status = 'active'
) WHERE age > 25

SELECT name, avgAge FROM (
    SELECT city, name, AVG(age) AS avgAge
    FROM (
        SELECT FROM Person WHERE status = 'active'
    )
    GROUP BY city
) WHERE avgAge > 30

SELECT AVG(deptCount) AS avgDeptSize FROM (
    SELECT department, COUNT(*) AS deptCount
    FROM Employee
    GROUP BY department
)

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

SELECT 
    name,
    (SELECT SUM(amount) FROM Order WHERE customer = @rid) AS totalSpent,
    (SELECT MAX(createdAt) FROM Order WHERE customer = @rid) AS lastOrderDate
FROM Person

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

SELECT 
    name,
    IF(
        (SELECT COUNT(*) FROM Order WHERE customer = @rid) > 10,
        'VIP',
        'Regular'
    ) AS customerLevel
FROM Person

LET $activeUsers = SELECT FROM User WHERE status = 'active'
SELECT name, email FROM $activeUsers

LET $users = SELECT FROM User WHERE status = 'active'
LET $orders = SELECT FROM Order WHERE createdAt > sysdate() - 30
SELECT 
    u.name,
    COUNT(o) AS orderCount
FROM $users u
LET $userOrders = (SELECT FROM $orders WHERE userId = u.@rid)
WHERE $userOrders.size() > 0

LET $topCustomers = (
    SELECT customer, SUM(amount) AS totalSpent
    FROM Order
    GROUP BY customer
    ORDER BY totalSpent DESC
    LIMIT 10
)
SELECT 
    c.name AS customerName,
    t.totalSpent
FROM $topCustomers t
LET c = (SELECT FROM Customer WHERE @rid = t.customer)

LET $managers = SELECT FROM Employee WHERE level = 'manager'
SELECT 
    m.name AS managerName,
    $team.size() AS teamSize
FROM $managers m
LET $team = (SELECT FROM Employee WHERE manager = m.@rid)
WHERE $team.size() > 0

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

SELECT 
    name,
    CASE 
        WHEN status = 'active' THEN 
            CASE 
                WHEN level = 'VIP' THEN 'Active VIP'
                ELSE 'Active Regular'
            END
        ELSE 'Inactive'
    END AS customerStatus
FROM Customer

SELECT 
    name,
    UPPER(TRIM(name)) AS cleanName,
    ROUND(AVG(salary), 2) AS avgSalary
FROM Employee

SELECT 
    name,
    IF(
        age > 18,
        IF(status = 'active', 'Adult Active', 'Adult Inactive'),
        'Minor'
    ) AS category
FROM Person

SELECT 
    p.name,
    (SELECT COUNT(*) FROM Order WHERE customer = p.@rid) AS orderCount
FROM Person p

SELECT 
    p.name,
    (SELECT name FROM Company WHERE @rid = p.employer)[0] AS company,
    (SELECT COUNT(*) FROM Order WHERE customer = p.@rid) AS orders
FROM Person p

SELECT 
    p.name,
    (SELECT SUM(amount) FROM Order 
     WHERE customer = p.@rid AND status = 'completed') AS totalSpent
FROM Person p
WHERE (SELECT COUNT(*) FROM Order WHERE customer = p.@rid) > 0

SELECT 
    name,
    out('MANAGES').name AS directReports
FROM Employee
WHERE level = 'CEO'

TRAVERSE out('KNOWS') FROM #12:0 MAXDEPTH 3

SELECT 
    @rid,
    name,
    $path AS path,
    $depth AS depth
FROM (
    TRAVERSE out('REPORTS_TO') FROM #12:0
)

LET $deptStats = (
    SELECT 
        department,
        COUNT(*) AS empCount,
        AVG(salary) AS avgSalary
    FROM Employee
    GROUP BY department
)
SELECT 
    d.name AS departmentName,
    s.empCount,
    s.avgSalary,
    CASE 
        WHEN s.avgSalary > (SELECT AVG(avgSalary) FROM $deptStats) THEN 'Above Average'
        ELSE 'Below Average'
    END AS salaryLevel
FROM $deptStats s
LET d = (SELECT FROM Department WHERE @rid = s.department)

LET $orders = SELECT FROM Order WHERE createdAt > sysdate() - 30
LET $customers = SELECT FROM Customer WHERE status = 'active'
SELECT 
    c.name AS customerName,
    COUNT(o) AS orderCount,
    SUM(o.totalAmount) AS totalSpent,
    AVG(o.totalAmount) AS avgOrderValue,
    MAX(o.createdAt) AS lastOrderDate
FROM $customers c
LET $customerOrders = (SELECT FROM $orders WHERE customer = c.@rid)
UNWIND $customerOrders AS o
GROUP BY c
HAVING COUNT(o) > 0
ORDER BY totalSpent DESC

SELECT 
    region,
    SUM(deptTotal) AS regionTotal,
    AVG(deptAvg) AS regionAvg
FROM (
    SELECT 
        department,
        region,
        SUM(salary) AS deptTotal,
        AVG(salary) AS deptAvg
    FROM Employee
    GROUP BY department, region
)
GROUP BY region

LET $companies = SELECT FROM Company
SELECT p.name, $c.name 
FROM Person p
LET $c = (SELECT FROM $companies WHERE @rid = p.employer)

LET $orderCount = (SELECT COUNT(*) FROM Order WHERE customer = @rid)
SELECT name, $orderCount AS orderCount
FROM Person
WHERE $orderCount > 0

SELECT FROM Person WHERE employer IN (
    SELECT FROM Company WHERE industry = 'Tech' LIMIT 100
)

CREATE INDEX Order.customer LINK
SELECT FROM Order WHERE customer = (SELECT FROM Person WHERE name = 'Tom')

LET $customerStats = (
    SELECT 
        customer,
        COUNT(*) AS orderCount,
        SUM(totalAmount) AS totalSpent,
        AVG(totalAmount) AS avgOrderValue,
        MAX(createdAt) AS lastOrderDate
    FROM Order
    WHERE status = 'completed'
    GROUP BY customer
)
SELECT 
    c.name AS customerName,
    c.email,
    s.orderCount,
    s.totalSpent,
    s.avgOrderValue,
    DATEDIFF('day', s.lastOrderDate, sysdate()) AS daysSinceLastOrder,
    CASE 
        WHEN s.totalSpent > 10000 THEN 'VIP'
        WHEN s.totalSpent > 5000 THEN 'Gold'
        WHEN s.totalSpent > 1000 THEN 'Silver'
        ELSE 'Bronze'
    END AS customerLevel
FROM $customerStats s
LET c = (SELECT FROM Customer WHERE @rid = s.customer)
ORDER BY s.totalSpent DESC

LET $employeeMetrics = (
    SELECT 
        e.@rid AS employeeId,
        e.name,
        e.department,
        COUNT(p) AS projectCount,
        AVG(p.score) AS avgScore,
        SUM(p.hours) AS totalHours
    FROM Employee e
    LET $projects = (SELECT FROM Project WHERE employeeId = e.@rid)
    UNWIND $projects AS p
    GROUP BY e
)
SELECT 
    m.name,
    m.department,
    m.projectCount,
    m.avgScore,
    m.totalHours,
    RANK() OVER (ORDER BY m.avgScore DESC) AS performanceRank
FROM $employeeMetrics m

LET $userPurchases = (
    SELECT productId, COUNT(*) AS purchaseCount
    FROM OrderItem
    WHERE orderId IN (SELECT @rid FROM Order WHERE customer = #12:0)
    GROUP BY productId
)
LET $similarUsers = (
    SELECT customer, COUNT(*) AS commonProducts
    FROM OrderItem
    WHERE productId IN (SELECT productId FROM $userPurchases)
    AND customer != #12:0
    GROUP BY customer
    ORDER BY commonProducts DESC
    LIMIT 10
)
SELECT 
    p.name AS productName,
    COUNT(*) AS recommendationScore
FROM OrderItem oi
WHERE oi.orderId IN (SELECT @rid FROM Order WHERE customer IN (SELECT customer FROM $similarUsers))
AND oi.productId NOT IN (SELECT productId FROM $userPurchases)
LET p = (SELECT FROM Product WHERE @rid = oi.productId)
GROUP BY oi.productId
ORDER BY recommendationScore DESC
LIMIT 10