数据集
公共数据集
公共数据集用于报表复用，减少重复配置，支持 SQL 查询/API 数据集，以下为各类场景的 SQL 编写模板：
一、基础参数使用
1.1 直接使用报表参数
SELECT * FROM orders
WHERE order_date >= $startDate
AND order_date <= $endDate
AND status = $status
SELECT id, name, dept_id, salary
FROM employees
WHERE dept_id = $dept
AND salary >= $minSalary
ORDER BY salary DESC1.2 自定义配置项作为参数
SELECT * FROM orders
WHERE status = $$config.status1.3 模糊查询 LIKE
报表参数 `keyword`，前后模糊匹配：
SELECT id, name, email
FROM employees
WHERE name LIKE ${'' + '%' + $keyword + '%'}
1.4 日期范围计算（最近 N 天）
SELECT order_no, amount, create_time
FROM orders
WHERE create_time >= ${addDays(now(), -$days)}
AND create_time < ${now()}
ORDER BY create_time DESC
二、动态 WHERE 条件（可选参数）
2.1 单个可选条件
报表参数 `status`（可为空）：
sql
SELECT id, order_no, amount, status
FROM orders
WHERE 1 = 1
#{$status != null && $status != '' ? 'AND status = $status' : ''}
ORDER BY create_time DESC
2.2 多个可选条件
报表参数：`dept`、`status`、`startDate`、`endDate`（均可选）：
SELECT id, name, dept_id, status, hire_date
FROM employees
WHERE 1 = 1
#{$dept != null && $dept != '' ? 'AND dept_id = $dept' : ''}
#{$status != null && $status != '' ? 'AND status = $status' : ''}
#{$startDate != null ? 'AND hire_date >= $startDate' : ''}
#{$endDate != null ? 'AND hire_date < $endDate' : ''}
ORDER BY hire_date DESC
2.3 数字类型可选条件
报表参数 `minAmount`（最低金额，可为空）：
SELECT id, order_no, amount
FROM orders
WHERE 1 = 1
#{$minAmount != null ? 'AND amount >= $minAmount' : ''}
ORDER BY amount DESC
2.4 条件组的开关
报表参数 `showAll`（布尔，是否显示全部）：
SELECT id, name, type, enabled
FROM config_items
WHERE #{$showAll == true ? '1 = 1' : 'enabled = 1'}
ORDER BY type, name
三、IN 条件 / 多值参数
3.1 固定 IN 值列表
SELECT id, name, dept_id
FROM employees
WHERE dept_id IN (1, 2, 3)
3.2 动态 IN 列表（逗号分隔字符串）
报表参数 `deptIds`，值如 `"1,2,3"`：
SELECT id, name, dept_id
FROM employees
WHERE dept_id IN (#{$deptIds})
ORDER BY dept_id, name
3.3 动态 IN 列表（数组参数）
报表参数 `statuses`，值为数组 `['待审核', '审核中']`：
SELECT id, order_no, status
FROM orders
WHERE status IN (#{
$statuses.map(s => "'" + s + "'").join(',')
})
ORDER BY create_time DESC
3.4 NOT IN 条件
报表参数 `excludeIds`（逗号分隔的排除 ID）：
SELECT id, name
FROM users
WHERE id NOT IN (#{$excludeIds})
AND enabled = 1
四、多表关联查询
4.1 INNER JOIN（内连接）
SELECT
o.id,
o.order_no,
o.amount,
c.name AS customer_name,
c.phone AS customer_phone
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.create_time >= $startDate
AND o.create_time < $endDate
ORDER BY o.create_time DESC
4.2 LEFT JOIN（左连接，含空值处理）
查询员工及其部门信息（员工可无部门）：
SELECT
e.id,
e.name,
e.salary,
COALESCE(d.name, '未分配') AS dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
ORDER BY dept_name, e.name
4.3 三表关联
查询订单明细（订单 → 商品 → 分类）：
SELECT
o.order_no,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS subtotal,
p.name AS product_name,
c.name AS category_name
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN categories c ON p.category_id = c.id
WHERE o.id = $orderId
ORDER BY oi.id
4.4 自关联（上下级关系）
查询员工及其直属上级：
SELECT
e.id,
e.name AS emp_name,
e.dept_id,
m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
WHERE e.dept_id = $dept
ORDER BY e.name
五、分组聚合统计
5.1 简单分组统计
按部门统计员工数和平均薪资：
SELECT
d.name AS dept_name,
COUNT(e.id) AS emp_count,
AVG(e.salary) AS avg_salary,
MAX(e.salary) AS max_salary,
MIN(e.salary) AS min_salary,
SUM(e.salary) AS total_salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY total_salary DESC
5.2 带 HAVING 过滤
报表参数 `minCount`，只显示员工数超过阈值的部门：
SELECT
dept_id,
dept_name,
COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id, dept_name
HAVING COUNT(*) >= $minCount
ORDER BY emp_count DESC
5.3 按月统计销售额
报表参数 `year`：
SELECT
MONTH(order_date) AS month_no,
COUNT(*) AS order_count,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount
FROM orders
WHERE YEAR(order_date) = $year
AND status = 'completed'
GROUP BY MONTH(order_date)
ORDER BY month_no
5.4 多维分组（部门 × 月份）
SELECT
d.name AS dept_name,
MONTH(o.create_time) AS month_no,
SUM(o.amount) AS total_amount,
COUNT(o.id) AS order_count
FROM orders o
INNER JOIN employees e ON o.salesperson_id = e.id
INNER JOIN departments d ON e.dept_id = d.id
WHERE YEAR(o.create_time) = $year
GROUP BY d.id, d.name, MONTH(o.create_time)
ORDER BY dept_name, month_no
5.5 ROLLUP 小计汇总（MySQL / SQL Server）
SELECT
COALESCE(dept_name, '合计') AS dept_name,
SUM(salary) AS total_salary,
COUNT(*) AS emp_count
FROM employees
GROUP BY dept_name WITH ROLLUP
ORDER BY dept_name
六、子查询
6.1 WHERE 子查询
查询薪资高于部门平均水平的员工：
SELECT e.id, e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE dept_id = e.dept_id
)
ORDER BY e.dept_id, e.salary DESC
6.2 FROM 子查询（派生表）
先汇总再筛选：
SELECT dept_name, total_amount
FROM (
SELECT
d.name AS dept_name,
SUM(o.amount) AS total_amount
FROM orders o
INNER JOIN employees e ON o.salesperson_id = e.id
INNER JOIN departments d ON e.dept_id = d.id
WHERE YEAR(o.create_time) = $year
GROUP BY d.id, d.name
) t
WHERE t.total_amount > $minAmount
ORDER BY total_amount DESC
6.3 EXISTS 子查询
查询有下单记录的客户：
SELECT id, name, phone, email
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.create_time >= $startDate
)
ORDER BY name
6.4 IN 子查询
查询指定分类下的所有商品：
SELECT p.id, p.name, p.price, p.stock
FROM products p
WHERE p.category_id IN (
SELECT id
FROM categories
WHERE parent_id = $categoryId
)
ORDER BY p.name
七、动态排序
7.1 动态排序字段
报表参数 `sortField`（排序字段名）、`sortDir`（`ASC` 或 `DESC`）：
SELECT id, name, salary, hire_date, dept_id
FROM employees
WHERE dept_id = $dept
ORDER BY #{$sortField} #{$sortDir}
7.2 带默认值的动态排序
SELECT id, name, salary, hire_date, dept_id
FROM employees
ORDER BY #{$sortField != null && $sortField != '' ? $sortField : 'hire_date'}
#{$sortDir != null && $sortDir != '' ? $sortDir : 'DESC'}
7.3 限定允许排序字段（防止 SQL 注入）
SELECT id, name, salary, hire_date, dept_id
FROM employees
ORDER BY #{
var allowedFields = ['name', 'salary', 'hire_date', 'dept_id'];
allowedFields.contains($sortField) ? $sortField : 'hire_date'
} #{$sortDir == 'ASC' ? 'ASC' : 'DESC'}
八、动态表名 / 列名
注意：需做白名单校验，仅允许已知安全的值
8.1 动态表名（按年分表）
报表参数 `year`，数据按年存入 `orders_2024`、`orders_2025` 等表：
SELECT id, order_no, amount, create_time
FROM #{
var y = $year != null ? $year : year(now());
'orders_' + y
}
WHERE status = 'completed'
ORDER BY create_time DESC
8.2 动态列名（按数据类型切换字段）
报表参数 `valueType`，值为 `'actual'` 或 `'budget'`：
SELECT
dept_name,
#{$valueType == 'actual' ? 'actual_amount' : 'budget_amount'} AS amount
FROM dept_budget
WHERE year = $year
ORDER BY dept_name
九、表达式参数计算
9.1 日期计算（查询上个月数据）
SELECT id, order_no, amount
FROM orders
WHERE create_time >= ${formatDate(addMonths(now(), -1), 'yyyy-MM-01')}
AND create_time < ${formatDate(now(), 'yyyy-MM-01')}
9.2 字符串拼接处理（自动拼接通配符）
报表参数 `keyword`：
SELECT id, name, description
FROM products
WHERE name LIKE ${'%' + $keyword + '%'}
OR description LIKE ${'%' + $keyword + '%'}
9.3 数值计算参数
报表参数 `baseRate`（基准税率），查询税后金额超过阈值的订单：
SELECT id, order_no, amount, amount * ${1 + $baseRate / 100} AS tax_amount
FROM orders
WHERE amount * ${1 + $baseRate / 100} >= $threshold
ORDER BY tax_amount DESC
9.4 条件参数（按角色过滤）
报表参数 `role`（`admin` 查全部）、`userId`：
SELECT id, title, amount, applicant_id, status
FROM expense_reports
WHERE #{$role == 'admin' ? '1 = 1' : 'applicant_id = $userId'}
AND status = $status
ORDER BY create_time DESC
十、整体 SQL 脚本生成
SQL 字段整体被 `${}` 包裹时，内部作为脚本执行返回最终 SQL
10.1 根据参数选择不同 SQL
${
if ($reportType == 'detail') {
return "SELECT id, order_no, customer_name, amount, status, create_time FROM orders ORDER BY create_time DESC"
} else {
return "SELECT MONTH(create_time) AS month_no, SUM(amount) AS total, COUNT(*) AS count FROM orders GROUP BY MONTH(create_time) ORDER BY month_no"
}
}
10.2 动态拼接 SELECT 字段
报表参数 `includePhone`（是否包含电话字段）：
${
var fields = "id, name, email, dept_id";
if ($includePhone == true) {
fields = fields + ", phone";
}
return "SELECT " + fields + " FROM employees ORDER BY name";
}
10.3 多数据源路由
报表参数 `region`，不同地区查不同表：
${
var tableMap = {"east": "sales_east", "west": "sales_west", "north": "sales_north"};
var tableName = tableMap[$region] != null ? tableMap[$region] : "sales_all";
return "SELECT dept, SUM(amount) AS total FROM " + tableName + " WHERE year = ${$year} GROUP BY dept ORDER BY total DESC";
}
十一、存储过程调用
格式：`{call 过程名(参数...)}`，输出参数用 `参数名:类型` 标注
11.1 无输出参数的存储过程
{call sp_sync_report_data($startDate, $endDate)}
11.2 带数字输出参数
输出参数 `totalCount`，类型为 `Integer`（类型编号 `4`）：
{call sp_get_order_summary($year, $month, totalCount:4)}
11.3 带游标输出参数（Oracle / PostgreSQL）
{call sp_get_dept_employees($deptId, result:cursor)}
11.4 可选输出参数
{call sp_generate_report($reportId, $format, [fileSize:4])}
11.5 多输出参数
{call sp_calc_statistics($startDate, $endDate, totalCount:4, totalAmount:2, avgAmount:2)}
十二、分页查询
12.1 MySQL 分页
报表参数 `pageNo`（页码从1开始）、`pageSize`：
SELECT id, order_no, amount, status, create_time
FROM orders
WHERE status = $status
ORDER BY create_time DESC
LIMIT ${($pageNo - 1) * $pageSize}, ${$pageSize}
12.2 SQL Server 分页（OFFSET FETCH）
SELECT id, order_no, amount, status, create_time
FROM orders
WHERE status = $status
ORDER BY create_time DESC
OFFSET ${($pageNo - 1) * $pageSize} ROWS
FETCH NEXT ${$pageSize} ROWS ONLY
12.3 Oracle 分页
SELECT *
FROM (
SELECT t.*, ROWNUM AS rn
FROM (
SELECT id, order_no, amount, status, create_time
FROM orders
WHERE status = $status
ORDER BY create_time DESC
) t
WHERE ROWNUM <= ${$pageNo * $pageSize}
)
WHERE rn > ${($pageNo - 1) * $pageSize}
十三、综合示例
13.1 销售业绩报表
参数：`year`（年份）、`dept`（部门，可选）、`minAmount`（最低金额，可选）：
SELECT
e.name AS salesperson,
d.name AS dept_name,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_amount,
AVG(o.amount) AS avg_amount,
MAX(o.amount) AS max_order
FROM orders o
INNER JOIN employees e ON o.salesperson_id = e.id
INNER JOIN departments d ON e.dept_id = d.id
WHERE YEAR(o.create_time) = $year
AND o.status = 'completed'
#{$dept != null && $dept != '' ? 'AND e.dept_id = $dept' : ''}
#{$minAmount != null ? 'AND o.amount >= $minAmount' : ''}
GROUP BY e.id, e.name, d.id, d.name
HAVING SUM(o.amount) > 0
ORDER BY total_amount DESC
13.2 库存预警报表
无参数，自动计算预警状态：
SELECT
p.id,
p.code AS product_code,
p.name AS product_name,
c.name AS category_name,
p.stock,
p.min_stock,
p.stock - p.min_stock AS stock_gap,
CASE
WHEN p.stock = 0 THEN '已断货'
WHEN p.stock < p.min_stock THEN '库存不足'
WHEN p.stock < p.min_stock * 2 THEN '库存偏低'
ELSE '库存正常'
END AS stock_status
FROM products p
INNER JOIN categories c ON p.category_id = c.id
WHERE p.enabled = 1
ORDER BY stock_gap ASC, p.name
13.3 应收账款账龄分析
参数 `asOfDate`（统计基准日）：
SELECT
c.name AS customer_name,
i.invoice_no,
i.amount,
i.due_date,
DATEDIFF($asOfDate, i.due_date) AS overdue_days,
CASE
WHEN DATEDIFF($asOfDate, i.due_date) <= 0 THEN '未到期'
WHEN DATEDIFF($asOfDate, i.due_date) <= 30 THEN '逾期1-30天'
WHEN DATEDIFF($asOfDate, i.due_date) <= 60 THEN '逾期31-60天'
WHEN DATEDIFF($asOfDate, i.due_date) <= 90 THEN '逾期61-90天'
ELSE '逾期90天以上'
END AS aging_bucket
FROM invoices i
INNER JOIN customers c ON i.customer_id = c.id
WHERE i.status = 'unpaid'
ORDER BY overdue_days DESC, c.name
13.4 交叉统计（各部门各月销售额）
参数 `year`（年份）：
SELECT
d.name AS dept_name,
SUM(CASE WHEN MONTH(o.create_time) = 1 THEN o.amount ELSE 0 END) AS jan,
SUM(CASE WHEN MONTH(o.create_time) = 2 THEN o.amount ELSE 0 END) AS feb,
SUM(CASE WHEN MONTH(o.create_time) = 3 THEN o.amount ELSE 0 END) AS mar,
SUM(CASE WHEN MONTH(o.create_time) = 4 THEN o.amount ELSE 0 END) AS apr,
SUM(CASE WHEN MONTH(o.create_time) = 5 THEN o.amount ELSE 0 END) AS may,
SUM(CASE WHEN MONTH(o.create_time) = 6 THEN o.amount ELSE 0 END) AS jun,
SUM(CASE WHEN MONTH(o.create_time) = 7 THEN o.amount ELSE 0 END) AS jul,
SUM(CASE WHEN MONTH(o.create_time) = 8 THEN o.amount ELSE 0 END) AS aug,
SUM(CASE WHEN MONTH(o.create_time) = 9 THEN o.amount ELSE 0 END) AS sep,
SUM(CASE WHEN MONTH(o.create_time) = 10 THEN o.amount ELSE 0 END) AS oct,
SUM(CASE WHEN MONTH(o.create_time) = 11 THEN o.amount ELSE 0 END) AS nov,
SUM(CASE WHEN MONTH(o.create_time) = 12 THEN o.amount ELSE 0 END) AS dec_,
SUM(o.amount) AS total
FROM orders o
INNER JOIN employees e ON o.salesperson_id = e.id
INNER JOIN departments d ON e.dept_id = d.id
WHERE YEAR(o.create_time) = $year
AND o.status = 'completed'
GROUP BY d.id, d.name
ORDER BY total DESC
13.5 动态多条件 + 动态排序完整示例
参数：`keyword`（可选）、`status`（可选）、`startDate`（可选）、`sortField`、`sortDir`：
SELECT
id, order_no, customer_name, amount, status, create_time
FROM orders
WHERE 1 = 1
#{$keyword != null && $keyword != '' ? "AND (order_no LIKE ${'%' + $keyword + '%'} OR customer_name LIKE ${'%' + $keyword + '%'})" : ''}
#{$status != null && $status != '' ? 'AND status = $status' : ''}
#{$startDate != null ? 'AND create_time >= $startDate' : ''}
ORDER BY
#{
var allowed = ['order_no','amount','create_time','status'];
allowed.contains($sortField) ? $sortField : 'create_time'
}
#{$sortDir == 'ASC' ? 'ASC' : 'DESC'}
