使用Self Join展示两年成本数据失败，请求技术协助

Hey there! Let's work through getting your self join to correctly display 2016 and 2017 cost data. First, let's start with a typical scenario (since you didn't share your exact table structure or code) — let's assume your cost table looks like this:

-- Example cost table structure
CREATE TABLE costs (
    cost_category VARCHAR(50),
    year INT,
    cost_amount DECIMAL(10,2)
);

Correct Self Join Query #

Here's how to write a self join that pairs each cost category's 2016 and 2017 data side by side:

SELECT 
    c1.cost_category,
    c1.cost_amount AS cost_2016,
    c2.cost_amount AS cost_2017
FROM 
    costs c1
LEFT JOIN 
    costs c2 ON c1.cost_category = c2.cost_category
            AND c1.year = 2016
            AND c2.year = 2017;

Common Mistakes That Cause Unexpected Results #

If your current code isn't working, it's likely one of these issues:

• Missing year filters in the join condition: If you only join on cost_category without specifying c1.year=2016 and c2.year=2017, you'll get cross-matched data (like 2016 paired with all other years, not just 2017).
• Using INNER JOIN instead of LEFT JOIN: An inner join will drop any categories that have data in 2016 but not 2017 (or vice versa). Use LEFT JOIN to keep all categories from the 2016 dataset, even if they don't have 2017 data.
• Incorrect join key: If you're not joining on a shared dimension (like cost_category, product_id, or department), you'll end up with a Cartesian product (all possible combinations of 2016 and 2017 rows), which is definitely not what you want.

Adjust for Your Specific Table #

If your table has different fields (like a product_id instead of cost_category), just swap out the join key to match your actual schema. For example, if your table uses product_id as the shared identifier:

SELECT 
    c1.product_id,
    c1.cost_amount AS cost_2016,
    c2.cost_amount AS cost_2017
FROM 
    costs c1
LEFT JOIN 
    costs c2 ON c1.product_id = c2.product_id
            AND c1.year = 2016
            AND c2.year = 2017;

内容的提问来源于stack exchange，提问作者qing zhangqing