高级查询 #

LET <variable> = <expression>
SELECT ... FROM ...

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 $company = SELECT FROM Company WHERE name = 'ABC Corp'
SELECT name FROM Person WHERE employer = $company[0].@rid
SELECT name FROM Department WHERE company = $company[0].@rid

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

SELECT name, age FROM (
    SELECT FROM Person WHERE age > 30
) WHERE city = 'Beijing'

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

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

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

SELECT name, 'Person' AS type FROM Person
UNION
SELECT name, 'Company' AS type FROM Company

SELECT name FROM Person WHERE city = 'Beijing'
UNION ALL
SELECT name FROM Person WHERE city = 'Shanghai'

SELECT name, email FROM Customer
UNION
SELECT name, email FROM Supplier
UNION
SELECT name, email FROM Employee

SELECT 
    name,
    age,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 30 THEN 'Young Adult'
        WHEN age BETWEEN 31 AND 50 THEN 'Adult'
        WHEN age BETWEEN 51 AND 65 THEN 'Middle Age'
        ELSE 'Senior'
    END AS ageGroup
FROM Person

SELECT 
    name,
    CASE status
        WHEN 'active' THEN 'Active User'
        WHEN 'inactive' THEN 'Inactive User'
        WHEN 'pending' THEN 'Pending Approval'
        ELSE 'Unknown Status'
    END AS statusLabel
FROM User

SELECT 
    name,
    CASE 
        WHEN status = 'active' THEN 
            CASE 
                WHEN age < 30 THEN 'Young Active'
                ELSE 'Senior Active'
            END
        ELSE 'Inactive'
    END AS category
FROM Person

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM Employee

SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS deptRank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS overallRank
FROM Employee

SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS deptAvgSalary,
    SUM(salary) OVER (ORDER BY salary) AS runningTotal
FROM Employee

SELECT 
    orderDate,
    amount,
    LAG(amount, 1) OVER (ORDER BY orderDate) AS prevAmount,
    LEAD(amount, 1) OVER (ORDER BY orderDate) AS nextAmount
FROM Sales

SELECT FROM Person WHERE 
    (age > 18 AND age < 60) 
    AND (city = 'Beijing' OR city = 'Shanghai')
    AND status = 'active'

SELECT FROM Person WHERE 
    (status = 'active' AND (
        (age > 30 AND experience > 5) OR
        (age > 25 AND education = 'PhD')
    ))

SELECT FROM Person WHERE 
    NOT (status = 'deleted' OR status = 'inactive')

SELECT FROM Person WHERE 
    @rid IN (
        SELECT @rid FROM Person WHERE 
            employer IN (SELECT FROM Company WHERE industry = 'Tech')
    )

SELECT name, preferences['theme'] AS theme FROM Person
SELECT name, address.city, address.street FROM Person

SELECT FROM Person WHERE preferences['theme'] = 'dark'
SELECT FROM Person WHERE address.city = 'Beijing'

SELECT name, JSON_FROM_RECORD(@this) AS jsonRecord FROM Person
SELECT name, JSON_FROM_SET(tags) AS tagsJson FROM Person

SELECT FROM Person WHERE name MATCHES '^[A-Z]'
SELECT FROM Person WHERE email MATCHES '^[a-zA-Z0-9._%+-]+@'

SELECT FROM Person WHERE phone MATCHES '^\+?[0-9]{10,15}$'
SELECT FROM Product WHERE code MATCHES '^[A-Z]{2}[0-9]{4}$'

SELECT FROM Person WHERE 
    name MATCHES '^[A-Z][a-z]+$' AND
    email MATCHES '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'

SELECT 
    name,
    hobbies,
    SIZE(hobbies) AS hobbyCount,
    FIRST(hobbies) AS firstHobby,
    LAST(hobbies) AS lastHobby
FROM Person

SELECT 
    name,
    ASSET(hobbies) AS uniqueHobbies,
    ASLIST(tags) AS tagList
FROM Person

SELECT 
    name,
    UNIONALL(hobbies, skills) AS allInterests,
    INTERSECT(hobbies, requiredSkills) AS matchingSkills
FROM Person

SELECT name, hobby FROM Person 
UNWIND hobbies AS hobby

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

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)

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

CREATE INDEX Person.email UNIQUE
SELECT FROM Person WHERE email = 'tom@example.com'

SELECT FROM Person WHERE status = 'active' LIMIT 100

SELECT @rid, name FROM Person

SELECT name, email, status FROM Person

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

LET $sales = SELECT FROM Order WHERE createdAt BETWEEN DATE('2024-01-01') AND DATE('2024-12-31')
SELECT 
    customer.name AS customerName,
    COUNT(*) AS orderCount,
    SUM(totalAmount) AS totalSpent,
    AVG(totalAmount) AS avgOrderValue
FROM $sales
GROUP BY customer
ORDER BY totalSpent DESC
LIMIT 10

LET $managers = SELECT FROM Employee WHERE level = 'manager'
SELECT 
    m.name AS managerName,
    in('REPORTS_TO').name AS teamMembers
FROM $managers m

SELECT 
    DATE(createdAt) AS date,
    COUNT(*) AS orderCount,
    SUM(amount) AS dailyTotal
FROM Order
WHERE createdAt >= sysdate() - 30
GROUP BY DATE(createdAt)
ORDER BY date ASC

SELECT 
    department,
    status,
    COUNT(*) AS count,
    AVG(salary) AS avgSalary,
    MIN(salary) AS minSalary,
    MAX(salary) AS maxSalary
FROM Employee
GROUP BY department, status
ORDER BY department, status