如何正确排除特定记录 – SQL Server

huangapple go评论96阅读模式
英文:

How Can I Exclude Specific Records Correctly - SQL Server

问题

ClientId ClientLabels
2 SLP, Texas, SDRC (0-3)
4 OT
5 ABA, SDRC (6+)
英文:

I have a table with Clients and their labels (saved in a single column separated by comma).
I need to find a way to EXCLUDE clients with SDRC label.

CREATE TABLE FakeData
(
    ClientId INT, 
    ClientLabels VARCHAR(MAX)
);

INSERT INTO FakeData
VALUES (1, 'ABA, SDRC, California'),
       (2, 'SLP, Texas, SDRC (0-3)'),
       (3, 'ABA, SDRC (0-3), SDRC, Virginia'),
       (4, 'OT'),
       (5, 'ABA, SDRC (6+)'),
       (6, 'SLP, SDRC, Texas, SDRC (12+)')

SELECT * FROM FakeData
ClientId ClientLabels
1 ABA, SDRC, California
2 SLP, Texas, SDRC (0-3)
3 ABA, SDRC (0-3), SDRC, Virginia
4 OT
5 ABA, SDRC (6+)
6 SLP, SDRC, Texas, SDRC (12+)

The output I need:

ClientId ClientLabels
2 SLP, Texas, SDRC (0-3)
4 OT
5 ABA, SDRC (6+)

I was thinking of using STRING_SPLIT, however it did not work.

SELECT * 
FROM FakeData
WHERE 'SDRC' NOT IN (SELECT VALUE 
                     FROM STRING_SPLIT(FakeData.ClientLabels, ','))

And obviously below code will not work as well.

SELECT * 
FROM FakeData
WHERE FakeData.ClientLabels NOT LIKE '%SDRC%'

答案1

得分: 1

以下是您提供的代码的中文翻译:

如何看待这个:

SELECT *
FROM FakeData
WHERE 'SDRC' NOT IN (SELECT TRIM(VALUE) FROM STRING_SPLIT(FakeData.ClientLabels, ','))


http://sqlfiddle.com/#!18/717fc/3
英文:

How about this:

SELECT * 
FROM FakeData
WHERE 'SDRC' NOT IN (SELECT TRIM(VALUE) FROM STRING_SPLIT(FakeData.ClientLabels, ','))

http://sqlfiddle.com/#!18/717fc/3

答案2

得分: 1

你可以将STRING_SPLIT用作表格:

SELECT ClientId, ClientLabels
  FROM @FakeData
    CROSS APPLY STRING_SPLIT(ClientLabels, ',') s
 GROUP BY ClientId, ClientLabels
 HAVING MAX(CASE WHEN TRIM(value) = 'SDRC' THEN 1 ELSE 0 END) = 0

在进行比较后重新聚合。要更改筛选器,只需在Case语句中添加或删除值。

英文:

You can use STRING_SPLIT as a table:

SELECT ClientId, ClientLabels
  FROM @FakeData
    CROSS APPLY STRING_SPLIT(ClientLabels, ',') s
 GROUP BY ClientId, ClientLabels
 HAVING MAX(CASE WHEN TRIM(value) = 'SDRC' THEN 1 ELSE 0 END) = 0

Re-aggregating it after making your comparisons. To alter the filter just add or remove values in the case statement.

答案3

得分: 1

以下是翻译好的部分:

如果您必须存储这样的数据,最简单的方法是使用类似匹配:

select * 
from FakeData
where Concat(', ', ClientLabels, ', ') not like '%, SDRC, %';
英文:

The simplest way if you must store data like this, would be to use a like match

select * 
from FakeData
where Concat(', ', ClientLabels, ', ') not like '%, SDRC, %';

huangapple
  • 本文由 发表于 2023年6月30日 01:17:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/76583278.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定