VPF::Проблема с подзапросом - Форум программистов

vzf

Дата 11.1.2007, 16:01 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 203
Регистрация: 10.9.2005

Репутация: нет
Всего: 5

Помогите разобраться с подзапросами.

Есть таблицы Book и Author

Код


-- Автор книги
create table Author
(
 Id number(9) not null primary key, -- Абстрактный идентификатор автора
 Name_0 varchar(40),                -- Фамилия
 Name_1 varchar(40),                -- Имя
 Remark_ varchar(26),               -- Короткое примечание на случай тёзок
 check (Name_0 is not null or Name_1 is not null),
 unique (Name_0, Name_1, Remark_)
)
/

-- Книга
create table Book
(
 BCI number(12) not null primary key,  -- Каталожный номер книги
 Title varchar(160),                   -- Название книги
 Edition_Nr number(2) default 1,       -- Номер издания книги
 Lang char(2) not null,                -- Код языка книги по ISO 639-1
 Year number(4)                        -- Год издания        
  check (Year between 1601 and 2099),  
 Pages_Count number(4),                -- Количество страниц
 ISBN varchar(14) unique               -- ISBN код
  check (trim(translate(ISBN,' -0123456789',' ')) is null), 
 Category_Code varchar(14)             -- Категория знаний
)
/

При следующем запросе возникает ошибка

Код


select name_0 , year ,(  
                          select r from ( 
                                          select rownum r, bci tmpBci, name_0, year from Book, Author 
                                          where Author.name_0 = name_0
                                          and Book.year = year  )
                        
                          where tmpBci = b.bci )    as nr      
from Book b, Author a

 /

Код


[1]: (Error): ORA-01427: подзапрос одиночной строки возвращает более одной строки

Мне нужно, чтобы в самом внутреннем подзапросе

Код



select rownum r, bci tmpBci, name_0, year from Book, Author 
where Author.name_0 = name_0
and Book.year = year

name_0 и year были те же что и в самом внешнем .

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

Java - Write Once, Test EveryWhere!

3x3

Дата 11.1.2007, 16:46 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 261
Регистрация: 17.9.2006

Репутация: 2
Всего: 8

А вам вообще-то обязательно перечислять для заданного автора все книги изданные в заданном году безотносительства авторства этих книг? А потом выводить всё это дело в количестве равном произведению числа авторов на число книг в базе?

Результат всё равно неосмысленный. А ошибка, видимо, зависит от версии Oracle. Я попробовал - ничего не случилось.

В данном случае совет - напишите русским текстом, чего хотите получить из выполнения запроса.
А ещё лучше, попробуйте выполнить простой запрос: "хочу получить названия всех книг, написанных заданным автором" - и найдете как митнимум ошибку в своём дизайне. И заодно поймете как надо строить более сложный запрос.

Я не шучу!
Скажите своей базе: "хочу получить названия всех книг, написанных заданным автором" прямо сейчас!

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

Зачем платить больше,
когда можно заплатить дважды?

vzf

Дата 11.1.2007, 18:48 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 203
Регистрация: 10.9.2005

Репутация: нет
Всего: 5

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

Я понимаю, что отдельно указанный мною запрос выглядит бессмысленным. Просто я хочу использовать его в реализации представления.

У меня есть следующее задание:

Библиотечный каталог реализован тремя таблицами:

Код


-- Автор книги
create table Author
(
 Id number(9) not null primary key, -- Абстрактный идентификатор автора
 Name_0 varchar(40),                -- Фамилия
 Name_1 varchar(40),                -- Имя
 Remark_ varchar(26),               -- Короткое примечание на случай тёзок
 check (Name_0 is not null or Name_1 is not null),
 unique (Name_0, Name_1, Remark_)
)
/

-- Книга
create table Book
(
 BCI number(12) not null primary key,  -- Каталожный номер книги
 Title varchar(160),                   -- Название книги
 Edition_Nr number(2) default 1,       -- Номер издания книги
 Lang char(2) not null,                -- Код языка книги по ISO 639-1
 Year number(4)                        -- Год издания        
  check (Year between 1601 and 2099),  
 Pages_Count number(4),                -- Количество страниц
 ISBN varchar(14) unique               -- ISBN код
  check (trim(translate(ISBN,' -0123456789',' ')) is null), 
 Category_Code varchar(14)             -- Категория знаний
)
/ 

-- Авторы книги (дочерняя сущность для сущности Книга)
create table Book_Author
(
 BCI references Book on delete cascade,  
 Nr number(2) not null,
 Author_Id references Author not null,
 primary key (BCI, Nr),
 unique (BCI, Author_Id)
)
/

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

Поля представления:
    Ref_Code,
    Title,
    Edition_Nr,
    Year,
    ISBN,
    Category_Code

Представление необходимо написать без использования аналитических и собственных функций.

Для меня сложность заключается в создании этого порядкового номера. Указанный запрос я хочу использовать для определения этого порядкового номера.

Код



-- Автор книги
create table Author
(
 Id number(9) not null primary key, -- Абстрактный идентификатор автора
 Name_0 varchar(40),                -- Фамилия
 Name_1 varchar(40),                -- Имя
 Remark_ varchar(26),               -- Короткое примечание на случай тёзок
 check (Name_0 is not null or Name_1 is not null),
 unique (Name_0, Name_1, Remark_)
)
/

-- Книга
create table Book
(
 BCI number(12) not null primary key,  -- Каталожный номер книги
 Title varchar(160),                   -- Название книги
 Edition_Nr number(2) default 1,       -- Номер издания книги
 Lang char(2) not null,                -- Код языка книги по ISO 639-1
 Year number(4)                        -- Год издания        
  check (Year between 1601 and 2099),  
 Pages_Count number(4),                -- Количество страниц
 ISBN varchar(14) unique               -- ISBN код
  check (trim(translate(ISBN,' -0123456789',' ')) is null), 
 Category_Code varchar(14)             -- Категория знаний
)
/ 

-- Авторы книги (дочерняя сущность для сущности Книга)
create table Book_Author
(
 BCI references Book on delete cascade,  
 Nr number(2) not null,
 Author_Id references Author not null,
 primary key (BCI, Nr),
 unique (BCI, Author_Id)
)
/

insert into Author(Id, Name_0, Name_1, Remark_)
    values(1,'Alexandr','Pushkin','');

insert into Author(Id, Name_0, Name_1, Remark_)
    values(2,'Vladimir','Mayakovsky','');    

insert into Book(BCI, Title, Edition_Nr, Lang, Year, Pages_Count, ISBN, Category_Code)
    values(1,'Evgeny Onegin',1,'RU',1825,100,'123','asd');

insert into Book(BCI, Title, Edition_Nr, Lang, Year, Pages_Count, ISBN, Category_Code)
    values(2,'Evgeny Onegin',2,'RU',1860,150,'143','asd');

insert into Book(BCI, Title, Edition_Nr, Lang, Year, Pages_Count, ISBN, Category_Code)
    values(4,'Zolotaya rybka',1,'RU',1825,100,'543','asd');

insert into Book(BCI, Title, Edition_Nr, Lang, Year, Pages_Count, ISBN, Category_Code)
    values(3,'Pasport',1,'RU',1925,100,'321','fgh');
    


    


insert into Book_Author(BCI, Nr, Author_Id)
    values(1,1,1);
    
insert into Book_Author(BCI, Nr, Author_Id)
    values(1,2,2);
    
insert into Book_Author(BCI, Nr, Author_Id)
    values(2,1,1);

insert into Book_Author(BCI, Nr, Author_Id)
    values(3,1,2);

insert into Book_Author(BCI, Nr, Author_Id)
    values(4,1,1);   


create view FolioForRef as 
    select  (  
              Name_0  || ' ' 
              
             
              || to_char(Year) || ' ' 
              || (  
                            
               
                 select r from ( select rownum r, bci tmpBci from Book, Author 
                                
                                where Author.name_0 = a.name_0 --'Alexandr'
                                and Book.year = b.year --1825 
                                                           
                                
                                  )
                 where tmpBci = b.bci --4    
                 
                                  
                  ) 
            
            ) as Ref_Code,
            
            Title, Edition_Nr, Year, ISBN, Category_Code
    
    from Book b,Book_Author,Author a
    where a.id = Book_Author.author_id
    and   Nr = 1
    and   Book_Author.bci = b.bci
    and   b.edition_nr = 1 
/

select * from FolioForRef;

Если в запросе указать конкретные значения (Alexandr, 1825,4), то получаются правильные порядковые номера.

Скорей всего есть другой вариант определния этого порядкового номера. Это то,что мне в голову пришло (в SQL я не силён)

Это сообщение отредактировал(а) vzf - 11.1.2007, 18:59

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

Java - Write Once, Test EveryWhere!

3x3

Дата 11.1.2007, 19:16 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 261
Регистрация: 17.9.2006

Репутация: 2
Всего: 8

Можно предположить, что первый по порядку автор имеет Book_Author.Nr=1 для каждой данной книги? smile

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

Зачем платить больше,
когда можно заплатить дважды?

vzf

Дата 12.1.2007, 15:27 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 203
Регистрация: 10.9.2005

Репутация: нет
Всего: 5

Думаю так оно и есть. Если у книги несколько авторов, то в записи для первого автора Nr = 1.

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

Java - Write Once, Test EveryWhere!

3x3

Дата 12.1.2007, 16:00 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 261
Регистрация: 17.9.2006

Репутация: 2
Всего: 8

Цитата(vzf @ 12.1.2007, 15:27)

Думаю так оно и есть. Если у книги несколько авторов, то в записи для первого автора Nr = 1.

Так как, сойдет такой вариант определения номера автора?
Нет риска, что первого автора забьют по ошибке, когда-нить в будущем ошибку выявят, строку удалят и нумерация пойдет с "2"?

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

Зачем платить больше,
когда можно заплатить дважды?

vzf

Дата 12.1.2007, 18:13 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 203
Регистрация: 10.9.2005

Репутация: нет
Всего: 5

Я вывожу фамилию первого автора ( у которого Nr = 1 ), т.к. в задании сказано

Цитата

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

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

наверное не очень понятно получается у меня объяснить .....

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

Java - Write Once, Test EveryWhere!

3x3

Дата 12.1.2007, 19:26 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 261
Регистрация: 17.9.2006

Репутация: 2
Всего: 8

Цитата(vzf @ 12.1.2007, 18:13)

наверное не очень понятно получается у меня объяснить .....

Не, с вами всё нормально smile

Это я не сразу дочитал: когда смотрел на селект в самый первый раз, то показалось, что вы пытаетесь номер автора извлечь, так и исходил из этого по инерции. А что бы порядок вхождения в группу.. Ночью попробую подумать как такое можно сделать, в лоб что-то ничего приходит с этим доп.номером, но прикольно.
Хотя смысла в таких идентификаторах не понимаю. Как по ним обратно что-то извлекать? Может вместо доп-номера ISBN лучше использовать и вставлять его всегда? Тогда идентификатор будет иметь всегда одинаковый формат и однозначно указывать на конкретную книжку.

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

Зачем платить больше,
когда можно заплатить дважды?

vzf

Дата 12.1.2007, 22:25 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 203
Регистрация: 10.9.2005

Репутация: нет
Всего: 5

Цитата

Может вместо доп-номера ISBN лучше использовать и вставлять его всегда? Тогда идентификатор будет иметь всегда одинаковый формат и однозначно указывать на конкретную книжку.

Наверное лучше. Но задание такое какое оно есть
(в задании хочется, что если есть две книги с одинаковым первым автором и годом издания, то к Ref_Code нужно дописывать доп. номер. Например:
есть две книги

Код


 Pushkin 1825

Тогда для одной нужно дописать доп. код 1, а для другой 2. А эти 1 и 2 надо им назначать исходя из их BCI, т.е. если у первой записи BCI 2, a у второй 5, то к Ref_Code первой записи надо дописать 1, а для второй -- 2 )

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

Java - Write Once, Test EveryWhere!

3x3

Дата 13.1.2007, 01:59 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 261
Регистрация: 17.9.2006

Репутация: 2
Всего: 8

Короче, вот.
Потребовался дополнительный источник монотонно возрастающих чисел, количество которых заведомо не меньше одинаковых пар Автор-Книга.
Т.е. таблица с единственной колонкой, содержащей значения 1,2,3,4,5,...,n

В качестве такого источника чисел была взята таблица Book:
select rownum as n from Book

Код


select q.Name_0||' '||q.Year||decode(q.lmt,1,'',' '||to_char(nns.n)) from
  (select a.Name_0, b.Year, count(1) lmt
      from Author a, Book b, book_author ba 
         where ba.BCI=b.BCI  and  ba.Author_Id=a.id and ba.nr=1
         group by a.Name_0, b.Year) q,
  (select rownum as n from Book) nns --<<<
  where nns.n<=lmt  -- <<<
  order by name_0, q.Year, nns.n

Если чего опять не пропустил в задании, то вроде всё.

А почему аналитическими функциями не любите пользоваться? row_number() смотрелась бы вроде уместно.

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

Зачем платить больше,
когда можно заплатить дважды?

vzf

Дата 13.1.2007, 22:49 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 203
Регистрация: 10.9.2005

Репутация: нет
Всего: 5

На первый взгляд результат тот, что нужен.

У меня вопрос, как селать, чтобы кроме RefCode запрос еще выводил например title и isdn?
Если я делаю так

Код


select q.Name_0||' '||q.Year||decode(q.lmt,1,'',' '||to_char(nns.n)), q.title from
  (select a.Name_0, b.Year, b.title count(1) lmt
      from Author a, Book b, book_author ba 
         where ba.BCI=b.BCI  and  ba.Author_Id=a.id and ba.nr=1
         group by a.Name_0, b.Year, b.title) q,
  (select rownum as n from Book) nns --<<<
  where nns.n<=lmt  -- <<<
  order by name_0, q.Year, nns.n
  /

то lmt для всех записей будет равным 1

Цитата

А почему аналитическими функциями не любите пользоваться? row_number() смотрелась бы вроде уместно.

Я об этом думал. Но в задании сказано, что надо сделать без своих и без аналитических функций.

Это сообщение отредактировал(а) vzf - 13.1.2007, 22:50

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

Java - Write Once, Test EveryWhere!

3x3

Дата 14.1.2007, 05:54 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 261
Регистрация: 17.9.2006

Репутация: 2
Всего: 8

Цитата(vzf @ 13.1.2007, 22:49)

На первый взгляд результат тот, что нужен.

У меня вопрос, как селать, чтобы кроме RefCode запрос еще выводил например title и isdn?

А вот это совсем маловероятно.
Если вы обратите внимание на запрос, то увидите, что дополнительный цифровой индекс бессмыссленен. Это то, о чем я говорил выше - идентификатор не адресует однозначно никакую запись, поэтому ему нельзя поставить в соответствие какую-либо конкретную книгу.

Пытаясь сопоставить заголовки книг с данными идентификаторы вы получите декартово произведение - набор записей из N=М**2 строк, где М - число записей для каждой пары автор-год издания. Т.е. скажем у вас один автор в одном году написал три книги, т.е. имеется три идентификатора: ID_1, ID_2, ID_3. Если станете выбирать заголовки книг для этих ID, то комбинаций, удовлетворяющих условию, будет девять:
ID_1  -  TITLE_1
ID_2  -  TITLE_1
ID_3  -  TITLE_1
ID_1  -  TITLE_2
ID_2  -  TITLE_2
ID_3  -  TITLE_2
ID_1  -  TITLE_3
ID_2  -  TITLE_3
ID_3  -  TITLE_3

У записей в базе данных в принципе нет порядкового номера, они идентифицурутся по ключу. И пока ваш идентификатор не включает в себя ключа, уникально идентифицирующего конкретную книгу, то он никакую конкретную книгу и не адресует. И автора, кстати, это тоже касается: он так же адресует всех однофамильцев.

Цитата(vzf)

Цитата

А почему аналитическими функциями не любите пользоваться? row_number() смотрелась бы вроде уместно.

Я об этом думал. Но в задании сказано, что надо сделать без своих и без аналитических функций.

Задание, задание..
Удовлетворите моё люботство, расскажите где вы добыли такое задание?

Это сообщение отредактировал(а) 3x3 - 14.1.2007, 05:56

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

Зачем платить больше,
когда можно заплатить дважды?

vzf

Дата 14.1.2007, 13:25 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 203
Регистрация: 10.9.2005

Репутация: нет
Всего: 5

Цитата

Удовлетворите моё люботство, расскажите где вы добыли такое задание?

Такое задание получил по курсу "СУБД" в универе (лабораторная работа по теме представления)

Это сообщение отредактировал(а) vzf - 14.1.2007, 13:27

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

Java - Write Once, Test EveryWhere!

3x3

Дата 14.1.2007, 17:20 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 261
Регистрация: 17.9.2006

Репутация: 2
Всего: 8

В аннотации к методичке не указано, какую траву курил автор?

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

Для простоты конечного запроса представим, что тот первый запрос, который ИДы генерит, у тебя в виде вьюхи сделан. Скажем имя вьюхи b_test_1.

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

Код


create view b_test_2 as select a.Name_0, b.Year, b.title ttl -- и пр. поля про книгу какие надо
  from Author a, Book b, book_author ba 
  where ba.BCI=b.BCI  and  ba.Author_Id=a.id and ba.nr=1
  order by a.name_0, b.Year, b.BCI -- последнее необязательно

У тебя две вьюхи теперь возвращают одинаковое количество записей, при этом в одинаковом порядке для каждой пары автор-год. В результирующем запросе объедини их по номеру строки:

Код


select a.id, b.ttl from
(select rownum n, id from b_test_1) a,
(select rownum n, ttl from b_test_2) b
where a.n=b.n;

Но ещё раз повторяю - это не имеет тысызыть физического смысла и подобное решение в реальной жизни было бы неприемлемо.

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

Зачем платить больше,
когда можно заплатить дважды?

vzf

Дата 14.1.2007, 22:32 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 203
Регистрация: 10.9.2005

Репутация: нет
Всего: 5

Спасибо большое. Теперь все как надо. Если бы мог обязательно поставил "+" в репутацию.

P.S.

Цитата

Код


 order by a.name_0, b.Year, b.BCI -- последнее необязательно

Кстати довольно важное условие. Из-за него доп. порядковые номера будут соответсвовать порядку BCI для книг

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

Java - Write Once, Test EveryWhere!

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Oracle \| Следующая тема »