Understanding Extracting Records with Same Value and Non-Continuous Id in a Column

Suppose you have the following users table:

I would like to extract records with the same name and no consecutive id from this table.
The expected results are as follows:

I would appreciate it if you could provide me with a query and a way of thinking for that purpose.

sql

2023-02-06 07:15

2 Answers

"If you change the expression ""I want to extract records that do not have consecutive id"" more specifically, you will find out."
In other words, the extraction condition should be あるFor a record, there is no record with a name matching id+1 and no record with a name matching id-1 と.
If you just write it as SQL (the table name is t),

SELECT id, name
FROM tast0
WHERE
  -- No record exists with name matching id+1
  NOT EXISTS (SELECT* FROM past1 WHERE t0.id+1=t1.id AND t0.name=t1.name)
AND 
  -- No record exists with name matching id-1
  NOT EXISTS (SELECT* FROM past2 WHERE t0.id-1 = t2.id AND t0.name = t2.name)

If you want to be a little more efficient

SELECT id, name
FROM tast0
WHERE NOT EXISTS (
    SELECT*
    FROM tast1
    WHERE(t0.id+1=t1.id OR t0.id-1=t1.id)AND t0.name=t1.name
)

2023-02-06 08:20

With MySQL 8.0 or later, you can use Common Table Expressions (CTE) and window functions to write as follows:

 WITH users_grouped AS(
    SELECT id, LAG(id) OVER w ASrb, LEAD(id) OVER w ASrn, name
    FROM users
    WINDOW WAS (Partition By name ORDER BY ID)
)
SELECT id, name FROM users_grouped WHERE id-rb!=1 OR rn-id!=1;

View on DB Fiddle

2023-02-06 08:54

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656