SQL Server How to filter out rows based on other rows in the same table

67up9zun 于 2023-11-16 发布在其他

关注(0)|答案(2)|浏览(104)

CREATE TABLE YourTableName 
(
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    Job VARCHAR(255),
    Action VARCHAR(MAX)
);

INSERT INTO YourTableName (Job, Action)
VALUES
    ('job1', '1E2B7D03-955E-46B0-BE35-0506C01CF68D/job1'),
    ('job1', '2CE87455-0AC5-4F5A-B0EA-66D8279E81D1/job1'),
    ('job1', '3CE87455-0AC5-4F5A-B0EA-66D8279E81D1/job1');
    
INSERT INTO YourTableName (Job, Action)
VALUES
    ('job2','1E2B7D03-955E-46B0-BE35-0506C01CF68D/job2'),
    ('job2','4E2B7D03-955E-46B0-BE35-0506C01CF68D/job2');

I need to get all the rows with job = 'job1' but if there is a row with job2 and the same Id inside the action then exclude the job from the result.

So the result for the values that are inserted will be:

('job1', '2CE87455-0AC5-4F5A-B0EA-66D8279E81D1/job1')
('job1', '3CE87455-0AC5-4F5A-B0EA-66D8279E81D1/job1')

because for the first job in list there is a job2 with the same Id in the Action column

I was trying to use a subquery but it either filters all out or nothing

SELECT *
FROM YourTableName j1
WHERE Job = 'job1'
  AND NOT EXISTS (SELECT 1
                  FROM YourTableName j2
                  WHERE Job = 'job2'
                    AND SUBSTRING(j1.Action, CHARINDEX('/', j1.Action) + 1, 36) = SUBSTRING(j2.Action, CHARINDEX('/', j2.Action) + 1, 36)
);

sql-server

来源：https://stackoverflow.com/questions/77375035/how-to-filter-out-rows-based-on-other-rows-in-the-same-table

2条答案

按热度按时间

lpwwtiir1#

Just concerning your existing query, you can easily fix it because a GUID string is always 36 characters:

select Id, Job, Action
from YourTableName j1
where Job = 'job1' and not exists (
  select 1 from YourTableName j2
  where Job = 'job2' 
    and Left(j2.Action, 36) = Left(j1.Action, 36)
);

Edit

Dealing with your actual data(!), perhaps try the following:

select Id, Job, Action
from dbo.YourTableName t
where Job = 'job1'
and not exists (
  select * from dbo.YourTableName t2
  where t2.Job = 'job2' 
    and t.Action like 
      Concat('%', Substring(t2.Action, CharIndex('/', t2.Action) + 1, 36), '%')
);

赞(0）回复(0）举报 2023-11-16

ycggw6v22#

Like I mention in the comments, if you fix your design this is much easier. Your column, Action, shouldn't have the job name at the end; remove it. Then you can easily use a NOT EXISTS :

CREATE TABLE YourTableName (
    Id uniqueidentifier PRIMARY KEY DEFAULT NEWID(),
    Job varchar(255),
    Action uniqueidentifier); --Fixed this

INSERT INTO YourTableName (Job, Action)
VALUES
    ('job1', '1E2B7D03-955E-46B0-BE35-0506C01CF68D'), --Fixed values
    ('job1', '2CE87455-0AC5-4F5A-B0EA-66D8279E81D1'),
    ('job1', '3CE87455-0AC5-4F5A-B0EA-66D8279E81D1');
    
INSERT INTO YourTableName (Job, Action)
VALUES
    ('job2','1E2B7D03-955E-46B0-BE35-0506C01CF68D'),
    ('job2','4E2B7D03-955E-46B0-BE35-0506C01CF68D');

GO

SELECT Id,
       Job,
       Action
FROM dbo.YourTableName YTN
WHERE Job = 'job1'
  AND NOT EXISTS (SELECT 1
                  FROM dbo.YourTableName E
                  WHERE E.Job = 'job2'
                    AND E.[Action] = YTN.[Action])

GO
DROP TABLE dbo.YourTableName;

db<>fiddle

If the difficulty you're having is fixing your design, you could do the following to fix the column Action :

UPDATE dbo.YourTableName
SET Action = LEFT(Action,CHARINDEX('/',Action+'/'));

ALTER TABLE dbo.YourTableName ALTER COLUMN Action uniqueidentifier;

db<>fiddle

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server How to filter out rows based on other rows in the same table

2条答案

相关问题

热门标签

最新问答