Qwen：Qwen3，R1 在 Text2SQL 效果评估

【对比模型】

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

1，跑分对比

【官方对比】

指标/模型	Qwen3 235B-A22B	Qwen3-32B	Qwen3-30B-A3B	QwQ-32B	DeepSeek-R1 671B-A37B
AIME 24	85.7	81.4	80.4	79.5	79.8
AIME 25	81.5	72.9	70.9	69.5	70.0
LiveCodeBench	70.7	65.7	62.6	62.7/63.4	64.3/65.9
LiveBench	77.7	74.9	74.3	72.0/73.1	71.6
BFCL	70.8	70.3	69.1	66.4	56.9/60.3
ArenaHard	95.6	93.8	91.0	89.5	93.2
CodeForces	2056	1977	1974	1982	2029
Aider	61.8	50.2	-	-	56.9
MultiIF	70.8	70.3	72.2	68.3	56.9
GPQA	-	-	65.8	65.6	-
IFEval	-	-	-	83.9	83.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：代码辅助、复杂推理、多跳问答等任务。

2，模型架构

指标/模型	Qwen3 235B-A22B	Qwen3-32B	Qwen3-30B-A3B	QwQ-32B	DeepSeek-R1 671B-A37B
种类	MoE	Dense	MoE	Dense	MoE
总参数	235B	32B	30B	32B	671B
网络层数	94	64	48	64	62
注意力头数	64 / 4	64 / 8	32 / 4	40 / 8	-
上下文长度	128K	128K	128K	128K	128K

• 235B：模型总参数；A22B：MoE架构下的激活参数；

【MoE & Dense】

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

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

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

3，语义SQL解析

任务描述：

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

请根据以下自然语言查询，生成对应的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-A22B	Qwen3-32B	Qwen3-30B-A3B	QwQ-32B	DeepSeek-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 < 50;
# qwen3-235b-a22b
SELECT product_name, stock_quantity, price FROM products WHERE stock_quantity < 50;
# qwen3-32b
SELECT product_name, stock_quantity, price FROM products WHERE stock_quantity < 50;
# qwen3-30b-a3b
SELECT product_name, stock_quantity, price FROM products WHERE stock_quantity < 50;
# qwq:32b
SELECT product_name, stock_quantity, price FROM products WHERE stock_quantity < 50;

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_idFROM orders oJOIN order_items oi ON o.id = oi.order_idWHERE oi.product_name = '旗舰X200'AND o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
)
AND EXISTS (SELECT 1FROM orders o2WHERE o2.customer_id = c.idAND o2.payment_method = '线上'
)
AND NOT EXISTS (SELECT 1FROM orders o3WHERE o3.customer_id = c.idAND 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) = 0AND 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_idFROM orders o2JOIN order_items oi2 ON o2.id = oi2.order_idWHERE 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 1FROM orders o3WHERE o3.customer_id = c.idAND o3.payment_method = '线上'AND o3.order_date BETWEEN '2023-04-01' AND '2023-06-30')AND NOT EXISTS (SELECT 1FROM orders o4WHERE o4.customer_id = c.idAND 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 1FROM orders o2WHERE o2.customer_id = c.idAND o2.order_date BETWEEN '2023-04-01' AND '2023-06-30'AND o2.status = '退款')AND EXISTS (SELECT 1FROM orders o3JOIN order_items oi2 ON o3.id = oi2.order_idWHERE o3.customer_id = c.idAND 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 cWHERE c.category_id IN (SELECT p.category_idFROM products pWHERE p.product_id IN (SELECT o3.product_idFROM orders o3WHERE o3.user_id = u.user_idAND 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 < DATE_SUB(NOW(), INTERVAL 1 YEAR)AND EXISTS (SELECT 1 FROM users u2 WHERE u2.user_id = u.referrer_id AND EXISTS (SELECT 1 FROM orders o4 WHERE o4.user_id = u2.user_id AND o4.status = 'completed'AND o4.amount > 1000))
GROUP BY u.user_id, u.name
HAVING completed_amount > 500AND 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_timeFROM ordersWHERE 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_amountFROM orders oJOIN (SELECT user_id, AVG(amount) AS avg_amountFROM ordersGROUP BY user_id) ua ON o.user_id = ua.user_idWHERE 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_amountFROM orders oWHERE o.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
) ranked
WHERE rn <= 2 AND amount > 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 rnFROM ordersWHERE order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
) AS ranked
WHERE rn <= 2
GROUP BY user_id
HAVING MAX(CASE WHEN rn = 1 THEN amount END) > (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_amountFROM orders oINNER JOIN (SELECT user_id, AVG(amount) AS avg_amountFROM ordersGROUP BY user_id) ua ON o.user_id = ua.user_idWHERE 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;