Форум АНТИЧАТ - Нетривиальные задачи Sql

Форум АНТИЧАТ (https://forum.antichat.xyz/index.php)

- PHP, PERL, MySQL, JavaScript (https://forum.antichat.xyz/forumdisplay.php?f=37)

- - Нетривиальные задачи Sql (https://forum.antichat.xyz/showthread.php?t=75032)

VDShark

26.06.2008 00:23

Нетривиальные задачи Sql

Предлагаю выкладывать в данной теме интересные, неоднозначные задачки, заставляющие моск пошевилиться :)
Новичкам будет полезно почитать и поучится, а ветеранам в этом нелегком деле - поулучшать и попредлагать свои варианты решения.

Для примера приведу задачку, котороя мне сегодня попалась (друг спросил):

Есть таблица, назовем ее tbl, в ней 2 столбца: st1 и st2. В каждом из столбцов какой то набор букв.

К примеру:

Код:

--------------------

         tbl

--------------------

    st1   |   st2

--------------------

     a    |     e     

     d    |     g     

     r    |     k

Так вот - необходимо НЕ используя UNION получить в результирующей выборке содержимое 2-х столбцов в одном столбце, упорядоченным по возрастанию.

Т.е.

Код:

---------

 result 

---------

   a     

   d

   e

   g

   k       

   r

Запрос вовсе не сложный, но если сразу не догадаться как решать - можно потратить некоторое время на обдумывание :)

Не было - ну да ладн :)

Код:

CREATE TABLE `tst` (

  `st1` varchar(1) NOT NULL,

  `st2` varchar(1) NOT NULL

);



INSERT INTO `tst` (`st1`, `st2`) VALUES

('a', 'e'),

('d', 'g'),

('r', 'k');

.Slip

26.06.2008 00:38

Кидай сразу код для создания таблицы и заполнения колонок. А то буковки впадлу переписывать

Solker

26.06.2008 03:59

Пойдет?

Код:

CREATE TEMPORARY TABLE temp TYPE=HEAP SELECT st1 as 'result' FROM tst;

INSERT INTO temp SELECT st2 FROM tst;

SELECT * FROM temp ORDER BY 'result' ASC;

DROP TABLE temp;

VDShark

26.06.2008 11:25

Цитата:

Сообщение от Solker

Пойдет?

Код:

CREATE TEMPORARY TABLE temp TYPE=HEAP SELECT st1 as 'result' FROM tst;

INSERT INTO temp SELECT st2 FROM tst;

SELECT * FROM temp ORDER BY 'result' ASC;

DROP TABLE temp;

Ну.... Никакого полета фантазии :)
Давай без временных таблиц.
Тем более что это все сделать необходимо одним запросом.

guest3297

26.06.2008 14:58

подзапросы тоже нельзя?

z3r0cool

26.06.2008 15:33

Использовать представления (VIEW), хранимые функции...?
Собственно эти объекты и предназначены для решения проблем такого рода.

VDShark

26.06.2008 16:50

Цитата:

Сообщение от [ cash ]

подзапросы тоже нельзя?

Можно. Хотя я без них решил :)

Цитата:

Сообщение от z3r0cool

Нет-нет-нет и еще раз нет... Тем более в основе представления так же лежит запрос - какая собственно разница? А в хп обычно группа запросов. Я ж написал - ЗАПРОС... 1 запрос. Ну максимум с подзапросами. А вы тут начали уже городить =)

На самом деле запрос достаточно легкий... главное догадаться каким путем идти.

-=lebed=-

26.06.2008 17:21

Цитата:

Сообщение от VDShark

На самом деле запрос достаточно легкий... главное догадаться каким путем идти.

Модифицировать исходную таблицу можно? Создавать дополнительные я так понял нельзя...

VDShark

26.06.2008 17:36

Цитата:

Сообщение от -=lebed=-

Модифицировать исходную таблицу можно? Создавать дополнительные я так понял нельзя...

Нет. Никаких DML и тп... Только запрос на выборку. Т.е. select и присущие ему конструкции: можно подзапросы, JOIN'ы, самосоединения и тп.

Solker

27.06.2008 19:10

На тему большинство забило, я же 3 дня листал мануалы на mysql.com.
Будте добрвы выскажите тогда пожалуйста наиболее оптимальное решение.
Очень интересно.

Naydav

27.06.2008 19:20

Цитата:

Будте добрвы выскажите тогда пожалуйста наиболее оптимальное решение

Рано еще :)

ENFIX

27.06.2008 19:27

Код:

select concat_ws('\n', st1, st2) as result from tst;

так нельзя?)

Solker

27.06.2008 20:05

Цитата:

Сообщение от ENFIX

Код:

select concat_ws('\n', st1, st2) as result from tst;

так нельзя?)

Всеровно получается, c точки зрения SQL, в одной строчке по 2 буквы.

VDShark

27.06.2008 21:06

Цитата:

Сообщение от ENFIX

Код:

select concat_ws('\n', st1, st2) as result from tst;

так нельзя?)

Это даже костылями назвать сложно :)

Да... наверное на задачку забили. Я сам минут 20 фтыкал, когда мне ее задли... сначала хотел сказать "Низя!", но посидел подумал... и вышел на верное направление :) Не исключаю что способ решения не один.
Если уж совсем не в моготу будет - могу подсказку сказать... правда думаю это практически решение :)

Naydav

27.06.2008 21:09

та уже нашли ответ :)

Цитата:

SELECT if( f1 = f2, t2.st1, t2.st2 ) as res
FROM (

SELECT rand( 120 ) AS f1
FROM tst
LIMIT 2
) AS tmp

JOIN (

SELECT rand( 120 ) AS f2

) AS tmp1,
tst as t2 ORDER BY res;

точно наши,
где они уж не наши не бывали...

VDShark

27.06.2008 21:18

Цитата:

Сообщение от Naydav

та уже нашли ответ :)

Да, молодца :) Можно канешн оттачивать само решение - но направление правильное найдено - использовать условия ;)

Naydav

27.06.2008 21:31

Задача:

Цитата:

Схема БД состоит из четырех отношений:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

Отношение Product представляет производителя (maker), номер модели (model) и тип (PC - ПК, Laptop - ПК-блокнот или Printer - принтер). Предполагается, что номера моделей уникальны для всех производителей и типов продуктов. В отношении PC для каждого номера модели, обозначающего ПК, указаны скорость -speed (процессора в мегагерцах), общий объем RAM (в мегабайтах), размер диска -hd (в гигабайтах), скорость считывающего устройства CD (например, 4х) и цена - price. Отношение Laptop аналогично отношению РС за исключением того, что вместо скорости CD содержится размер экрана -screen (в дюймах). В отношении Printer для каждой модели принтера указывается, является ли он цветным - color ('y', если цветной), тип принтера - type (лазерный - Laser, струйный - Jet или матричный - Matrix) и цена

Вопрос:

Цитата:

Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол. Вывести: model, speed и hd

Результат правильного выполнения упражнения:
Например

Цитата:

model speed hd
1232 500 10
1232 450 8
1232 450 10
1260 500 10

Задачи на подобии такой на
http://sql-ex.ru/index.php
Там просто орвет в текстареа пишешь и смотришь резалт
2 этапа : обучающий, сертификационный
вопросов 40 наверно

пс
Там косячит регистрация, но есть кнопка - вход без регистрации

VDShark

27.06.2008 22:00

Ничего нетривиального в данной задаче не вижу :)

Цитата:

Сообщение от Naydav

Там косячит регистрация, но есть кнопка - вход без регистрации

Я сейчас занимаюсь расширением функциональности и поддержкой данного проекта :) Не мог бы ты отписать что там именно косячит? Либо в личку, либо через форму обратной связи.

P.S. Этапа там не два а три.

Red_Red1

27.06.2008 22:23

Извините почти оффтоп...
Может и забили но не все... жаль что неуспел решить :(:(, а то что тема упала, так это потому что нечего было писать так как в наверное были в поисках решения.
VDShark, я так понимаю нашли решение не такое как у тебя, ты писал что решил без подзапросов?
Хотелось бы еще со столь же простым условием и столь же захватывающее по поиску решения :)

Naydav

27.06.2008 22:30

VDShark
Написал в личку

VDShark

27.06.2008 22:57

Цитата:

Сообщение от Red_Red1

VDShark, я так понимаю нашли решение не такое как у тебя, ты писал что решил без подзапросов?

Да... у меня там самосоединение было + условная конструкция =)

VDShark

27.06.2008 23:14

Цитата:

Сообщение от Red_Red1

Хотелось бы еще со столь же простым условием и столь же захватывающее по поиску решения :)

Ну... коль никто не пишет - предложу еще одну задачку. Не такая интересная как предыдущая - но здесь задачка немног другая - максимально упростить решение.
Только что придумал условия - но прием, применяемый в данной задаче, достаточно распространенный.

Вот дамп базы:

Код:

--

-- Структура таблицы `fruits`

--



CREATE TABLE IF NOT EXISTS `fruits` (

  `name` varchar(50) NOT NULL,

  PRIMARY KEY  (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=cp1251;



--

-- Дамп данных таблицы `fruits`

--



INSERT INTO `fruits` (`name`) VALUES

('apple'),

('banana'),

('orange'),

('pear');

Задача следующая: получить все пары значений (т.е. в результирующей выборке будет 2 столбца), подпадающие под след. условия:
1) Одного и того же фрукта в обоих столбцах быть не должно
2) Кортежи, в которых ячейки поменяны местами считаются одинаковыми. Т.е. если у нас есть
------------------
| banana | apple |
------------------
то
------------------
| apple | banana |
------------------
уже быть не должно :)

Ждем максимально компактного решения :)

z3r0cool

30.06.2008 09:05

Вот так!

Код:

SELECT *

        FROM fruits AS f1

                JOIN fruits AS f2 ON f2.name > f1.name

        ORDER BY f1.name

VDShark

30.06.2008 09:34

Цитата:

Сообщение от z3r0cool

Код:

SELECT *

        FROM fruits AS f1

                JOIN fruits AS f2 ON f2.name > f1.name

        ORDER BY f1.name

Да, молодец... В общем правильный подход.
Только

Код:

SELECT *

FROM `fruits` as `f1`,`fruits` as `f2`

WHERE `f1`.`name`>`f2`.`name`

данный код (через декартово произведение) в этом конкретном случае отработает чуточку быстрее (0.0003 сек. против 0.0008 сек).
Хотя с увеличением таблицы эта разница конечно же будет незаметна, а при некоторой оптимизации скорее всего будет обратная ситуация :)

VDShark

19.09.2008 16:32

Помню первая задача из этой темки вызвала ажиотаж в свое время :) Итак, недавно свой взгляд на данную задачу опубликовал один mvp... Думаю тем кто решал данную задачку, будет интересно посмотреть:

"Как объединить данные из двух столбцов в один без использования UNION и JOIN?"

MasterMushi

19.09.2008 19:05

Я так до конца и не понял как красиво решить задачу с компутерными кусками. Хотябы проще возьмем из моей практики. Есть 2 таблицы.

В одной написаны
Id | Id_Получатель | Id_Отправитель | Текст сообщения

Во второй
Id | Имя.

Нужно на выходе получить структуру
Id_сообщения | Имя 1 отправителя | Имя 2 получателя | Текст сообщения

Как сделать такую выборку единым запросом?

VDShark

22.09.2008 12:52

Цитата:

Сообщение от MasterMushi

В твоем приме ничего нетривиального нет :) А для таких вопросов существует спец. топик, с вопросами по sql... Так что просьба не захламлять топик =\

VDShark

09.09.2009 22:40

Давно никто ничего не выкладывал здесь. А жаль. Разминка для мозга полезна ;)
Ну что ж - выложу небольшую задачку. Она достаточно проста, но как говорится на безрыбье... В общем вы поняли =)
На данную задачку меня натолкнул следующий факт: все чащи люди неправильно проектируют структуру БД, а потом страдают.
Возьмем пример:

Код:

login     |     param     |   value

--------------------------------------

Alex      |   City        |   London

Alex      |   RegDate     |   22-04-2009

Alex      |   Email       |   aswer@yahoo.com

Nata      |   City        |   Paris

Nata      |   RegDate     |   15-07-2009

Nata      |   Email       |   dser@mail.com

Налицо безграмотнейшее составление структуры. Отношение не находится даже в 1НФ.
И как следствие у людей начинаются большие проблемы с составлением запросов =)
И начинают они плакаться в соцсетях с просьбами о помощи.
Так вот - нормальную структуру, допустим, выяснили, осталось сконвертить для нее данные.
Структура:

Код:

login | city | regdate | email

Т.е. задача состоит в том, что бы из вышеприведенной таблички одним запросом получить данные в формате, приведенном выше.

Дамп базы (для ленивых, что б не создавать структуру и не писать инсерты):

Код:

CREATE TABLE IF NOT EXISTS `tst` (

  `login` varchar(20) NOT NULL DEFAULT '',

  `param` varchar(10) NOT NULL DEFAULT '',

  `value` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`login`,`param`)

);



INSERT INTO `tst` (`login`, `param`, `value`) VALUES

('Alex', 'City', 'London'),

('Alex', 'Email', 'aswer@yahoo.com'),

('Alex', 'RegDate', '22-04-2009'),

('Nata', 'City', 'Paris'),

('Nata', 'Email', 'dser@mail.com'),

('Nata', 'RegDate', '15-07-2009');

Задача решается не одним способом =) Итак - разомните мозги, отложите на минутку свой "набор кавычек".

oRb	09.09.2009 23:44

Код:

mysql> SELECT t1.login, t1.value as city, t2.value as regdate, t3.value as email FROM `tst` t1

    -> LEFT JOIN `tst` t2 ON t1.login = t2.login AND t2.param='regdate'

    -> LEFT JOIN `tst` t3 ON t1.login = t3.login AND t3.param='email'

    -> GROUP BY t1.login;

+-------+--------+------------+-----------------+

| login | city   | regdate    | email           |

+-------+--------+------------+-----------------+

| Alex  | London | 22-04-2009 | aswer@yahoo.com | 

| Nata  | Paris  | 15-07-2009 | dser@mail.com   | 

+-------+--------+------------+-----------------+

2 rows in set (0,00 sec)

Код:

mysql> SELECT login,

    -> (SELECT `value` FROM `tst` WHERE `login`=t1.login AND `param`='city') as city,

    -> (SELECT `value` FROM `tst` WHERE `login`=t1.login AND `param`='regdate') as regdate,

    -> (SELECT `value` FROM `tst` WHERE `login`=t1.login AND `param`='email') as email

    -> FROM `tst` t1

    -> GROUP BY `login`;

+-------+--------+------------+-----------------+

| login | city   | regdate    | email           |

+-------+--------+------------+-----------------+

| Alex  | London | 22-04-2009 | aswer@yahoo.com | 

| Nata  | Paris  | 15-07-2009 | dser@mail.com   | 

+-------+--------+------------+-----------------+

2 rows in set (0,01 sec)

VDShark

10.09.2009 00:48

oRb - молодец.
Остальные - еще остались способы =) Пишем - не стесняемся :)

VDShark

14.09.2009 19:57

Итак, никто не хочет остальные методы решения для прошлой задачи придумывать - ну и ладно =)
Еще одна задачка. На мой взгляд более интересная.
Имеется в наличии такая вот табличка

Код:

--------------------------------

  model  |    name    |  maker   

--------------------------------

  1035         |  pliers    |  F

  1103         |  pestle    |  A

  1104         |  key       |  G

  1132         |  nippers   |  G

  1273         |  gavel     |  F

  1314         |  chisel    |  A

  1402         |  mortar    |  B

  1561         |  bottle    |  F

Необходимо добавить столбец с номерами строк (т.е. номер кортежа - и собственно кортеж), в порядке возрастания модели.
Пользоваться можно ТОЛЬКО средствами, предусмотренными стандартом SQL-92 (т.е. без всяких rank(), row_number() и подобных функций), а так же без временных таблиц и иже с ними.
По хорошему - это один запрос (ну, возможно с коррелирующим подзапросом, но можно сделать и без него).
В итоге мы должны получить примерно следующее:

Код:

----------------------------------------

  num |  model  |    name    |  maker   

----------------------------------------

  1   |  1035   |    pliers  |  F 

  2   |  1103   |    pestle  |  A

  ... |  ...    |    ...     |  ...

Ну и по традиции дамп:

Код:

CREATE TABLE `tst2` (

`model` INT( 3 ) NOT NULL ,

`name` VARCHAR( 20 ) NOT NULL ,

`maker` CHAR( 1 ) NOT NULL ,

PRIMARY KEY ( `model` ) 

);



INSERT INTO `tst2` VALUES

('1104','key','G'),

('1273','gavel','F'),

('1035','pliers','F'),

('1132','nippers','G'),

('1314','chisel','A'),

('1103','pestle','A'),

('1402','mortar','B'),

('1561','bottle','F')

VDShark

26.09.2009 21:13

Неужто никому размять мозги не хочется? Задачка то плевая.
Или все "к0вычк0ми" мозг забили? :)

Pashkela

26.09.2009 21:34

нет, ну почему, интересно, просто на практике нафиг такое никогда не понадобиться. Лучше бы чонить жизненное, что могло бы хоть когда нибудь пригодиться.

VDShark

26.09.2009 21:55

Цитата:

Сообщение от Pashkela

А представь прийдется поработать со старой СУБД? В жизни всякое бывает =) Относительно недавно это не так уж и редко использовалось на практике.

astrologer

26.09.2009 23:03

<spoiler>
select count(*) as num, current.*
from test as current, test
where current.model >= test.model
group by current.model
order by current.model asc
</spoiler>

VDShark

27.09.2009 10:26

astrologer - молодец =)

VDShark

26.10.2009 17:12

На этот раз проверим как у нас обстоят дела с работой со строками.
Задача:
есть таблица с единственным полем, в котором записаны cтроки вида

Код:

sdfdf_efgregehmtri_ref_WEFEW_reg_regbb

На выходе нужно заменить все что находится между первым и последним символами "_" на "*".

Т.е. по вышеприведенной строке должно получится такое:

Код:

sdfdf_**************************_regbb

Вот небольшой дамп (хотя легче самим что нить набить (предварительно подумав), дабы отловить некоторые "подводные камни" и т.п.):

Код:

CREATE TABLE IF NOT EXISTS `tst` (

  `field` varchar(40) NOT NULL

);



INSERT INTO `tst` (`field`) VALUES

('aaaa_wd_regr_dew_bbbb'),

('asd_sed_fergf')

Реализации для конкретных СУБД могут немного отличаться, но суть от этого не меняется.

upd: естественно как всегда ответ - единственный запрос. Без рекурсий и тп. Так же никаких хп. Ну в общем вы поняли =)

krypt3r

26.10.2009 19:43

хз, это, наверно, извращенный метод, но что пришло в голову =)

Код:

select concat_ws('_', 

  substring_index(url, '_', 1), 

  repeat('*', length(url) - length(substring_index(url, '_', 1)) - 

    length(substring_index(url, '_', -1)) - 2), 

  substring_index(url, '_', -1)) as str from t1;

VDShark

26.10.2009 23:31

В принципе ход мысли правильный... Хотя ничего особо сложного в данной задачке нет =) Но все равно молодец)

Время: 06:08