query-writing

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Query Writing Skill

SQL查询编写技能

When to Use This Skill

何时使用本技能

Use this skill when you need to answer a question by writing and executing a SQL query.

当你需要通过编写和执行SQL查询来解答问题时，可以使用本技能。

Workflow for Simple Queries

简单查询工作流程

For straightforward questions about a single table:

Identify the table - Which table has the data?
Get the schema - Use
```
sql_db_schema
```
to see columns
Write the query - SELECT relevant columns with WHERE/LIMIT/ORDER BY
Execute - Run with
```
sql_db_query
```
Format answer - Present results clearly

针对涉及单个表的简单问题：

确定数据表 - 哪个表包含所需数据？
获取表结构 - 使用
```
sql_db_schema
```
查看列信息
编写查询语句 - 使用SELECT选择相关列，搭配WHERE/LIMIT/ORDER BY等子句
执行查询 - 使用
```
sql_db_query
```
运行语句
格式化结果 - 清晰展示查询结果

Workflow for Complex Queries

复杂查询工作流程

For questions requiring multiple tables:

针对涉及多个表的问题：

1. Plan Your Approach

1. 规划解决思路

Use
write_todos
to break down the task:

Identify all tables needed
Map relationships (foreign keys)
Plan JOIN structure
Determine aggregations

使用
write_todos
分解任务：

确定所需的所有数据表
梳理表间关系（外键）
规划JOIN结构
确定聚合方式

2. Examine Schemas

2. 查看表结构

Use

sql_db_schema

for EACH table to find join columns and needed fields.

为每个表调用

sql_db_schema

，找到用于关联的列和所需字段。

3. Construct Query

3. 构建查询语句

SELECT - Columns and aggregates
FROM/JOIN - Connect tables on FK = PK
WHERE - Filters before aggregation
GROUP BY - All non-aggregate columns
ORDER BY - Sort meaningfully
LIMIT - Default 5 rows

SELECT - 选择列和聚合函数
FROM/JOIN - 通过外键=主键关联数据表
WHERE - 聚合前过滤数据
GROUP BY - 包含所有非聚合列
ORDER BY - 进行有意义的排序
LIMIT - 默认返回5行数据

4. Validate and Execute

4. 验证并执行

Check all JOINs have conditions, GROUP BY is correct, then run query.

检查所有JOIN是否有关联条件，GROUP BY是否正确，然后执行查询。

Example: Revenue by Country

示例：按国家统计收入

sql

SELECT
    c.Country,
    ROUND(SUM(i.Total), 2) as TotalRevenue
FROM Invoice i
INNER JOIN Customer c ON i.CustomerId = c.CustomerId
GROUP BY c.Country
ORDER BY TotalRevenue DESC
LIMIT 5;

sql

SELECT
    c.Country,
    ROUND(SUM(i.Total), 2) as TotalRevenue
FROM Invoice i
INNER JOIN Customer c ON i.CustomerId = c.CustomerId
GROUP BY c.Country
ORDER BY TotalRevenue DESC
LIMIT 5;

Quality Guidelines

质量规范

Query only relevant columns (not SELECT *)
Always apply LIMIT (5 default)
Use table aliases for clarity
For complex queries: use write_todos to plan
Never use DML statements (INSERT, UPDATE, DELETE, DROP)

仅查询相关列（避免使用SELECT *）
始终添加LIMIT限制（默认5行）
使用表别名提升可读性
处理复杂查询时：使用write_todos进行规划
禁止使用DML语句（INSERT、UPDATE、DELETE、DROP）