menu


Подписаться на блог

Social networks

My page on vk.com
My page on Facebook
My page on Google plus
My page on Twitter

Главная - Блог - Сложные запросы SQL с функциями

Задачи по программированию Дата создания: Просмотры: 209

Сложные запросы SQL с функциями

vitaly.lesnih.info


Россия

Описание задачи

Есть таблица с 1 полем, заполненная числами по порядку: {1,2,4,7,8,11..}.
Необходимо написать SQL запрос, который сделает выборку следующего вида (2 столбца): {{3,1},{5,2},...}
Т.е. в первом поле идет число, с которого начинается пропуск, во втором количество пропущенных чисел.

Можете взять SQL запрос, который создаст таблицу test, и запишет данные по условию задачи. Я использовал MySQL для решения данной задачи:


CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `number` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `number`) VALUES
(1, 1),
(2, 2),
(3, 4),
(4, 7),
(5, 8),
(6, 11);

ALTER TABLE `test`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `test`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

Код

А теперь, я ниже написал SQL запрос, который выполняет эту задачу.


drop function if exists GetNextNumber;
drop function if exists GetBetweenNumbersCount;

DELIMITER //

CREATE FUNCTION GetNextNumber(NumberID int) RETURNS int
BEGIN
    DECLARE result INT;
    SET result = IF(GetBetweenNumbersCount(NumberID) = 0,0, (SELECT number FROM `test` WHERE id = NumberID-1 ) + 1 );
    RETURN (result); 
END//

CREATE FUNCTION GetBetweenNumbersCount(NumberID int) RETURNS int
BEGIN
    DECLARE result INT;
    SET result = (SELECT number FROM `test` WHERE id = (NumberID)) -
    (SELECT number FROM `test` WHERE id = (NumberID-1) ) - 1;
    RETURN (result); 
END//

SELECT    
    (GetNextNumber(id)) AS NextNumber, 
    (GetBetweenNumbersCount(id)) AS BetweenCount
    FROM `test` 
    WHERE GetBetweenNumbersCount(id) != 0;

Результат работы программы

NextNumber    BetweenCount
3                     1
5                     2
9                     2

Описание работы программы

Теперь разберем SQL запрос.

1. Как мы видим, в запросе есть несколько основных блоков:
- В начале, идет блок очищения функции, начинается с ключевого слова drop,
- DELIMITER // - определяет завершение команды
- Блок создания функции CREATE FUNCTION GetNextNumber,
- После этого блок создания функции CREATE FUNCTION GetBetweenNumbersCount,
- И в самом конце идет запрос SELECT, который обращается уже к этим функциям.
Теперь рассмотрим детальнее логику созданной программы.

2. И так, давайте посмотрим на сам запрос SELECT, запрос обращается к таблице и получает записи, в двух колонках: NextNumber, BetweenCount. , а также стоит условие WHERE, то есть условие выдает нам только те результаты, которые при обращение к фукнции GetBetweenNumbersCount(id) != 0, не будут равны нулю. Это условие нам необходимо в связи с тем что бы убрать результаты где нет пропуска чисел, и оставить только те, где есть пропуски.

3. Теперь рассмотрим как работает первая функция GetBetweenNumbersCount:
GetBetweenNumbersCount получает в параметрах колонку таблицы ID, который передается от самого запроса SELECT.
Далее, внутри этой функции сделаем запрос на проверку сколько у нас пропущено чисел:
(SELECT number FROM `test` WHERE id = (NumberID)) -
(SELECT number FROM `test` WHERE id = (NumberID-1) ) - 1;
Вот такой запрос внутри самой функции позволит нам понять сколько пропущено чисел между текущем числом и предидущим числом.
И это значение мы установим в переменной SET result , далее это значение уходит в сам запрос SELECT в колонку BetweenCount

4. Теперь рассмотрим как работает первая функция GetNextNumber:
GetBetweenNumbersCount получает в параметрах колонку таблицы ID, который передается от самого запроса SELECT.
Внутри функции мы ставим условие IF, которое проверяет были ли пропуски и обращается к GetBetweenNumbersCount(NumberID) = 0, если ответ 0, то значит пропусков не было, в SELECT уйдет значение 0 в колонку NextNumber, но так как стоит ограничение на WHERE, которое удаляет строки, в которых нет пропусков, то соотвественно эти результаты не будут показаны
если пропуски были то делает запрос к предидущему числу и добавляет единицу, и возврящает уже это значение, а SELECT выводит результат в колонке NextNumber

Таким образом, мы рассмотрели как выполнять подобные запросы SQL с функциями.

Сложные запросы SQL с функциями
Rating: 3.67 | Тotal votes: 3 | Please, rate this article




Комментарии:









    Подписаться на блог


    Полезные статьи:


    Редирект на Apache Tomcat - htaccess

    Редирект на Apache Tomcat - htaccess
    Тема: Программирование

    Теги: java,jsp,сервер,apache tomcat

    Просмотры:387

    Mysql Java - INSERT - добавить данные в Mysql

    Mysql Java - INSERT - добавить данные в Mysql
    Тема: Программирование

    Теги: database,java,mysql,insert

    Просмотры:244

    Mysql Java - UPDATE - обновить данные в Mysql

    Mysql Java - UPDATE - обновить данные в Mysql
    Тема: Программирование

    Теги: database,java,mysql,update

    Просмотры:295