通用SQL重复字段筛选及House表built_year字段更新技术问询

Alright, let's break down your problem into two clear steps and solve them with SQL. I'll assume you're working with a standard relational database (like PostgreSQL, MySQL, or SQL Server) since you didn't specify, but I'll note any syntax differences where relevant.

First, we need to identify which (street, street_number) groups have both 0 and non-zero values in built_year. Then we can fetch all records belonging to those groups.

Here's a straightforward approach using a subquery to find the qualifying groups, then joining back to the original table:

SELECT h.*
FROM House h
JOIN (
    SELECT street, street_number
    FROM House
    GROUP BY street, street_number
    HAVING COUNT(DISTINCT CASE WHEN built_year = 0 THEN 0 ELSE 1 END) = 2
) qualifying_groups 
ON h.street = qualifying_groups.street 
AND h.street_number = qualifying_groups.street_number;

How this works: #

• The subquery groups by street and street_number, then uses HAVING to check if the group has both 0 and non-0 values. The CASE statement converts non-0 values to 1, so COUNT(DISTINCT ...) = 2 means both 0 and 1 are present (i.e., both 0 and non-0 built_year values exist in the group).
• We then join this subquery back to the original House table to get all records in those qualifying groups.

Alternatively, if you prefer window functions (great for readability in some cases):

SELECT street, street_number, built_year
FROM (
    SELECT 
        *,
        MAX(CASE WHEN built_year = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY street, street_number) AS has_zero,
        MAX(CASE WHEN built_year != 0 THEN 1 ELSE 0 END) OVER (PARTITION BY street, street_number) AS has_non_zero
    FROM House
) sub
WHERE has_zero = 1 AND has_non_zero = 1;

Before running this update, make sure to back up your data or test it in a staging environment first!

First, we need to get the non-0 built_year value for each qualifying group. I'll assume each group has only one unique non-0 value (if there are multiple, you might need to adjust to use MAX(built_year), MIN(built_year), or another aggregate depending on your needs).

For PostgreSQL/SQL Server: #

WITH group_non_zero_years AS (
    SELECT 
        street, 
        street_number,
        MAX(built_year) AS valid_built_year -- Use MAX/MIN if multiple non-0 values exist
    FROM House
    WHERE built_year != 0
    GROUP BY street, street_number
    -- Only include groups that have both 0 and non-0 values (from step 1)
    INTERSECT
    SELECT street, street_number, NULL
    FROM House
    GROUP BY street, street_number
    HAVING COUNT(DISTINCT CASE WHEN built_year = 0 THEN 0 ELSE 1 END) = 2
)
UPDATE House h
SET built_year = gnzy.valid_built_year
FROM group_non_zero_years gnzy
WHERE h.street = gnzy.street
AND h.street_number = gnzy.street_number
AND h.built_year = 0;

For MySQL: #

MySQL uses a slightly different UPDATE JOIN syntax:

UPDATE House h
JOIN (
    SELECT 
        street, 
        street_number,
        MAX(built_year) AS valid_built_year
    FROM House
    WHERE built_year != 0
    GROUP BY street, street_number
    HAVING EXISTS (
        SELECT 1 FROM House h2
        WHERE h2.street = House.street 
        AND h2.street_number = House.street_number
        AND h2.built_year = 0
    )
) gnzy 
ON h.street = gnzy.street 
AND h.street_number = gnzy.street_number
SET h.built_year = gnzy.valid_built_year
WHERE h.built_year = 0;

How this works: #

• The CTE/subquery gets the valid non-0 built_year for each group that has both 0 and non-0 values (we ensure this with the INTERSECT or EXISTS clause).
• We then update all records in House where built_year is 0, setting it to the valid year from the same group.

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