Qwen：Qwen3，R1 在 Text2SQL 效果评估

06-01 1648阅读

【对比模型】

  • Qwen3 235B-A22B（2350亿总参数，220亿激活参数），32B，30B-A3B；
  • QwQ 32B（推理模型）
  • DeepSeek-R1 671B（满血版）（推理模型）

    1，跑分对比

    【官方对比】

    指标/模型Qwen3 235B-A22BQwen3-32BQwen3-30B-A3BQwQ-32BDeepSeek-R1 671B-A37B
    AIME 2485.781.480.479.579.8
    AIME 2581.572.970.969.570.0
    LiveCodeBench70.765.762.662.7/63.464.3/65.9
    LiveBench77.774.974.372.0/73.171.6
    BFCL70.870.369.166.456.9/60.3
    ArenaHard95.693.891.089.593.2
    CodeForces20561977197419822029
    Aider61.850.2--56.9
    MultiIF70.870.372.268.356.9
    GPQA--65.865.6-
    IFEval---83.983.3

    PS：表中多个数据：62.7/63.4。左出自 Qwen3 博客，右出自 QwQ-32B 论文。

    【结论总结】

    • Qwen3 235B-A22B 为全方位领先模型，在所有公开指标中几乎都是最高分，尤其在数学、代码、对话与逻辑推理方面，具备显著优势。

    • Qwen3-32B 和 30B-A3B 是成本更低但保持较好性能的替代选项，适合资源有限的场景。

    • QwQ 32B 和 DeepSeek-R1 在部分指标表现尚可（如IFEval、CodeForces），但总体偏弱，且不稳定。

    • DeepSeek-R1 的表现不及其模型规模（671B），说明其效率或能力未充分发挥。

      【数据集介绍】

      • AIME 24 / 25：数学竞赛题，考查数学推理能力。

      • LiveCodeBench：代码生成任务。

      • LiveBench：多任务真实世界评测。

      • BFCL、ArenaHard：多为复杂推理或语言理解任务。

      • CodeForces：程序竞赛分数（越高越好）。

      • Aider、MultiIF、GPQA、IFEval：代码辅助、复杂推理、多跳问答等任务。

        Qwen：Qwen3，R1 在 Text2SQL 效果评估
        （图片来源网络，侵删）

        2，模型架构

        指标/模型Qwen3 235B-A22BQwen3-32BQwen3-30B-A3BQwQ-32BDeepSeek-R1 671B-A37B
        种类MoEDenseMoEDenseMoE
        总参数235B32B30B32B671B
        网络层数9464486462
        注意力头数64 / 464 / 832 / 440 / 8-
        上下文长度128K128K128K128K128K
        • 235B：模型总参数；A22B：MoE架构下的激活参数；

          【MoE & Dense】

          • Dense（稠密）模型是指：每个输入都激活模型中所有的参数，所有的神经网络层和单元都会参与到每一次前向传播计算中。

          • MoE 模型是指：为每个输入动态选择一部分“专家子网络”进行激活和计算，而不是激活全部参数。比如，一个 MoE 层里有 64 个专家网络，但每次输入只选其中 2~4 个进行前向传播。

            Qwen：Qwen3，R1 在 Text2SQL 效果评估
            （图片来源网络，侵删）
            特性Dense 模型MoE 模型
            每次激活参数量全部参数部分专家（如 2/64）
            参数规模通常较小（如70B）可超大（如1T），但每次推理只用一小部分
            推理成本高相对低
            性能提升潜力线性增长潜在指数增长
            实现复杂度简单高，需要路由和负载均衡机制

            3，语义SQL解析

            任务描述：

            你的目标是将给定的自然语言查询（NLQ）转换为相应的SQL查询语句。我们希望通过测试不同的LLM模型，在语义理解和生成SQL语句的准确性方面，找到它们之间的差异。

            Qwen：Qwen3，R1 在 Text2SQL 效果评估
            （图片来源网络，侵删）

            请根据以下自然语言查询，生成对应的SQL查询语句。

            自然语言查询示例：

            1. 查询1: "获取所有销售额大于1000的订单的订单号、客户姓名和订单日期。"

            2. 查询2: "找到所有在2023年1月1日到2023年12月31日之间下单的客户的名称和他们的总购买金额。"

            3. 查询3: "返回所有购买了'电子产品'类别商品的客户信息，以及他们购买的商品的名称。"

            4. 查询4: "获取2022年第二季度内，每个地区的平均销售额和总订单数。"

            5. 查询5: "列出所有库存低于50的产品信息，包括产品名称、库存数量和价格。"

            数据库表结构（假设）：

            • orders (订单表): order_id, customer_id, order_date, total_amount, region

            • customers (客户表): customer_id, customer_name

            • products (产品表): product_id, product_name, category, stock_quantity, price

            • order_items (订单项表): order_id, product_id, quantity, price

              输出：

              对于每个查询，请生成一个相应的SQL查询语句。

              【测试结果】Qwen3 235B-A22B & DeepSeek-R1 671B-A37B 胜出

              指标/模型Qwen3 235B-A22BQwen3-32BQwen3-30B-A3BQwQ-32BDeepSeek-R1 671B-A37B
              查询1√√√√√
              查询2⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐
              查询3√√√√√
              查询4√√√√√
              查询5√√√√√
              • 查询1: "获取所有销售额大于1000的订单的订单号、客户姓名和订单日期。" 
                # deepseek-r1
SELECT o.order_id, c.customer_name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > 1000;
# qwen3-235b-a22b
SELECT o.order_id, c.customer_name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > 1000;
# qwen3-32b
SELECT o.order_id, c.customer_name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > 1000;
# qwen3-30b-a3b
SELECT orders.order_id, customers.customer_name, orders.order_date FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.total_amount > 1000;
# qwq:32b
SELECT o.order_id, c.customer_name, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total_amount > 1000;
                • 查询2: "找到所有在2023年1月1日到2023年12月31日之间下单的客户的名称和他们的总购买金额。" 
                  # deepseek-r1
SELECT c.customer_name, SUM(o.total_amount) AS total_purchase FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY c.customer_id;
# qwen3-235b-a22b
SELECT c.customer_name, SUM(o.total_amount) AS total_purchase_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY c.customer_name;
# qwen3-32b
SELECT c.customer_name, SUM(o.total_amount) AS total_purchase FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY c.customer_id, c.customer_name;
# qwen3-30b-a3b
SELECT customers.customer_name, SUM(orders.total_amount) AS total_purchase FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY customers.customer_id, customers.customer_name;
# qwq:32b
SELECT c.customer_name, SUM(o.total_amount) AS total_purchase_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY c.customer_id, c.customer_name;
                  • 查询3: "返回所有购买了'电子产品'类别商品的客户信息，以及他们购买的商品的名称。" 
                    # deepseek-r1
SELECT DISTINCT c.*, p.product_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items i ON o.order_id = i.order_id JOIN products p ON i.product_id = p.product_id WHERE p.category = '电子产品';
# qwen3-235b-a22b
SELECT DISTINCT c.*, p.product_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id WHERE p.category = '电子产品';
# qwen3-32b
SELECT c.customer_name, p.product_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id WHERE p.category = '电子产品';
# qwen3-30b-a3b
SELECT customers.customer_name, products.product_name FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN order_items ON orders.order_id = order_items.order_id JOIN products ON order_items.product_id = products.product_id WHERE products.category = '电子产品';
# qwq:32b
SELECT DISTINCT c.*, p.product_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id WHERE p.category = '电子产品';
                    • 查询4: "获取2022年第二季度内，每个地区的平均销售额和总订单数。" 
                      # deepseek-r1
SELECT region, AVG(total_amount) AS avg_sales, COUNT(order_id) AS total_orders FROM orders WHERE order_date BETWEEN '2022-04-01' AND '2022-06-30' GROUP BY region;
# qwen3-235b-a22b
SELECT region, AVG(total_amount) AS avg_sales, COUNT(*) AS total_orders FROM orders WHERE order_date BETWEEN '2022-04-01' AND '2022-06-30' GROUP BY region;
# qwen3-32b
SELECT o.region, AVG(o.total_amount) AS avg_sales, COUNT(o.order_id) AS total_orders FROM orders o WHERE o.order_date BETWEEN '2022-04-01' AND '2022-06-30' GROUP BY o.region;
# qwen3-30b-a3b
SELECT region, AVG(total_amount) AS avg_sales, COUNT(order_id) AS total_orders FROM orders WHERE order_date BETWEEN '2022-04-01' AND '2022-06-30' GROUP BY region;
# qwq:32b
SELECT region, AVG(total_amount) AS average_sales, COUNT(order_id) AS total_orders FROM orders WHERE order_date BETWEEN '2022-04-01' AND '2022-06-30' GROUP BY region;
                      • 查询5: "列出所有库存低于50的产品信息，包括产品名称、库存数量和价格。" 
                        # deepseek-r1
SELECT product_name, stock_quantity, price FROM products WHERE stock_quantity  
 

                        4，复杂语义SQL解析
                         
 
 
                        【测试1】
                         
 
                        你是一个资深的数据分析师，负责为公司的业务部门从数据库中提取关键数据。请根据以下复杂的自然语言业务需求，编写一条等价的 SQL 查询语句，并确保语义准确、逻辑严谨、结果符合业务预期。
                         
 
                        【业务需求】找出在 2023 年第二季度内，购买了 "旗舰X200" 产品的客户中，总消费金额排名前 10 的客户姓名和他们所在的城市，要求这些客户的订单中至少包含一次“线上”支付，并且不包含任何“退款”记录。
                         
 
                        【数据库表结构说明】
                        1. customers(id, name, city)
                        2. orders(id, customer_id, order_date, payment_method, status)
                        3. order_items(id, order_id, product_name, quantity, price)
                         
 
                        说明：
                        - 一个 customer 可以有多个 orders；
                        - 一个 order 包含多个 order_items；
                        - payment_method 可能的值有 '线上', '线下'；
                        - status 可能的值有 '已完成', '退款', '取消'；
                        - order_date 是日期格式。
                         
 
                        【额外说明】
                        - 请仅返回 SQL 语句，不要附带解释；
                        - 若需使用子查询、联结或聚合，请合理安排；
                        - 所有字段和表名都来自上述数据库结构；
                        - 尽可能使用标准 SQL 语法，确保在 MySQL 或 PostgreSQL 上兼容。
                         
 
                        【返回格式】
                        ```sql
                        -- SQL 查询语句
                        SELECT ...
                         
 
                        【测试结果】Qwen3 235B-A22B & DeepSeek-R1 671B-A37B 胜出
                        • deepseek-r1：7，有效筛选客户，但金额统计和子条件时间范围不严谨
                        • qwen3-32b：9，少量语义模糊
                        • qwq:32b：10
                        • qwen3-30b-a3b：6，子查询没有限定时间
                        • qwen3-235b-a22b：10 
                          # deepseek-r1
SELECT c.name, c.city, SUM(oi.quantity * oi.price) AS total_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE c.id IN (
    SELECT DISTINCT o.customer_id
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE oi.product_name = '旗舰X200'
    AND o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
)
AND EXISTS (
    SELECT 1
    FROM orders o2
    WHERE o2.customer_id = c.id
    AND o2.payment_method = '线上'
)
AND NOT EXISTS (
    SELECT 1
    FROM orders o3
    WHERE o3.customer_id = c.id
    AND o3.status = '退款'
)
GROUP BY c.id, c.name, c.city
ORDER BY total_amount DESC
LIMIT 10;
                          # qwen3-32b
SELECT 
    c.name, 
    c.city, 
    SUM(CASE WHEN o.status = '已完成' THEN oi.quantity * oi.price ELSE 0 END) AS total_spent
FROM 
    customers c
JOIN 
    orders o ON c.id = o.customer_id
JOIN 
    order_items oi ON o.id = oi.order_id
WHERE 
    oi.product_name = '旗舰X200'
    AND o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
GROUP BY 
    c.id, c.name, c.city
HAVING 
    SUM(CASE WHEN o.status = '退款' THEN 1 ELSE 0 END) = 0
    AND SUM(CASE WHEN o.payment_method = '线上' AND o.status = '已完成' THEN 1 ELSE 0 END) >= 1
ORDER BY 
    total_spent DESC
LIMIT 10;
                          # qwq:32b
SELECT c.name, c.city, SUM(oi.quantity * oi.price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
AND c.id IN (
    SELECT DISTINCT o2.customer_id
    FROM orders o2
    JOIN order_items oi2 ON o2.id = oi2.order_id
    WHERE o2.order_date BETWEEN '2023-04-01' AND '2023-06-30'
    AND oi2.product_name = '旗舰X200'
)
GROUP BY c.id, c.name, c.city
HAVING 
    EXISTS (
        SELECT 1
        FROM orders o3
        WHERE o3.customer_id = c.id
        AND o3.payment_method = '线上'
        AND o3.order_date BETWEEN '2023-04-01' AND '2023-06-30'
    )
    AND NOT EXISTS (
        SELECT 1
        FROM orders o4
        WHERE o4.customer_id = c.id
        AND o4.status = '退款'
        AND o4.order_date BETWEEN '2023-04-01' AND '2023-06-30'
    )
ORDER BY total_spent DESC
LIMIT 10;
                          # qwen3-30b-a3b
SELECT c.name, c.city, SUM(oi.quantity * oi.price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
  AND oi.product_name = '旗舰X200'
  AND o.status != '退款'
  AND c.id NOT IN (SELECT customer_id FROM orders WHERE status = '退款')
GROUP BY c.id, c.name, c.city
HAVING SUM(CASE WHEN o.payment_method = '线上' THEN 1 ELSE 0 END) >= 1
ORDER BY total_spent DESC
LIMIT 10;
                          # qwen3-235b-a22b
SELECT c.name, c.city, SUM(oi.quantity * oi.price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
  AND NOT EXISTS (
    SELECT 1
    FROM orders o2
    WHERE o2.customer_id = c.id
      AND o2.order_date BETWEEN '2023-04-01' AND '2023-06-30'
      AND o2.status = '退款'
  )
  AND EXISTS (
    SELECT 1
    FROM orders o3
    JOIN order_items oi2 ON o3.id = oi2.order_id
    WHERE o3.customer_id = c.id
      AND o3.order_date BETWEEN '2023-04-01' AND '2023-06-30'
      AND oi2.product_name = '旗舰X200'
  )
GROUP BY c.id, c.name, c.city
HAVING SUM(CASE WHEN o.payment_method = '线上' THEN 1 ELSE 0 END) >= 1
ORDER BY total_spent DESC
LIMIT 10;

                          5，SQL 语句理解

                          你是一个数据库专家，请仔细阅读以下表结构和 SQL 查询，只用一句话描述出该SQL查询的作用。

                          表结构：

                          -- 用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    signup_date DATETIME,
    referrer_id INT
);
-- 订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    order_date DATETIME,
    amount DECIMAL(10,2),
    status ENUM('pending', 'completed', 'canceled')
);
-- 产品表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    category_id INT,
    price DECIMAL(10,2),
    name VARCHAR(100)
);
-- 类别表
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);
-- 售后表
CREATE TABLE refunds (
    refund_id INT PRIMARY KEY,
    order_id INT,
    refund_amount DECIMAL(10,2),
    refund_date DATETIME
);

                          SQL 查询：

                          SELECT 
    u.name AS user_name,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(CASE 
        WHEN o.status = 'completed' THEN o.amount 
        ELSE 0 END) AS completed_amount,
    (
        SELECT COUNT(*) 
        FROM orders o2 
        WHERE o2.user_id = u.user_id 
          AND o2.status = 'canceled' 
          AND EXISTS (
              SELECT 1 
              FROM refunds r 
              WHERE r.order_id = o2.order_id 
                AND r.refund_amount > (
                    SELECT AVG(p.price) 
                    FROM products p 
                    WHERE p.category_id IN (
                        SELECT category_id 
                        FROM products 
                        WHERE product_id = o2.product_id
                    )
                )
          )
    ) AS suspicious_cancellations,
    (
        SELECT GROUP_CONCAT(DISTINCT c.category_name)
        FROM categories c
        WHERE c.category_id IN (
            SELECT p.category_id
            FROM products p
            WHERE p.product_id IN (
                SELECT o3.product_id
                FROM orders o3
                WHERE o3.user_id = u.user_id
                  AND o3.order_date BETWEEN 
                      DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND CURDATE()
            )
        )
    ) AS recent_categories
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.signup_date  1000
        )
  )
GROUP BY u.user_id, u.name
HAVING completed_amount > 500
   AND suspicious_cancellations > 1
ORDER BY completed_amount DESC
LIMIT 10;

                          【测试结果】正确答案：找出注册时间超过一年的活跃高消费用户（Top 10），并标记他们是否有未解决的投诉。

                          • deepseek-r1：9
                          • qwen3-32b：10
                          • qwq:32b：6
                          • qwen3-30b-a3b：7
                          • qwen3-235b-a22b：9.5 
                            # deepseek-r1
该SQL查询的作用是：筛选出注册超过一年、推荐人有大额成交订单的用户中，完成订单金额超过500且存在超过1次退款金额高于同类产品均价的异常取消订单的用户，统计其总订单数、完成金额、可疑取消次数及最近半年的购买品类，并按完成金额降序取前10名。
# qwen3-32b
该SQL查询旨在找出注册超过一年且由推荐人（推荐人至少有一个金额超过1000的已完成订单）引入的用户，统计他们的总订单数、已完成订单金额、可疑取消订单数（取消订单的退款金额超过对应产品类别的平均价格）及最近6个月购买的产品类别，筛选出已完成金额超500且可疑取消订单数超1的用户，按已完成金额降序返回前10名。
# qwq:32b
该SQL查询用于筛选出注册超过1年的用户中，由成功推荐人推荐、且在过去6个月有可疑退款行为（退款金额超过产品所属类别均价的取消订单超过1次）、年度完成订单金额超500元的用户，并统计其订单数、完成金额、可疑取消次数及近期购买品类，最终按消费金额降序取前10名。
# qwen3-30b-a3b
该SQL查询用于识别注册超过一年、其推荐人有大额订单的用户，统计其总订单数、完成金额，并筛选出存在多个可疑取消订单（退款金额高于所购产品类别的平均价格）及近期购买多类商品的高价值用户，按完成金额降序排列前10名。
# qwen3-235b-a22b
该SQL查询用于筛选出注册时间超过一年、其推荐人有过单笔订单金额超1000元的用户中，自身有效订单总金额超过500元且存在两个以上可疑取消订单（取消后退款金额高于同类商品均价）的用户，并展示其最近六个月购买过的商品类别，按有效订单金额降序返回前十名用户信息。

                            6，SQL 补全改写

                            你正在维护一个销售分析系统。现在有一个 SQL 片段用于分析客户的购买行为，但由于开发中断，该 SQL 残缺不全。请你根据提供的语义和表结构，尝试补写和改全这个 SQL，使其逻辑完整并可以在 MySQL 中正确执行，只需要输出缺失部分即可。

                            数据库结构（部分）：

                            -- 用户表
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  phone VARCHAR(20)
);
-- 订单表
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2),
  order_time DATETIME,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

                            任务目标语义：我们希望找出在过去3个月中，连续两次下单金额均高于该用户平均订单金额的用户姓名、联系方式、以及他们最近一次下单的时间。

                            初步 SQL 查询语句：

                            SELECT u.name, u.phone, latest_order_time
FROM users u
JOIN (
  SELECT user_id, MAX(order_time) AS latest_order_time
  FROM orders
  WHERE order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
  GROUP BY user_id
) t ON u.id = t.user_id
WHERE u.id IN (
  -- 请补全这个子查询

                            【测试结果】

                            • deepseek-r1：8.0，使用窗口函数但平均值时间范围错误
                            • qwen3-32b：7.0，不保证连续性
                            • qwq:32b：5.0，差异过大
                            • qwen3-30b-a3b：9.0
                            • qwen3-235b-a22b：9.5 
                              # deepseek-r1
SELECT DISTINCT user_id
FROM (
  SELECT 
    o.user_id,
    o.amount,
    ua.avg_amount,
    LEAD(o.amount) OVER (PARTITION BY o.user_id ORDER BY o.order_time) AS next_amount
  FROM orders o
  JOIN (
    SELECT user_id, AVG(amount) AS avg_amount
    FROM orders
    GROUP BY user_id
  ) ua ON o.user_id = ua.user_id
  WHERE o.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
) t
WHERE t.amount > t.avg_amount AND t.next_amount > t.avg_amount
)
# qwen3-32b
SELECT user_id
FROM (
    SELECT 
        o.user_id,
        o.amount,
        ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.order_time DESC) AS rn,
        (SELECT AVG(amount) FROM orders o2 
         WHERE o2.user_id = o.user_id 
           AND o2.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS avg_amount
    FROM orders o
    WHERE o.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
) ranked
WHERE rn  avg_amount
GROUP BY user_id
HAVING COUNT(*) = 2
# qwq:32b
SELECT user_id FROM orders o JOIN ( SELECT user_id, AVG(amount) AS avg_amt FROM orders GROUP BY user_id ) a ON o.user_id = a.user_id WHERE o.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH) AND o.amount > a.avg_amt GROUP BY o.user_id HAVING COUNT(*) >= 2
# qwen3-30b-a3b
SELECT user_id
FROM (
    SELECT 
        user_id,
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn
    FROM orders
    WHERE order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
) AS ranked
WHERE rn  (SELECT AVG(amount) FROM orders WHERE user_id = ranked.user_id)
    AND MAX(CASE WHEN rn = 2 THEN amount END) > (SELECT AVG(amount) FROM orders WHERE user_id = ranked.user_id)
# qwen3-235b-a22b
SELECT user_id
FROM (
    SELECT 
        o.user_id,
        o.amount AS curr_amount,
        LAG(o.amount) OVER (PARTITION BY o.user_id ORDER BY o.order_time) AS prev_amount,
        ua.avg_amount
    FROM orders o
    INNER JOIN (
        SELECT user_id, AVG(amount) AS avg_amount
        FROM orders
        GROUP BY user_id
    ) ua ON o.user_id = ua.user_id
    WHERE o.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
) t
WHERE curr_amount > avg_amount AND prev_amount > avg_amount

                              7，SQL 修改纠正

                              任务：你将看到一条SQL查询语句及其语义描述。该查询语句存在某些错误，需要你帮助修正。请仔细检查并修复SQL中的错误。错误可能非常隐蔽，要求你深刻理解语法和语义，确保修复后的查询语句能够正确执行。只返回修改后的SQL语句即可。

                              给定 SQL 查询语句：

                              SELECT users.name, orders.product, payments.payment_amount 
FROM users
JOIN orders ON users.id = orders.user_id 
JOIN payments ON orders.order_id = payments.order_id 
WHERE users.age > 25 
  AND payments.payment_date > '2024-01-01' 
  AND orders.order_date = '2024-01-01'
  AND orders.amount > 100
ORDER BY payments.payment_amount DESC;

                              表结构：

                              • users 表： id INT PRIMARY KEY name VARCHAR(100) email VARCHAR(100) age INT
                              • orders 表： order_id INT PRIMARY KEY user_id INT product VARCHAR(100) order_date DATE amount DECIMAL(10, 2)
                              • payments 表： payment_id INT PRIMARY KEY order_id INT payment_date DATE payment_amount DECIMAL(10, 2)

                                语义：从users表中查询所有年龄大于25的用户的名字，查询他们的订单信息（包括产品和支付金额）。只返回在2024年1月1日之后付款的订单，且订单金额大于100。

                                【测试结果】

                                • deepseek-r1：10
                                • qwen3-32b：10
                                • qwq:32b：10
                                • qwen3-30b-a3b：6
                                • qwen3-235b-a22b：10 
                                  # deepseek-r1
SELECT users.name, orders.product, payments.payment_amount 
FROM users
JOIN orders ON users.id = orders.user_id 
JOIN payments ON orders.order_id = payments.order_id 
WHERE users.age > 25 
  AND payments.payment_date > '2024-01-01' 
  AND orders.amount > 100
ORDER BY payments.payment_amount DESC;
# qwen3-32b
SELECT users.name, orders.product, payments.payment_amount FROM users JOIN orders ON users.id = orders.user_id JOIN payments ON orders.order_id = payments.order_id WHERE users.age > 25 AND payments.payment_date > '2024-01-01' AND orders.amount > 100 ORDER BY payments.payment_amount DESC;
# qwq:32b
SELECT users.name, orders.product, payments.payment_amount FROM users JOIN orders ON users.id = orders.user_id JOIN payments ON orders.order_id = payments.order_id WHERE users.age > 25 AND payments.payment_date > '2024-01-01' AND orders.amount > 100 ORDER BY payments.payment_amount DESC;
# qwen3-30b-a3b
SELECT users.name, orders.product, payments.payment_amount FROM users JOIN orders ON users.id = orders.user_id JOIN payments ON orders.order_id = payments.order_id WHERE users.age > 25 AND payments.payment_date > '2024-01-01' AND orders.order_date = '2024-01-01' AND orders.amount > 100 ORDER BY payments.payment_amount DESC;
# qwen3-235b-a22b
SELECT users.name, orders.product, payments.payment_amount FROM users JOIN orders ON users.id = orders.user_id JOIN payments ON orders.order_id = payments.order_id WHERE users.age > 25 AND payments.payment_date > '2024-01-01' AND orders.amount > 100 ORDER BY payments.payment_amount DESC;
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。

相关阅读

目录[+]

取消
微信二维码
微信二维码
支付宝二维码