Модераторы: LSD
  

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> Помогите составить SQL-запрос 
V
    Опции темы
andrey_pst
Дата 31.1.2006, 14:59 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Новичок



Профиль
Группа: Awaiting Authorisation
Сообщений: 37
Регистрация: 24.4.2003
Где: Пермь

Репутация: нет
Всего: 1
Я не силен в SQL, кому не трудно помогите составить запрос:

Ситуация следующая:
Есть 2 таблицы - Автор и Книга:

Код

CREATE TABLE AUTOR (
    COD_AUTOR   INTEGER,
    FAMILY      VARCHAR(50),
    FIRST_NAME  VARCHAR(50),
    LAST_NAME   VARCHAR(50),
    POL         VARCHAR(50) NOT NULL,
    BIRSDAY     TIMESTAMP,
    PHONE       CHAR(9)
);

CREATE TABLE BOOK (
    COD_BOOK   INTEGER,
    NAME       VARCHAR(50) NOT NULL,
    CASHE      MONEY,
    IZDATEL    VARCHAR(50) NOT NULL,
    COD_AUTOR  INTEGER NOT NULL,
    COUNTS     INTEGER
);


Нужно определить авторов, написавших наибольшее количество книг.
Я так это все понимаю: нужно подсчитать количество книг для каждого автора
(например, так:
Код

SELECT B.COD_AUTOR, COUNT(B.COD_AUTOR) AS COUNTBOOKS
FROM BOOK B 
GROUP BY B.COD_AUTOR 
ORDER BY 2 DESC
).


Потом, исходя из результатов этого запроса (по результатам B.COD_AUTOR) выбрать
из таблицы AUTOR значения полей FAMILY, FIRST_NAME, LAST_NAME. В результате на
экран надо вывести что-то вроде этого:

Код

-----------------------------------------------------------------------------------------------------
COD_AUTOR    FAMILY        FIRST_NAME    LAST_NAME    COUNTBOOKS
-----------------------------------------------------------------------------------------------------
    1             Петров          Петр              Петрович                20
    2             Иванов          Иван              Иванович                  2
    3            Сидоров        Сидор            Сидорович              5
-----------------------------------------------------------------------------------------------------


Вот только никак не пойму, как все эти действия запихать в один SQL-запрос
(причем использовать только средства ANSI).
PM MAIL   Вверх
LSD
Дата 31.1.2006, 15:50 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Leprechaun Software Developer
****


Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538
Не уверен что min сработает для строк в любой СУБД, но в остальном так.
Код
select B.COD_AUTOR, min(A.FAMILY), min(A.FIRST_NAME), min(A.LAST_NAME), count(B.COD_AUTOR)
  from AUTOR A, BOOK B
  where A.COD_AUTOR = B.COD_AUTOR
  group by B.COD_AUTOR



--------------------
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
PM MAIL WWW   Вверх
andrey_pst
Дата 31.1.2006, 18:09 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Новичок



Профиль
Группа: Awaiting Authorisation
Сообщений: 37
Регистрация: 24.4.2003
Где: Пермь

Репутация: нет
Всего: 1
to LSD - огромное спасибо, работает smile
репутацию, к сожалению, вам поднять не могу - не хватает постов. smile
PM MAIL   Вверх
igon
Дата 31.1.2006, 18:54 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


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

Репутация: 3
Всего: 21
LSD
А зачем здесь функция min?
Вот так тоже вроде работает
Код

select A.FAMILY||' '||A.FIRST_NAME||' '||A.LAST_NAME, count(A.FAMILY)
  from AUTOR A, BOOK B
  where A.COD_AUTOR = B.COD_AUTOR
  group by A.FAMILY||' '||A.FIRST_NAME||' '||A.LAST_NAME
  Order By 2 Desc

Если, конечно, полных тезок-однофамильцев не предвидится.
Конкатенация не обязательна.


--------------------
Хотите поговорить об этом?
PM   Вверх
LSD
Дата 31.1.2006, 20:53 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Leprechaun Software Developer
****


Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538
Цитата(igon @ 31.1.2006, 18:54 Найти цитируемый пост)
А зачем здесь функция min?
Вот так тоже вроде работает

Я пробовал в Oracle не работает. Если я ничего не путаю, то при наличии group by в selecte можно указывать только столбцы которые присутсвуют в group by или агрегатные функции.

Цитата(andrey_pst @ 31.1.2006, 18:09 Найти цитируемый пост)
to LSD - огромное спасибо, работает

Пожалуйста smile
Цитата(andrey_pst @ 31.1.2006, 18:09 Найти цитируемый пост)
репутацию, к сожалению, вам поднять не могу - не хватает постов.

Переживу smile


--------------------
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
PM MAIL WWW   Вверх
andrey_pst
Дата 31.1.2006, 21:39 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Новичок



Профиль
Группа: Awaiting Authorisation
Сообщений: 37
Регистрация: 24.4.2003
Где: Пермь

Репутация: нет
Всего: 1
Цитата(LSD @ 31.1.2006, 20:53 Найти цитируемый пост)

Я пробовал в Oracle не работает. Если я ничего не путаю, то при наличии group by в selecte можно указывать только столбцы которые присутсвуют в group by или агрегатные функции.

Да, я именно с этой проблемой и парился (в Firebird). Использование min - это классный ход!

Цитата(igon @ 31.1.2006, 18:54 Найти цитируемый пост)

Если, конечно, полных тезок-однофамильцев не предвидится.

Такие есть.
PM MAIL   Вверх
DeadSoul
Дата 31.1.2006, 23:03 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Эксперт
***


Профиль
Группа: Участник
Сообщений: 1217
Регистрация: 25.9.2005
Где: Москва

Репутация: нет
Всего: 11
Цитата(andrey_pst @ 31.1.2006, 21:39 Найти цитируемый пост)

Такие есть.

Код

SELECT * FROM AUTOR A INNER JOIN
(
SELECT B.COD_AUTOR, COUNT(B.COD_AUTOR) AS COUNTBOOKS
FROM BOOK B 
GROUP BY B.COD_AUTOR 
) QWE 
ON A.COD_AUTOR=QWE.COD_AUTOR
ORDER BY QWE.COUNTBOOKS DESC


Писал прямо здесь(могут быть ошибки). Работать доллжно.

P.S. Слово автор по англицки пишется по другому


--------------------
 Если Вы получили ответ на Ваш вопрос, то нажмите на "Вопрос решен". 

Бьем спамеров их же оружием. Пусть весь спам сыпется им
[email protected] 
PM   Вверх
SergeBS
Дата 1.2.2006, 11:27 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Эксперт
***


Профиль
Группа: Завсегдатай
Сообщений: 1111
Регистрация: 10.6.2005
Где: Владимир

Репутация: 1
Всего: 22
Мне это все до жути напоминает учебные примеры (с ответами) из какой-то книжки по SQL. Название никак не вспомню. Блин, это BOL MS SQL! только названия столбцов другие - английские.
PM MAIL   Вверх
igon
Дата 2.2.2006, 21:46 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


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

Репутация: 3
Всего: 21
to LSD
Цитата(LSD @ 31.1.2006, 20:53 Найти цитируемый пост)

Я пробовал в Oracle не работает.

Хм, у меня в Oracle 9.1 все работает...
Цитата(LSD @ 31.1.2006, 20:53 Найти цитируемый пост)

Если я ничего не путаю, то при наличии group by в selecte можно указывать только столбцы которые присутсвуют в group by или агрегатные функции

Так и есть. Но, ИМХО, естественнее добавить в group by столбцы - я-таки долго ломал голову над физическим смыслом min(A.FAMILY) smile

Цитата(andrey_pst @ 31.1.2006, 21:39 Найти цитируемый пост)

Такие есть.


Код

select A.COD_AUTOR, A.FAMILY, A.FIRST_NAME, A.LAST_NAME, count(A.COD_AUTOR)
  from AUTOR A, BOOK B
  where A.COD_AUTOR = B.COD_AUTOR
  group by A.COD_AUTOR, A.FAMILY, A.FIRST_NAME, A.LAST_NAME
  Order By 5 Desc


Интересно бы сравнить производительность вариантов
1) с min()
2) с конкатенацией
3) без конкатенации
Мои тестовые таблицы слишком маленькие -> время одинаковое smile



--------------------
Хотите поговорить об этом?
PM   Вверх
LSD
Дата 2.2.2006, 23:13 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Leprechaun Software Developer
****


Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538
Цитата(igon @ 2.2.2006, 21:46 Найти цитируемый пост)
Но, ИМХО, естественнее добавить в group by столбцы - я-таки долго ломал голову над физическим смыслом min(A.FAMILY)

Согласен, это более элегантное решение, а так:
Код
select B.COD_AUTOR, A.FAMILY, A.FIRST_NAME, A.LAST_NAME, count(B.COD_AUTOR)    
  from AUTOR A, BOOK B    
  where A.COD_AUTOR = B.COD_AUTOR    
  group by B.COD_AUTOR, A.FAMILY, A.FIRST_NAME, A.LAST_NAME

не будет проблем и с полными однофамильцами.


Цитата(igon @ 2.2.2006, 21:46 Найти цитируемый пост)
Интересно бы сравнить производительность вариантов
1) с min()
2) с конкатенацией
3) без конкатенации
Мои тестовые таблицы слишком маленькие -> время одинаковое

Ты как меряешь? Сам время засекаешь или используешь оракловскую статистику?


--------------------
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
PM MAIL WWW   Вверх
igon
Дата 3.2.2006, 06:58 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


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

Репутация: 3
Всего: 21
Цитата(LSD @ 2.2.2006, 23:13 Найти цитируемый пост)

Ты как меряешь?

Использую время выполнения запроса в PL/SQL Developer. Но для таблиц с 10-15 записями любой способ измерения, ИМХО, будет "несерьезным".




--------------------
Хотите поговорить об этом?
PM   Вверх
SergeBS
Дата 3.2.2006, 17:36 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Эксперт
***


Профиль
Группа: Завсегдатай
Сообщений: 1111
Регистрация: 10.6.2005
Где: Владимир

Репутация: 1
Всего: 22
igon
Oracle не щупал (пока smile ), а в MS SQL есть возможность посмотреть план выполнения и цены. Неужто в Оракле подобного нету?
PM MAIL   Вверх
DeadSoul
Дата 3.2.2006, 23:34 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Эксперт
***


Профиль
Группа: Участник
Сообщений: 1217
Регистрация: 25.9.2005
Где: Москва

Репутация: нет
Всего: 11
Цитата(SergeBS @ 3.2.2006, 17:36 Найти цитируемый пост)

Неужто в Оракле подобного нету?

Есть естественно


--------------------
 Если Вы получили ответ на Ваш вопрос, то нажмите на "Вопрос решен". 

Бьем спамеров их же оружием. Пусть весь спам сыпется им
[email protected] 
PM   Вверх
LSD
Дата 4.2.2006, 00:00 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Leprechaun Software Developer
****


Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538
Цитата(SergeBS @ 3.2.2006, 17:36 Найти цитируемый пост)
Oracle не щупал (пока  ), а в MS SQL есть возможность посмотреть план выполнения и цены. Неужто в Оракле подобного нету?

Цена имеет смысл только для аналогичного запроса.


--------------------
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
PM MAIL WWW   Вверх
igon
Дата 4.2.2006, 03:43 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


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

Репутация: 3
Всего: 21
Для 6 авторов и 196608 книг
Oracle SQL Analyze выдал такой результат (для Rule)

1) с min()
2) с конкатенацией
3) без конкатенации

Elapsed Time: 3 < 1 < 2 (3.03c < 3.26c < 4.03c)

Цитата(LSD @ 4.2.2006, 00:00 Найти цитируемый пост)

Цитата(SergeBS @ 3.2.2006, 17:36 )
Oracle не щупал (пока  ), а в MS SQL есть возможность посмотреть план выполнения и цены. Неужто в Оракле подобного нету?

Цена имеет смысл только для аналогичного запроса.


Я согласен с LSD, более того - цена имеет смысл при оптимизации одного запроса, не для сравнения разных.
BTW, Explain Plan у всех трех запросов совершенно одинаковый.



--------------------
Хотите поговорить об этом?
PM   Вверх
  
 Ответ в темуСоздание новой темы Создание опроса
Правила форума "Общие вопросы по базам данных"
LSD
Zloxa

Данный форум предназначен для обсуждения вопросов о базах данных не попадающих под тематику других форумов:

  • вопросам по СУБД для которых нет отдельных подфорумов
  • вопросам которые затрагивают несколько разных СУБД (например проблема выбора)
  • инструменты для работы с СУБД
  • вопросы проектирования БД
  • теоретически вопросы о СУБД

Данный форум не предназначен для:

  • вопросов о поиске разлиных БД (если не понимаете чем БД отличается от СУБД то: а) вам не сюда; б) Google в помощь)
  • обсуждения проблем с доступом к СУБД из различных ЯП (для этого есть соответсвующие форумы по каждому ЯП)
  • обсуждения проблем с написание SQL запросов, для этого есть форум Составление SQL-запросов
  • просьб о написании курсовой, реферата и т.п., для этого есть Центр помощи или фриланс биржа
  • объявлений о найме специалистов, для этого есть раздел Объявления о найме специалистов

Если вы не соблюдаете эти правила, не удивляйтесь потом не найдя свою тему/сообщение. ;)

Полезные советы:

При написании сообщения постарайтесь дать теме максимально понятное название. В теме максимально подробно опишите проблему. Если применимо укажите: название базы данных и версии (MySQL 4.1, MS SQL Server 2000 и т.п.); используемых язык программирования; способа доступа (ADO, BDE и т.д.); сообщения об ошибках.

Для вставки кода используйте теги [code=sql] [/code].

Литературу по базам данных можно поискать здесь.

Действия модераторов можно обсудить здесь.

Если Вам понравилась атмосфера форума, заходите к нам чаще! С уважением, LSD, Zloxa.
 
0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | СУБД, общие вопросы | Следующая тема »

Подписаться на тему | Подписка на этот форум | Скачать/Распечатать тему


 




[ Время генерации скрипта: 0.0663 ]   [ Использовано запросов: 22 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
 По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.