生成复杂SQL查询语句-提示词库

敬请期待...

# Role: 专业的SQL生成助手
- Description: 你是一位精通SQL语言的专家，对ANSI SQL标准有深入的理解，能够根据用户提供的自然语言需求，准确生成符合要求的SQL查询语句。你擅长处理各种数据类型，并能合理假设字段名以生成可执行的SQL语句。
- Skills: 你具备将自然语言需求转化为SQL语句的能力，能够准确处理字符串和数值型字段，合理使用WHERE条件、ORDER BY排序、LIMIT和OFFSET分页等SQL语句组件。你能够生成简单、清晰且可直接执行的SQL语句，避免复杂嵌套查询，除非用户明确要求。
- Rules: 仅生成查询（SELECT）语句，禁止生成任何修改、删除、创建数据库或表的SQL语句，如DROP、DELETE、UPDATE、INSERT、ALTER、TRUNCATE等。如果用户没有指定查询的字段，默认使用SELECT *。在WHERE条件中，自动合理处理字符串加引号，数值型字段不加引号。如果用户指定了排序，使用ORDER BY，默认升序（ASC），如果明确说明降序，使用DESC。如果用户提供了分页信息，添加LIMIT和OFFSET。
- OutputFormat: 只返回SQL语句本身，不需要任何额外解释或说明。
- Workflow:
  1. 确认用户提供的表名、字段名、查询条件、排序要求、分页参数等信息。
  2. 根据用户需求，构建基础的SELECT语句，合理假设字段名以确保语句可执行。
  3. 在WHERE条件中，根据字段类型合理处理字符串加引号和数值型字段不加引号。
  4. 如果用户指定了排序，添加ORDER BY子句，默认升序（ASC），如果明确说明降序，使用DESC。
  5. 如果用户提供了分页信息，添加LIMIT和OFFSET子句。
  6. 确保生成的SQL语句简单、清晰、可直接执行，避免复杂嵌套查询，除非用户明确要求。
输入：从 users 表中筛选，只选出年龄大于18岁，**且状态为启用状态（"active"）**的用户；
这些用户的ID必须在 orders 表中下过至少一笔订单的用户ID列表中（即 user_id 存在于 orders 表中）；
需要关联查询每个用户的最近一笔订单信息（通过 LEFT JOIN 连接 orders 表，按订单创建时间降序，取最新）；
查询结果中请返回：
用户姓名 (users.name)、邮箱 (users.email)、注册时间 (users.created_at)、
最近订单编号 (orders.order_number)、最近订单创建时间 (orders.created_at)；
最终结果要求：
按用户注册时间降序排序；
按用户的邮箱域名进行分组统计（比如按 @gmail.com、@yahoo.com分类），显示每个域名下的用户数量；
只取出前10条结果进行展示。

```sql SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users LEFT JOIN (SELECT user_id, order_number, created_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn FROM orders) orders ON users.id = orders.user_id AND orders.rn = 1 WHERE users.age > 18 AND users.status = 'active' AND users.id IN (SELECT user_id FROM orders) GROUP BY users.name, users.email, users.created_at, orders.order_number, orders.created_at ORDER BY users.created_at DESC LIMIT 10; ```

```sql SELECT u.name AS "用户姓名", u.email AS "邮箱", u.created_at AS "注册时间", o.order_number AS "最近订单编号", o.created_at AS "最近订单创建时间", COUNT(*) OVER (PARTITION BY SUBSTRING(u.email FROM '@(.*)$')) AS "域名用户数量" FROM users u LEFT JOIN ( SELECT o1.*, ROW_NUMBER() OVER (PARTITION BY o1.user_id ORDER BY o1.created_at DESC) AS rn FROM orders o1 ) o ON u.id = o.user_id AND o.rn = 1 WHERE u.age > 18 AND u.status = 'active' AND u.id IN (SELECT DISTINCT user_id FROM orders) ORDER BY u.created_at DESC LIMIT 10; ```

```sql SELECT users.name, users.email, users.created_at, recent_orders.order_number, recent_orders.created_at FROM users LEFT JOIN ( SELECT user_id, order_number, created_at FROM orders WHERE user_id IN (SELECT user_id FROM orders) ORDER BY created_at DESC ) as recent_orders ON users.id = recent_orders.user_id WHERE users.age > 18 AND users.status = 'active' ORDER BY users.created_at DESC LIMIT 10; ```

```sql SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.age > 18 AND users.status = 'active' AND users.id IN (SELECT user_id FROM orders) ORDER BY users.created_at DESC GROUP BY SUBSTRING_INDEX(users.email, '@', -1) LIMIT 10; ```

模型名称	模型提供商	响应总时长（s）	生成 token 数	首 token 延迟（s）	生成速率（tokens/s）
通义千问2.5-Coder-32B	阿里云百炼	5.17	179	0.55 延迟最小	34.62
DeepSeek V3	Deepseek	9.97	192 内容最多	3.27	19.26
gpt-4o	OpenAI	2.08 响应最快	103	0.92	49.61 速度最快
claude3.5 sonnet 20241022	Anthropic	4.66	0	2.14	0

SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users JOIN orders ON users.id = orders.user_id WHERE users.age > 18 AND users.status = 'active' AND users.id IN (SELECT user_id FROM orders GROUP BY user_id) LEFT JOIN orders ON users.id = orders.user_id ORDER BY orders.created_at DESC LIMIT 1 OFFSET 0 ORDER BY users.created_at DESC LIMIT 10;

SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users LEFT JOIN orders ON users.id = orders.user_id AND orders.created_at IN (SELECT MAX(created_at) FROM orders GROUP BY user_id) WHERE users.age > 18 AND users.status = 'active' GROUP BY users.email ORDER BY users.created_at DESC LIMIT 10;

SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.age > 18 AND users.status = 'active' AND users.id IN (SELECT user_id FROM orders) ORDER BY users.created_at DESC, users.email LIMIT 10

SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.age > 18 AND users.status = 'active' AND users.id IN (SELECT user_id FROM orders) ORDER BY users.created_at DESC, users.email ASC LIMIT 10;

SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.age > 18 AND users.status = 'active' AND users.id IN (SELECT user_id FROM orders) ORDER BY users.created_at DESC, users.email LIMIT 10;

SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.age > 18 AND users.status = 'active' AND users.id IN (SELECT user_id FROM orders) ORDER BY users.created_at DESC, users.email ASC LIMIT 10;

SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.age > 18 AND users.status = 'active' ORDER BY users.created_at DESC, users.email ASC LIMIT 10;

SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.age > 18 AND users.status = 'active' ORDER BY users.created_at DESC LIMIT 10 OFFSET 0;

SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users JOIN orders ON users.id = orders.user_id WHERE users.age > 18 AND users.status = 'active' AND users.id IN (SELECT user_id FROM orders) LEFT JOIN (SELECT user_id, MAX(created_at) AS max_created_at FROM orders GROUP BY user_id) AS recent_orders ON orders.id = recent_orders.user_id AND orders.created_at = recent_orders.max_created_at ORDER BY users.created_at DESC, users.email GROUP BY users.email LIMIT 10;

SELECT users.name, users.email, users.created_at, orders.order_number, orders.created_at FROM users JOIN orders ON users.id = orders.user_id WHERE users.age > 18 AND users.status = 'active' AND users.id IN (SELECT user_id FROM orders) LEFT JOIN orders ON users.id = orders.user_id ORDER BY orders.created_at DESC LIMIT 1 FOR users.id ORDER BY users.created_at DESC GROUP BY users.email LIMIT 10;