Skip to content

drandin/SQL-tasks

BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

6 Commits
.gitignore
.gitignore
 
 
readme.md
readme.md
 
 

Repository files navigation

Задачи и решения SQL

1. Подсчёт статистики накопления товаров с использованием пользовательской переменной

На Sqlfiddle http://www.sqlfiddle.com/#!9/49756e/4/2

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `products` (`id`, `name`) VALUES
(1, 'Процессор INTEL Core i5-7500'),
(2, 'Процессор INTEL Core i7-5000'),
(3, 'Оптическая мышь'),
(4, 'Видео карта MV10'),
(5, 'SSD диск Samsung 860 PRO 2 Тб'),
(6, 'SSD диск Samsung 860 PRO 5 Тб'),
(7, 'Материнская плата Supermicro X11DPL-I'),
(8, 'Материнская плата ASUS X99-E-10G WS'),
(9, 'Водяное охлаждение Corsair Hydro Series H115i');
CREATE TABLE IF NOT EXISTS `statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_product` bigint(20) NOT NULL,
  `orders` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `id_product` (`id_product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `statistics` (`id`, `id_product`, `orders`, `date`) VALUES
(1, 1, 1, '2017-09-04 12:52:15'),
(3, 2, 1, '2017-09-04 10:22:44'),
(4, 3, 10, '2017-09-04 12:32:31'),
(5, 4, 2, '2017-09-04 15:24:51'),
(6, 5, 22, '2017-09-04 23:22:15'),
(7, 6, 5, '2017-09-04 11:22:11'),
(8, 7, 26, '2017-09-04 01:22:14'),
(9, 8, 8, '2017-09-04 20:22:12'),
(10, 9, 18, '2017-09-04 19:22:31'),
(11, 1, 1, '2017-09-12 12:22:41'),
(12, 2, 10, '2017-09-12 22:22:21'),
(13, 3, 3, '2017-09-12 10:22:51'),
(14, 4, 5, '2017-09-12 01:22:21'),
(15, 5, 18, '2017-09-12 06:22:31'),
(16, 6, 1, '2017-09-12 14:22:14'),
(17, 7, 12, '2017-09-12 12:22:51'),
(18, 8, 7, '2017-09-12 11:22:11'),
(19, 9, 30, '2017-09-12 18:22:51'),
(20, 1, 2, '2017-09-14 13:22:01'),
(21, 1, 2, '2017-09-15 09:22:02'),
(22, 2, 3, '2017-09-15 02:22:33');

SELECT 
      `id_product`, 
      SUM(`orders`) AS `orders`, 
      DATE(`date`) AS `date` 
FROM `statistics`
WHERE `id_product` = 1 GROUP BY `date`;
SET @count_value_order = 0;
SELECT 
       `products`.`name` AS `name`, 
       (@count_value_order := @count_value_order + `orders`) AS `orders`, 
       `date` 
FROM (
          SELECT 
                 `id_product`, 
                 SUM(`orders`) AS `orders`, 
                 DATE(`date`) AS `date` 
            FROM `statistics` 
           WHERE `id_product` = 3
        GROUP BY `date`) AS `statistics` 
JOIN `products` ON `statistics`.`id_product` = `products`.`id`;

2. Ранжирование и создание блоков данных фиксированного размера

На Sqlfiddle http://www.sqlfiddle.com/#!9/e7c70d/2/1

CREATE TABLE `people` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `people` (`id`, `name`)
VALUES
	(1,'Иванов Иван'),
	(2,'Андрей Петров'),
	(3,'Марина Васильевна'),
	(4,'Фёдор Николаевич'),
	(5,'Дмитрий Игоревич'),
	(6,'Семён Николаевич'),
	(7,'Василий Алибабаевич'),
	(8,'Никита Петрович'),
	(9,'Зинаида Михайловна'),
	(10,'Ольга Николаевна'),
	(11,'Юлия Матвеева');

Ранжирование строк:

SELECT 
	`people`.`id`, 
	`people`.`name`, 
	(SELECT COUNT(*) FROM `people` AS `people_line` WHERE `people`.`id` > `people_line`.`id`) + 1 AS `rank` 
FROM `people`;

Или так:

SELECT 
COUNT(*) AS `rank`,
`p`.`id`,
`p`.`name`
FROM `people` AS `p` JOIN `people` ON `p`.`id` >= `people`.`id`
GROUP BY `p`.`id`, `p`.`name`;

Создание групп из людей. Количество персон в каждой группе 3. Количество групп не фиксированно, установлено требование на количество элементов в группах.

SELECT CEIL(`rank` / 3) AS `group`, `id`, `name`
FROM 
      (SELECT 
          `people`.`id`, 
          `people`.`name`, 
          (SELECT COUNT(*) FROM `people` AS `people_line` WHERE `people`.`id` > `people_line`.`id`) + 1 AS `rank` 
      FROM `people`) AS `table_rank`;
ORDER BY `group`

Создание групп из людей. Количество персон в каждой не опроеделено. Количество групп фиксированно и рано 3 (Обратная задача).

http://www.sqlfiddle.com/#!9/e7c70d/47

SELECT 
MOD(COUNT(*), 3) + 1 AS `group`,
`p`.`id`,
`p`.`name`
FROM `people` AS `p` 
JOIN `people` ON `p`.`id` <= `people`.`id`
GROUP BY `p`.`id`, `p`.`name`
ORDER BY `group` ASC;

3. Сумма из связаных таблиц.

Есть связанные таблицы - Процедуры (1) <--> (n) Лоты (1) <--> (n) Заказчики. Написать запрос, который должен выводить наименование процедуры и суммарный НДС из лотов, суммарный НДС, уплаченый заказчиками.

CREATE TABLE IF NOT EXISTS `procedures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
INSERT INTO `procedures` (`id`, `name`) VALUES
(1, 'Procedure one'),
(2, 'Other procedure');
CREATE TABLE IF NOT EXISTS `lots` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `procedure_id` int(11) NOT NULL,
  `vat` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 ;
INSERT INTO `lots` (`id`, `procedure_id`, `vat`) VALUES
(1, 1, 320),
(2, 1, 100),
(3, 2, 40),
(4, 2, 10);
CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lot_id` int(11) NOT NULL,
  `vat_customer` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 ;
INSERT INTO `customers` (`id`, `lot_id`, `vat_customer`) VALUES
(1, 1, 10),
(2, 1, 35),
(3, 2, 190),
(4, 2, 120),
(5, 3, 1000),
(6, 3, 10),
(7, 4, 50),
(8, 4, 150);
Способ 1

http://www.sqlfiddle.com/#!9/325ada/1

SELECT 
  `p`.`name` AS `name`, 
  `l`.`vat_sum`, 
  `table_vat_customer`.`vat_customer_sum`
FROM `procedures` AS `p` 
LEFT JOIN (
    SELECT 
      `lots`.`id`, 
      `lots`.`procedure_id` AS `procedure_id`,
      SUM(`lots`.`vat`) AS `vat_sum`
      FROM `lots` 
      GROUP BY `lots`.`procedure_id`
) AS `l` ON `l`.`procedure_id` = `p`.`id` 
JOIN (
  SELECT 
      `tbl`.`procedure_id`, 
      SUM(`vat_customer_by_lots`) AS 
      `vat_customer_sum` 
   FROM (
     SELECT `lots`.`procedure_id`, 
     SUM(`customers`.`vat_customer`) AS `vat_customer_by_lots` 
     FROM `lots` LEFT JOIN `customers` ON `lots`.`id` = `customers`.`lot_id` 
     GROUP BY `customers`.`lot_id`) AS `tbl` GROUP BY `tbl`.`procedure_id`
) AS `table_vat_customer` ON `table_vat_customer`.`procedure_id` = `l`.`procedure_id`
Способ 2

http://www.sqlfiddle.com/#!9/325ada/15

SELECT 
`procedures`.`name`,
SUM(`vat`) AS `vat_sum`,  
SUM(`vat_customer_sum`) AS `vat_customer_sum`
FROM (
       SELECT `procedure_id`, `vat`, (
            SELECT SUM(`vat_customer`) 
            FROM `customers` 
            WHERE `customers`.lot_id = `l`.`id`) AS `vat_customer_sum`
        FROM `lots` AS `l`) AS `table_sum`
        JOIN `procedures` ON `procedures`.`id` = `table_sum`.`procedure_id`
GROUP BY `procedure_id`;

4. Поиск пропусков идентификаторов

Написать SQL запрос который выведет все пропуски.

http://sqlfiddle.com/#!9/e8ea82/1635

CREATE TABLE test (
  id INT NOT NULL PRIMARY KEY
);
INSERT INTO test (id) VALUES (1), (2), (3), (6), (8), (9), (12);

Ответ:

SELECT 
`id` AS `id_one`,
      (
        SELECT `test`.`id` 
          FROM `test` 
         WHERE `test`.`id` > `test_interval`.`id` 
      ORDER BY `id` ASC 
         LIMIT 1
      ) AS id_two
FROM `test` AS `test_interval`
HAVING `id_two` - `id_one` > 1;

5. Получение «свежих» значений по каждой валютной паре

В таблице содержатся данные со значениями валютных пар в разные даты. Набор валютных пар ограничен – предположим, что их около 50 штук. Данные в таблице обновляются каждый день, но может случиться так, что в какой-то из дней нет данных по некоторым валютным парам. Необходимо написать запрос, который получит самые «свежие» значения по каждой валютной паре. Если за текущий день значения по какой-то из валютных пар отсутствует, то необходимо выбрать предыдущее значение, и т.д.

http://www.sqlfiddle.com/#!9/037015

CREATE TABLE IF NOT EXISTS `exchangeRates` (
  `id` int(11) NOT NULL auto_increment,
  `symbol` enum('EURUSD','GBPUSD','NZDUSD') NOT NULL,
  `date` date NOT NULL,
  `value` float NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `symbol` (`symbol`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=5 ;
INSERT INTO `exchangeRates` (`id`, `symbol`, `date`, `value`) VALUES
(1, 'EURUSD', '2014-09-18', 1.34),
(2, 'GBPUSD', '2014-09-18', 1.67),
(3, 'EURUSD', '2014-09-17', 1.31),
(4, 'NZDUSD', '2014-09-17', 0.83);
SELECT `exchangeRates`.*
  FROM `exchangeRates` 
 INNER JOIN (
                SELECT 
                       `symbol`, 
                        MAX(`date`) AS `date` 
			      FROM `exchangeRates` 
              GROUP BY `symbol`
            ) AS `uSymbolsDates` 
 USING (`symbol`)
 WHERE `exchangeRates`.`date` = `uSymbolsDates`.`date`;

About

Примеры SQL запросов

Resources

Readme
Activity

Stars

1 star

Watchers

1 watching

Forks

3 forks
Report repository

Releases

No releases published

Packages

No packages published