SQL列对比取值及产品状态赋值求助：孤儿产品无法设为0

Hey Jason, let's walk through this together—since you're new to SQL, I'll keep things clear and step-by-step so you can follow along easily. It sounds like the core issue is that your logic for counting Parent PLUs is accidentally grouping Orphan products into another category, so let's fix that.

First, let's align on the definitions (this is critical for getting the logic right):

• Parent (1): A product that has at least one child product linked to it
• Child (2): A product that has a parent product linked to it
• Orphan (0): A product that has no parent and no children

Step 1: Capture Parent/Child Relationships #

Let's start with a common table expression (CTE) to gather two key details for each product:

1. How many child products it has
2. Whether it has a parent product

Assuming your product table is named products with columns plu (your unique product ID) and parent_plu (the parent product's ID, NULL if no parent), here's the code:

WITH product_relations AS (
    SELECT
        plu,
        parent_plu,
        -- Count how many children this product has
        (SELECT COUNT(*) FROM products p2 WHERE p2.parent_plu = p1.plu) AS child_count
    FROM products p1
)

Step 2: Assign Status with a CASE Statement #

Now we'll use a CASE statement to map each product to its correct status. The key here is to prioritize checking for Parent first, then Child, and finally default to Orphan (since Orphans are the only products that fit neither category):

SELECT
    plu,
    parent_plu,
    CASE
        -- Parent: Has at least one child product
        WHEN child_count > 0 THEN 1
        -- Child: Has a parent (but no children, since we already checked Parent)
        WHEN parent_plu IS NOT NULL THEN 2
        -- Orphan: No children AND no parent
        ELSE 0
    END AS product_status
FROM product_relations;

Why This Fixes Your Orphan Issue #

Chances are your original logic only checked for either parent or child status, but didn't account for the "neither" scenario that defines Orphans. For example, if you were marking any non-Parent product as Child, you'd accidentally include Orphans. This code explicitly checks for products with no parent and no children, assigning them 0 as intended.

Pro Tips for New SQL Users #

• Test in chunks: First run just the CTE (SELECT * FROM product_relations) to verify that child_count and parent_plu values match your expectations. For an Orphan product, you should see child_count = 0 and parent_plu = NULL.
• Handle edge cases: Some products might be both a Parent and a Child (e.g., a product that has its own children but is also a child of another product). The code above marks these as Parent (1) since we prioritize that condition—if you need to adjust this, just reorder the CASE clauses.
• Check for empty strings: If your parent_plu uses empty strings ('') instead of NULL to indicate no parent, update the Child condition to WHEN parent_plu != '' THEN 2.

Verify Orphans Directly #

To double-check that Orphans are correctly assigned 0, run this query:

SELECT *
FROM product_relations
WHERE child_count = 0 AND parent_plu IS NULL;

All these rows should show product_status = 0 in your final result.

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