SQL查询需求：根据SpecimenGroup值处理SpecimenSite重复数据显示

Got it, let's build this query step by step to match your exact requirements:

Step 1: Core Logic - Filter SpecimenSite Based on SpecimenGroup #

First, we need to group records by SpecimenSite and check which groups qualify for inclusion. We'll use aggregate functions to detect if each site has "Data", "Other", or both in its SpecimenGroup values:

WITH SiteGroupValidation AS (
    SELECT
        SpecimenSite,
        -- Flag if the site has any "Data" group entry
        MAX(CASE WHEN SpecimenGroup = 'Data' THEN 1 ELSE 0 END) AS HasData,
        -- Flag if the site has any "Other" group entry
        MAX(CASE WHEN SpecimenGroup = 'Other' THEN 1 ELSE 0 END) AS HasOther
    FROM YourTableName
    GROUP BY SpecimenSite
)
SELECT
    SpecimenSite,
    'Other' AS Annotation
FROM SiteGroupValidation
-- Keep only sites that have "Other" but NO "Data" groups
WHERE HasOther = 1 AND HasData = 0

Breakdown: #

• The CTE SiteGroupValidation creates a summary for each SpecimenSite, marking whether it contains "Data" or "Other" groups.
• The main query filters out any site that has both groups, and retains only those with exclusively "Other", adding your required annotation.

Step 2: Handle Edge Case - No Records with A = 'SAMPLE' #

Next, we'll add logic to display your specified format when there are no records where A = 'SAMPLE'. We'll use EXISTS to check this condition and combine results with UNION ALL:

WITH SiteGroupValidation AS (
    SELECT
        SpecimenSite,
        MAX(CASE WHEN SpecimenGroup = 'Data' THEN 1 ELSE 0 END) AS HasData,
        MAX(CASE WHEN SpecimenGroup = 'Other' THEN 1 ELSE 0 END) AS HasOther
    FROM YourTableName
    GROUP BY SpecimenSite
),
ValidSites AS (
    SELECT
        SpecimenSite,
        'Other' AS Annotation
    FROM SiteGroupValidation
    WHERE HasOther = 1 AND HasData = 0
)
SELECT * FROM ValidSites
UNION ALL
-- Replace this with your exact "specified format" if needed
SELECT
    'No valid sites (no records with A = SAMPLE)' AS SpecimenSite,
    'N/A' AS Annotation
WHERE NOT EXISTS (SELECT 1 FROM YourTableName WHERE A = 'SAMPLE')

Quick Notes: #

• Swap YourTableName with your actual table name everywhere in the query.
• Adjust the string values in the final SELECT block to match your exact "specified format" for the edge case.

内容的提问来源于stack exchange，提问作者stefan edwin Prasanth