Couchbase N1QL高级查询 #

一、子查询 #

1.1 SELECT中的子查询 #

sql 
SELECT 
    name,
    email,
    (SELECT COUNT(*) 
     FROM `my-bucket`.`_default`.`_default` AS o 
     WHERE o.type = 'order' AND o.user_id = META(u).id) AS order_count
FROM `my-bucket`.`_default`.`_default` AS u
WHERE u.type = 'user';

1.2 WHERE中的子查询 #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' 
  AND city IN (
    SELECT city 
    FROM `my-bucket`.`_default`.`_default`
    WHERE type = 'city' AND population > 1000000
  );

1.3 FROM中的子查询 #

sql 
SELECT avg_age, city
FROM (
    SELECT city, AVG(age) AS avg_age
    FROM `my-bucket`.`_default`.`_default`
    WHERE type = 'user'
    GROUP BY city
) AS city_stats
WHERE avg_age > 30;

1.4 相关子查询 #

sql 
SELECT 
    name,
    email,
    (SELECT VALUE o.total
     FROM `my-bucket`.`_default`.`_default` AS o
     WHERE o.type = 'order' 
       AND o.user_id = META(u).id
     ORDER BY o.created_at DESC
     LIMIT 1)[0] AS last_order_total
FROM `my-bucket`.`_default`.`_default` AS u
WHERE u.type = 'user';

二、CTE（公共表表达式） #

2.1 基本CTE #

sql 
WITH active_users AS (
    SELECT name, email, city
    FROM `my-bucket`.`_default`.`_default`
    WHERE type = 'user' AND status = 'active'
)
SELECT * FROM active_users
WHERE city = '北京';

2.2 多个CTE #

sql 
WITH 
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM `my-bucket`.`_default`.`_default`
    WHERE type = 'order'
    GROUP BY user_id
),
vip_users AS (
    SELECT META().id, name, email
    FROM `my-bucket`.`_default`.`_default`
    WHERE type = 'user' AND level = 'vip'
)
SELECT 
    v.name,
    v.email,
    o.order_count,
    o.total_amount
FROM vip_users v
LEFT JOIN user_orders o ON META(v).id = o.user_id
ORDER BY o.total_amount DESC;

2.3 递归CTE #

sql 
WITH RECURSIVE category_tree AS (
    SELECT 
        META().id,
        name,
        parent_id,
        0 AS level
    FROM `my-bucket`.`_default`.`_default`
    WHERE type = 'category' AND parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        META(c).id,
        c.name,
        c.parent_id,
        ct.level + 1
    FROM `my-bucket`.`_default`.`_default` c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE c.type = 'category'
)
SELECT * FROM category_tree
ORDER BY level, name;

三、窗口函数 #

3.1 ROW_NUMBER #

sql 
SELECT 
    name,
    city,
    age,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY age DESC) AS age_rank
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

3.2 RANK和DENSE_RANK #

sql 
SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'student';

3.3 聚合窗口函数 #

sql 
SELECT 
    name,
    city,
    amount,
    SUM(amount) OVER (PARTITION BY city) AS city_total,
    AVG(amount) OVER (PARTITION BY city) AS city_avg,
    COUNT(*) OVER (PARTITION BY city) AS city_count
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order';

3.4 LEAD和LAG #

sql 
SELECT 
    date,
    amount,
    LAG(amount, 1) OVER (ORDER BY date) AS prev_amount,
    LEAD(amount, 1) OVER (ORDER BY date) AS next_amount,
    amount - LAG(amount, 1) OVER (ORDER BY date) AS diff
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'daily_sales'
ORDER BY date;

3.5 FIRST_VALUE和LAST_VALUE #

sql 
SELECT 
    name,
    city,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY city ORDER BY score DESC) AS city_top_score,
    LAST_VALUE(score) OVER (
        PARTITION BY city 
        ORDER BY score DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS city_bottom_score
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'student';

3.6 NTILE #

sql 
SELECT 
    name,
    score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'student';

四、数组操作 #

4.1 UNNEST展开数组 #

sql 
SELECT 
    name,
    hobby
FROM `my-bucket`.`_default`.`_default`
UNNEST hobbies AS hobby
WHERE type = 'user';

4.2 UNNEST与条件 #

sql 
SELECT 
    name,
    hobby
FROM `my-bucket`.`_default`.`_default`
UNNEST hobbies AS hobby
WHERE type = 'user' AND hobby LIKE '%编程%';

4.3 嵌套数组展开 #

sql 
SELECT 
    name,
    order_item.product_id,
    order_item.quantity
FROM `my-bucket`.`_default`.`_default`
UNNEST order_items AS order_item
WHERE type = 'order';

4.4 数组聚合 #

sql 
SELECT 
    name,
    ARRAY_AGG(hobby) AS all_hobbies,
    ARRAY_LENGTH(ARRAY_AGG(hobby)) AS hobby_count
FROM `my-bucket`.`_default`.`_default`
UNNEST hobbies AS hobby
WHERE type = 'user'
GROUP BY name;

4.5 数组函数 #

sql 
SELECT 
    name,
    hobbies,
    ARRAY_LENGTH(hobbies) AS count,
    ARRAY_CONTAINS(hobbies, '编程') AS likes_coding,
    ARRAY_FIRST(hobbies) AS first_hobby,
    ARRAY_LAST(hobbies) AS last_hobby
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

五、高级聚合 #

5.1 GROUP BY ROLLUP #

sql 
SELECT 
    city,
    status,
    COUNT(*) AS count,
    SUM(amount) AS total
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order'
GROUP BY ROLLUP(city, status);

5.2 GROUP BY CUBE #

sql 
SELECT 
    city,
    status,
    COUNT(*) AS count
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order'
GROUP BY CUBE(city, status);

5.3 GROUP BY GROUPING SETS #

sql 
SELECT 
    city,
    status,
    COUNT(*) AS count
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order'
GROUP BY GROUPING SETS (
    (city),
    (status),
    (city, status)
);

5.4 GROUPING函数 #

sql 
SELECT 
    city,
    status,
    GROUPING(city) AS is_city_total,
    GROUPING(status) AS is_status_total,
    COUNT(*) AS count
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order'
GROUP BY ROLLUP(city, status);

六、CASE表达式高级用法 #

6.1 复杂条件 #

sql 
SELECT 
    name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade,
    CASE
        WHEN score >= 90 AND attendance >= 95 THEN '优秀'
        WHEN score >= 80 AND attendance >= 90 THEN '良好'
        ELSE '一般'
    END AS performance
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'student';

6.2 CASE在聚合中 #

sql 
SELECT 
    city,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count,
    SUM(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_total
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
GROUP BY city;

七、集合操作 #

7.1 UNION #

sql 
SELECT name, 'user' AS type FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND city = '北京'
UNION
SELECT name, 'admin' AS type FROM `my-bucket`.`_default`.`_default`
WHERE type = 'admin' AND city = '北京';

7.2 UNION ALL #

sql 
SELECT name FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND city = '北京'
UNION ALL
SELECT name FROM `my-bucket`.`_default`.`_default`
WHERE type = 'admin' AND city = '北京';

7.3 INTERSECT #

sql 
SELECT user_id FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order' AND YEAR(created_at) = 2024
INTERSECT
SELECT user_id FROM `my-bucket`.`_default`.`_default`
WHERE type = 'review' AND YEAR(created_at) = 2024;

7.4 EXCEPT #

sql 
SELECT user_id FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND status = 'active'
EXCEPT
SELECT user_id FROM `my-bucket`.`_default`.`_default`
WHERE type = 'order' AND YEAR(created_at) = 2024;

八、高级过滤 #

8.1 EXISTS #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default` u
WHERE u.type = 'user' 
  AND EXISTS (
    SELECT 1 
    FROM `my-bucket`.`_default`.`_default` o
    WHERE o.type = 'order' 
      AND o.user_id = META(u).id
      AND o.amount > 1000
  );

8.2 NOT EXISTS #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default` u
WHERE u.type = 'user'
  AND NOT EXISTS (
    SELECT 1 
    FROM `my-bucket`.`_default`.`_default` o
    WHERE o.type = 'order' AND o.user_id = META(u).id
  );

8.3 ANY和ALL #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'product' 
  AND price > ANY (
    SELECT price 
    FROM `my-bucket`.`_default`.`_default`
    WHERE type = 'product' AND category = 'electronics'
  );

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'product'
  AND price > ALL (
    SELECT price 
    FROM `my-bucket`.`_default`.`_default`
    WHERE type = 'product' AND category = 'budget'
  );

九、正则表达式 #

9.1 REGEXP_LIKE #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' 
  AND REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$');

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
  AND REGEXP_LIKE(phone, '^1[3-9]\\d{9}$');

9.2 REGEXP_CONTAINS #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user'
  AND REGEXP_CONTAINS(name, '张|李|王');

9.3 REGEXP_POSITION #

sql 
SELECT 
    name,
    REGEXP_POSITION(name, '\\d+') AS number_position
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user';

十、查询优化技巧 #

10.1 使用索引提示 #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
USE INDEX (idx_users_city)
WHERE type = 'user' AND city = '北京';

10.2 使用覆盖索引 #

sql 
CREATE INDEX idx_users_covering 
ON `my-bucket`.`_default`.`_default`(city, name, email)
WHERE type = 'user';

SELECT city, name, email
FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND city = '北京';

10.3 避免全表扫描 #

sql 
SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND LOWER(name) = '张三';

SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND name = '张三';

10.4 使用EXPLAIN分析 #

sql 
EXPLAIN SELECT * FROM `my-bucket`.`_default`.`_default`
WHERE type = 'user' AND city = '北京';

EXPLAIN SELECT u.name, COUNT(o.id)
FROM `my-bucket`.`_default`.`_default` u
JOIN `my-bucket`.`_default`.`_default` o ON META(u).id = o.user_id
WHERE u.type = 'user' AND o.type = 'order'
GROUP BY u.name;

十一、总结 #

高级查询要点：

功能 说明
子查询 嵌套查询，支持SELECT/WHERE/FROM
CTE 公共表表达式，提高可读性
窗口函数 排名、聚合、前后值
数组操作 UNNEST展开，数组函数
集合操作 UNION/INTERSECT/EXCEPT

最佳实践：

  1. 复杂查询使用CTE提高可读性
  2. 合理使用窗口函数避免多次查询
  3. 注意子查询性能，优先使用JOIN
  4. 使用EXPLAIN分析查询计划
  5. 为常用查询字段创建索引

下一步，让我们学习多表查询！

最后更新：2026-03-27