Как быстро получить пропущенное в поле значение?

#mysql #sql #запрос

Есть таблица sample. Важный уникальный индекс UNIQUE( laboratory_prefix_id, sample_number
). Последний задаёт группу, в пределах которой не может быть повторяющихся sample_number.
Вопрос следующий - как быстро получить список пропущенных sample_number в пределах
запрошенной группы laboratory_prefix_id? И если не список, то хотя бы 1 наименьшее
значение.

Например, если искать в группе laboratory_prefix_id=5, то максимальный sample_number=15,
но перед ним пропущены значения 11, 12, 13, 14 - их (или хотя бы значение 11) хотелось
бы как-то получить (т.е. получить просто sample number пропущенных значений). Как это
быстро сделать?



Дамп таблицы для примера прикладываю

CREATE TABLE `sample` ( 
    `id` Int( 10 ) UNSIGNED AUTO_INCREMENT NOT NULL,
    `person_id` Int( 10 ) UNSIGNED NOT NULL,
    `laboratory_prefix_id` TinyInt( 3 ) UNSIGNED NOT NULL DEFAULT '1',
    `sample_number` Smallint( 5 ) UNSIGNED NOT NULL,
    `total_cost` Decimal( 6, 2 ) NOT NULL DEFAULT '0.00',
    `completion_date` Date NULL,
    `barcode` VarChar( 15 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    `country_id` TinyInt( 3 ) UNSIGNED NOT NULL DEFAULT '20',
    `sample_priority` TinyInt( 1 ) UNSIGNED NOT NULL DEFAULT '1',
    `sample_note` VarChar( 255 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    `registration_date` Date NOT NULL,
    PRIMARY KEY ( `id` ),
    CONSTRAINT `UK_sample` UNIQUE( `laboratory_prefix_id`, `sample_number` ) )
CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT 'Образцы'
ENGINE = InnoDB
AUTO_INCREMENT = 57;

INSERT INTO `sample`(`id`,`person_id`,`laboratory_prefix_id`,`sample_number`,`total_cost`,`registration_date`,`completion_date`,`barcode`,`country_id`,`sample_priority`,`sample_note`)
VALUES 
( '15', '120', '8', '155', '0.00', '2017-08-18', NULL, '12-55fff', '20', '3', 'sdfsdfsdf' ),
( '22', '120', '7', '1', '0.00', '2017-08-11', NULL, NULL, '20', '1', NULL ),
( '32', '120', '7', '10', '0.00', '2017-08-19', NULL, NULL, '20', '1', NULL ),
( '33', '165', '5', '1', '0.00', '2017-08-19', NULL, NULL, '20', '2', NULL ),
( '34', '165', '5', '2', '0.00', '2017-08-19', NULL, NULL, '20', '2', NULL ),
( '35', '166', '5', '3', '0.00', '2017-08-19', NULL, NULL, '20', '3', NULL ),
( '36', '166', '5', '4', '0.00', '2017-08-19', NULL, NULL, '20', '3', NULL ),
( '37', '167', '5', '5', '0.00', '2017-08-19', NULL, NULL, '20', '2', '6лоло' ),
( '38', '168', '5', '6', '0.00', '2017-08-19', NULL, NULL, '20', '1', NULL ),
( '39', '168', '5', '7', '0.00', '2017-08-19', NULL, NULL, '20', '1', NULL ),
( '40', '168', '5', '8', '0.00', '2017-08-19', NULL, NULL, '20', '1', NULL ),
( '41', '168', '5', '9', '0.00', '2017-08-19', NULL, NULL, '20', '1', NULL ),
( '42', '168', '5', '10', '0.00', '2017-08-19', NULL, NULL, '20', '1', NULL ),
( '43', '173', '7', '2', '0.00', '2017-08-20', NULL, 'к', '17', '2', 'ккк' ),
( '44', '173', '5', '15', '0.00', '2017-08-18', NULL, 'ne', '20', '2', '=' ),
( '47', '180', '8', '5', '0.00', '2017-08-20', NULL, NULL, '20', '2', NULL ),
( '49', '120', '8', '161', '0.00', '2017-08-18', NULL, '12-55fff', '20', '2', 'sdfsdfsdf' ),
( '54', '120', '6', '212', '0.00', '2017-09-01', NULL, NULL, '20', '2', NULL ),
( '55', '120', '6', '213', '0.00', '2017-09-01', NULL, NULL, '20', '3', NULL ),
( '56', '120', '6', '214', '0.00', '2017-09-01', NULL, NULL, '20', '2', NULL );

    

Ответы

Ответ 1

SELECT @count
FROM sample, (select @count := 0) dummy
WHERE sample.laboratory_prefix_id = 5
  AND (@count := @count+1) < sample.sample_number
ORDER BY sample.sample_number ASC
LIMIT 1;


Не работает в случае, если для заданного sample.laboratory_prefix_id нет ни одной записи.

Если начальное значение не равно 1, следует откорректировать как псевдотаблицу dummy,
так и условие во WHERE. Вот более универсальный запрос (соответствует условию типа
"найти первый свободный в группе laboratory_prefix_id = 5, но не менее sample.sample_number
= 4"):

SET @laboratory_prefix_id = 5;
SET @min_sample_number = 4;

SELECT @count
FROM sample, (select @count := @min_sample_number-1) dummy
WHERE sample.laboratory_prefix_id = @laboratory_prefix_id
  AND sample.sample_number > @min_sample_number-1
  AND (@count := @count+1) < sample.sample_number
ORDER BY sample.sample_number ASC
LIMIT 1;


Недостаток - тот же.

Ну и запрос, избавленный от этого недостатка:

SET @laboratory_prefix_id = 5;
SET @min_sample_number = 4;

(
    SELECT @count
    FROM sample, (select @count := @min_sample_number-1) dummy
    WHERE sample.laboratory_prefix_id = @laboratory_prefix_id
      AND sample.sample_number > @min_sample_number-1
      AND (@count := @count+1) < sample.sample_number
    ORDER BY sample.sample_number ASC
    LIMIT 1
)
UNION ALL
(
    SELECT @min_sample_number
)
ORDER BY 1 DESC
LIMIT 1;


Ответ 2

select s1.sample_number+1 
from sample s1 
left join sample s2 on s2.sample_number = s1.sample_number+1 AND s2.laboratory_prefix_id=5
where s1.laboratory_prefix_id=5 and s2.id is null 
order by s1.sample_number limit 1;