PDA

Просмотр полной версии : MS SQL - Помогите же !


h4ckeR
03.06.2013, 20:44
Что мы имеем :

Вся фонотека хранится на компакт-дисках и включает в себя как сборники, так и сольные альбомы. Следует различать сборники и альбомы по стилям, а отдельные записи (композиции) по исполнителям. Сведения о диске должны содержать информацию о названии сборника или альбома, стиле, дате выхода. Композиция описывается названием, исполнителем, временем звучания. Исполнитель характеризуется именем или сценическим именем или названием группы, а также страной.

Есть база из 5 таблиц:

CREATE TABLE Autors (
id INT NOT NULL PRIMARY KEY,
name CHAR(255),
country CHAR(255)
);

CREATE TABLE Compositions (
id INT NOT NULL PRIMARY KEY,
name CHAR(255),
duration TIME
);

CREATE TABLE Disks (
id INT NOT NULL PRIMARY KEY,
disktype CHAR(10),
name CHAR(255),
style CHAR(255),
releasedate DATE
);

CREATE TABLE CompositionByAutors (
id_autors INT,
id_composition INT,
FOREIGN KEY (id_autors) REFERENCES Autors(id),
FOREIGN KEY (id_composition) REFERENCES Compositions(id)
);

CREATE TABLE CompositionOnDisk (
id_disk INT,
id_composition INT,
FOREIGN KEY (id_composition) REFERENCES Compositions(id),
FOREIGN KEY (id_disk) REFERENCES Disks(id)
);


Заполнили её :

INSERT INTO Disks VALUES
(1, 'Сольный', 'Мания величия', 'Хеви-метал', '1985-10-31'),
(2, 'Сольный', 'С кем ты?', 'Хеви-метал', '1986-10-01'),
(3, 'Сборник', 'Штиль', 'Хеви-метал', '2002-12-31'),
(4, 'Сольный', 'The Album', 'поп', '1997-12-12');

INSERT INTO Autors VALUES
(1, 'Ария', 'Россия'),
(2, 'ABBA', 'Швеция');

INSERT INTO Compositions VALUES
/* Мания величия */
(1, 'Это рок', '00:05:54'),
(2, 'Тореро', '00:05:29'),
(3, 'Волонтёр', '00:08:24'),
(4, 'Бивни чёрных скал', '00:04:51'),
(5, 'Мания величия', '00:01:49'),
(6, 'Жизнь задаром', '00:04:19'),
(7, 'Мечты', '00:05:16'),
(8, 'Позади Америка', '00:05:14'),
/* С кем ты? */
(9, 'Воля и разум', '00:04:34'),
(10, 'Встань, страх преодолей', '00:04:16'),
(11, 'Здесь куют металл', '00:04:42'),
(12, 'С кем ты?', '00:04:43'),
(13, 'Без тебя', '00:04:24'),
(14, 'Память о...', '00:02:49'),
(15, 'Икар', '00:04:14'),
(16, 'Игры не для нас', '00:05:47'),
/* Штиль */
(17, 'Штиль', '00:05:11'),
(18, 'Пробил час', '00:05:19'),
(19, 'Небо тебя найдёт', '00:06:04'),
(20, 'Ангельская пыль', '00:06:00'),
(21, 'Свобода', '00:05:13'),
(22, 'Герой асфальта', '00:05:15'),
(23, 'Потерянный рай', '00:05:35'),
(24, 'You’d better believe me', '00:04:00'),
(25, 'Беспечный ангел', '00:03:58'),
(26, 'Машина смерти', '00:03:54'),
(27, 'Дай руку мне', '00:05:05'),
/* The Album */
(28, 'Eagle', '00:05:53'),
(29, 'Take a Chance on Me', '00:04:03'),
(30, 'One Man, One Woman', '00:04:37'),
(31, 'The Name of the Game', '00:04:53'),
(32, 'Move On', '00:04:45'),
(33, 'Hole in Your Soul', '00:03:43'),
(34, 'Thank You for the Music', '00:03:51'),
(35, 'I Wonder (Departure)', '00:04:34'),
(36, 'I’m a Marionette', '00:04:05');

INSERT INTO CompositionOnDisk VALUES
/* Мания величия */
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(1,7),
(1,8),
/* С кем ты? */
(2,9),
(2,10),
(2,11),
(2,12),
(2,13),
(2,14),
(2,15),
(2,16),
/* Штиль */
(3,16),
(3,17),
(3,18),
(3,19),
(3,20),
(3,21),
(3,22),
(3,23),
(3,24),
(3,25),
(3,26),
(3,27),
/* The Album */
(4,28),
(4,29),
(4,30),
(4,31),
(4,32),
(4,33),
(4,34),
(4,35),
(4,36);

INSERT INTO CompositionByAutors VALUES
/* Ария */
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(1, 7),
(1, 8),
(1, 9),
(1, 10),
(1, 11),
(1, 12),
(1, 13),
(1, 14),
(1, 15),
(1, 16),
(1, 17),
(1, 18),
(1, 19),
(1, 20),
(1, 21),
(1, 22),
(1, 23),
(1, 24),
(1, 25),
(1, 26),
(1, 27),
/* ABBA */
(2, 28),
(2, 29),
(2, 30),
(2, 31),
(2, 32),
(2, 33),
(2, 34),
(2, 35),
(2, 36);


Имеем 2 задания:

1. Какое общее время звучания композиций каждого диска стиля В?

Мой вариант решения :

SELECT
/* Название диска */
Disks.name,
/* Часы */
cast(Sum(DateDiff(Second, 0, Duration)) / 3600 as varchar) + ':' +
/* Минуты */
cast(Sum(DateDiff(Second, 0, Duration)) / 60 % 60 as varchar) + ':' +
/* Секунды */
cast(Sum(DateDiff(Second, 0, Duration)) % 60 as varchar) as TotalDuration
FROM
Disks,Compositions
INNER JOIN
CompositionOnDisk ON Compositions.id=CompositionOnDisk.id_composition
WHERE
CompositionOnDisk.id_disk = Disks.id AND Disks.Style='Хеви-метал'
GROUP BY
Disks.name


2. Какая из композиций исполнителя Д самая длинная?

Мой вариант решения :

SELECT
MAX(Duration) AS Duration
FROM
Compositions
WHERE
id IN (
SELECT
id_composition
FROM
CompositionByAutors
WHERE
id_autors IN (
SELECT
id
FROM
Autors
WHERE
name='Ария'
)
)


Всё конечно хорошо и замечательно, но нужно как-то упростить запрос.
Какие есть идеи ?..

Der_SySLIK
03.06.2013, 21:01
во втором select * from composition order by dlina (1ое есть самое длинное) //ps: не лез в дебри лишь образно показал.
в первом таким же запросом выбераем их все и уже циклом суммируем время композиций.


ИМХО.

Добавлено через 34 секунды
select 1 from composition

h4ckeR
03.06.2013, 21:11
Der_SySLIK, Маленько не то ты подсказал, в 1м нужно суммировать композиции по исполнителю, в базе с композициями нет информации об исполнителе, а функция SUM для типа TIME не работает.

Во 2м тоже самое - связь идёт через 2 таблицы...

Ещё какие идеи ?

RGrand
03.06.2013, 21:18
h4ckeR, а че тут упрощать? у тебя воды не налито. Все запросы по условию критерия. И так пойдет *klass*

h4ckeR
03.06.2013, 21:22
RGrand, эт курсач аццкий, нужно выполнить на высоком уровне.
а то что выше - нельзя таковым назвать)

gg.Groove
03.06.2013, 21:44
запихать все данные в одну таблицу можно еще, просто, если правильно сделать, то и быстро будет. В идеале для таких дел использовать графовые базы данных

h4ckeR
03.06.2013, 21:50
gg.Groove, пример можно увидеть исходя из данных в топике ?:)