在SQL中删除或保留列中的数据

Question

我有一列数值需要根据以下条件删除或保留：

1. 如果相同的ID拥有以下类型（在同一ID上）：1000、1100、1200、1300和任何其他类型，例如700、800、900等，则需要删除这些类型（1000、1100、1200、1300）

2. 但是，如果ID仅具有类型：1000、1100、1200、1300，而没有其他类型，则保留它们

来源

ID       | TYPE
40208001 | 700
40208001 | 1100
40209017 | 900
40209017 | 800
40209017 | 910
40209017 | 1200
40210494 | 1100
40210494 | 1000
40210494 | 1200
40210551 | 400
40210551 | 1200
40210767 | 1200
40210767 | 800
40211724 | 1300
40211724 | 900
40211724 | 500

WHAT I WANT:

ID       | TYPE
40208001 | 700
40208001 |
40209017 | 900
40209017 | 800
40209017 | 910
40209017 |
40210494 | 1100
40210494 | 1000
40210494 | 1200
40210551 | 400
40210551 |
40210767 |
40210767 | 800
40211724 |
40211724 | 900
40211724 | 500

- Michael

Answer 1

在WHERE子句中使用EXISTS条件来查找这样的行：

UPDATE t
SET TYPE = NULL
WHERE TYPE IN (1000, 1100, 1200, 1300)
AND EXISTS (
    SELECT 1
    FROM t AS x
    WHERE ID = t.ID
    AND TYPE NOT IN (1000, 1100, 1200, 1300)
)

DB Fiddle演示

如果您想要选择数据，您可以将上述内容重写为OUTER APPLY：

SELECT ID, CASE
    WHEN TYPE IN (1000, 1100, 1200, 1300) AND hasother = 'y' THEN NULL
    ELSE TYPE
END AS TYPE
FROM t
OUTER APPLY (
    SELECT TOP 1 'y'
    FROM t AS x
    WHERE ID = t.ID AND TYPE NOT IN (1000, 1100, 1200, 1300)
) OA(hasother)

- Salman A

---

这不是一种灵活的解决方案，因为数值被硬编码了。 - Gaurav

@GauravS，原帖的问题是特别询问基于列表（1000、1100、1200、1300）应考虑哪些值进行NULL处理，因此这怎么是硬编码解决方案呢？ - George Joseph

@GeorgeJoseph。好的，我同意你的看法。撤回我的话。但我认为OP已经发布了示例数据，并因此提到了这些特定的数字。 - Gaurav

Answer 2

你可以使用以下查询来获得结果。

select t1.ID, (case when length(t1.type) > min_l then NULL else t1.type end) type
from test t1
join 
(select ID,length(Min(type)) min_l from test group by ID) t2 
on t1.ID = t2.ID

SQL Fiddle

{{链接1：SQL Fiddle}}

- Gaurav

Answer 3

你也可以使用以下类似的查询。

逻辑是查找一组具有千位数值的ID，然后将其与原始表左连接，并使用条件语句。

查看工作演示

; with idsWithTypeInThousands as 
(
select id from sourcetbl where type in (1000, 1100, 1200, 1300)
group by id 
)

select s.id, case when  i.id is not null and type not in (1000, 1100, 1200, 1300)  then null else type end 
from sourcetbl s left join idsWithTypeInThousands i
on i.id=s.id

- DhruvJoshi

Answer 4

试试这个：

    Declare @ListValues TABLE(Id int, [Type] int)

    INSERT INTO @ListValues
    SELECT 40208001,700  Union All
    SELECT 40208001,1100 Union All
    SELECT 40209017,900  Union All
    SELECT 40209017,800  Union All
    SELECT 40209017,910  Union All
    SELECT 40209017,1200 Union All
    SELECT 40210494,1100 Union All
    SELECT 40210494,1000 Union All
    SELECT 40210494,1200 Union All
    SELECT 40210551,400  Union All
    SELECT 40210551,1200 Union All
    SELECT 40210767,1200 Union All
    SELECT 40210767,800  Union All
    SELECT 40211724,1300 Union All
    SELECT 40211724,900  Union All
    SELECT 40211724,500

    ;with cte
    As
    (

        Select Id
        ,SUM(CASE WHEN Type<1000 THEN 1 ELSE 0 END)As Type1Count
        ,SUM(CASE WHEN Type<1000 THEN 0 ELSE 1 END) As Type2Count
            from @ListValues
            Group by Id
    )

    Update l
    set Type=NULL
    from cte c
    JOIN @ListValues l on c.Id=l.Id
    WHERE Type2Count>0 and Type1Count>0 and l.Type>1000

    Select * from @ListValues order by id

注意：根据您的要求，可以更改类型列上的条件。从您的问题描述中，我推导出了条件（type>1000且type<100）。

- Sahi