Нетривиальные задачи Sql [Архив]

VDShark

26.06.2008, 00:23

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

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

Есть таблица, назовем ее 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

Пойдет?
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

']подзапросы тоже нельзя?
Можно. Хотя я без них решил :)

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

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

-=lebed=-

26.06.2008, 17:21

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

VDShark

26.06.2008, 17:36

Модифицировать исходную таблицу можно? Создавать дополнительные я так понял нельзя...
Нет. Никаких 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

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

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

VDShark

27.06.2008, 21:06

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

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

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

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

Red_Red1

27.06.2008, 22:23

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

Naydav

27.06.2008, 22:30

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

VDShark

27.06.2008, 22:57

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

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

VDShark

27.06.2008, 23:14

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

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

--
-- Структура таблицы `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

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?" (http://www.sql-ex.ru/help/select14.php)

MasterMushi

19.09.2008, 19:05

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

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

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

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

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

VDShark

22.09.2008, 12:52

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

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

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

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