写一个sql 根据 传递过来的参数年份和 memberId 去找对应的三张订单表里面获取订单数据 并根据这个查询结果按照每个月进行划分 需要有总的订单金额 总的订单数 还有获得每个订单表的订单金额进行回显 java 数据库为postgresql
时间: 2024-03-13 07:48:21 浏览: 20
好的,以下是示例 SQL 语句:
```sql
WITH all_orders AS (
SELECT
order_date,
order_amount,
'order_table_1' AS order_table_name
FROM
order_table_1
WHERE
member_id = ? AND EXTRACT(YEAR FROM order_date) = ?
UNION ALL
SELECT
order_date,
order_amount,
'order_table_2' AS order_table_name
FROM
order_table_2
WHERE
member_id = ? AND EXTRACT(YEAR FROM order_date) = ?
UNION ALL
SELECT
order_date,
order_amount,
'order_table_3' AS order_table_name
FROM
order_table_3
WHERE
member_id = ? AND EXTRACT(YEAR FROM order_date) = ?
)
SELECT
EXTRACT(MONTH FROM order_date) AS month,
SUM(order_amount) AS total_order_amount,
COUNT(*) AS total_order_count,
SUM(CASE WHEN order_table_name = 'order_table_1' THEN order_amount ELSE 0 END) AS order_table_1_amount,
SUM(CASE WHEN order_table_name = 'order_table_2' THEN order_amount ELSE 0 END) AS order_table_2_amount,
SUM(CASE WHEN order_table_name = 'order_table_3' THEN order_amount ELSE 0 END) AS order_table_3_amount
FROM
all_orders
GROUP BY
month
ORDER BY
month;
```
其中,`?` 是需要替换成实际的参数值的占位符,每个问号代表一个参数。这个 SQL 语句会根据传递过来的年份和会员 ID,从三张订单表中获取对应的订单数据,并按照每个月进行汇总,返回每个月的总订单金额和总订单数,以及每个订单表的订单金额。在 SQL 语句中,使用了 Common Table Expression (CTE) 将三张表的数据合并在一起,方便后面的统计。