SQL:在不使用透视的情况下获取多列的差异

在不使用透视（PIVOT）的情况下获取多列的差异，可以通过多种方法实现，具体取决于你想要计算的差异类型。以下是几种常见的情况和解决方案：

1. 计算每行各列之间的差异

假设你有一个表 sales，包含以下列：product, january, february, march。

CREATE TABLE sales (
    product VARCHAR(50),
    january INT,
    february INT,
    march INT
);

如果你想计算每个月销售额之间的差异，可以使用自连接或者子查询。

方法一：使用自连接

SELECT 
    s1.product,
    s1.january - s2.february AS jan_feb_diff,
    s2.february - s3.march AS feb_mar_diff
FROM 
    sales s1
JOIN 
    sales s2 ON s1.product = s2.product
JOIN 
    sales s3 ON s1.product = s3.product;

方法二：使用子查询

SELECT 
    product,
    january - (SELECT february FROM sales WHERE product = s.product) AS jan_feb_diff,
    (SELECT february FROM sales WHERE product = s.product) - (SELECT march FROM sales WHERE product = s.product) AS feb_mar_diff
FROM 
    sales s;

2. 计算每列的总差异

如果你想计算每列的总差异（例如，计算每个月的总销售额与平均销售额的差异），可以使用聚合函数。

SELECT 
    AVG(january) AS avg_january,
    AVG(february) AS avg_february,
    AVG(march) AS avg_march,
    SUM(january) - AVG(january) * COUNT(*) AS jan_total_diff,
    SUM(february) - AVG(february) * COUNT(*) AS feb_total_diff,
    SUM(march) - AVG(march) * COUNT(*) AS mar_total_diff
FROM 
    sales;

3. 使用CASE语句

如果你需要更复杂的逻辑来计算差异，可以使用CASE语句。

SELECT 
    product,
    CASE WHEN january > february THEN january - february ELSE february - january END AS jan_feb_diff,
    CASE WHEN february > march THEN february - march ELSE march - february END AS feb_mar_diff
FROM 
    sales;