mysql postgresql with 案例

场景假设

假设我们有两张表：

1. sales 表：记录了每个产品的销售数量
   • product_id (产品ID)
   • quantity (销售数量)
2. products 表：记录了每个产品的详细信息
   • product_id (产品ID)
   • product_name (产品名称)

我们的目标是计算每个产品销售数量占总销售量的百分比。

• ROUND(expression, 2) 同样用于将计算结果四舍五入到小数点后两位。
• (ps.product_quantity / ts.total_quantity) 用于计算百分比，结果会经过 ROUND 函数处理，保留两位小数。

• 在 PostgreSQL 和 MySQL 中，ROUND(expression, 2) 都可以确保结果精确到小数点后两位。
• ROUND 函数是标准的 SQL 函数，用于控制数字的精度

PG:

WITH total_sales AS (
SELECT SUM(quantity) AS total_quantity
FROM sales
),
product_sales AS (
SELECT p.product_id, p.product_name, SUM(s.quantity) AS product_quantity
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_id, p.product_name
)
SELECT ps.product_name,
ps.product_quantity,
ts.total_quantity,
ROUND((ps.product_quantity::float / ts.total_quantity) * 100, 2) AS percentage
FROM product_sales ps, total_sales ts;

MYSQL:

WITH total_sales AS (
SELECT SUM(quantity) AS total_quantity
FROM sales
),
product_sales AS (
SELECT p.product_id, p.product_name, SUM(s.quantity) AS product_quantity
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_id, p.product_name
)
SELECT ps.product_name,
ps.product_quantity,
ts.total_quantity,
ROUND((ps.product_quantity / ts.total_quantity) * 100, 2) AS percentage
FROM product_sales ps, total_sales ts;