Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
MSVS 2012, C# + SQLite.net

Нужен совет, как лучше (возможно, пост уже содержит ответ, но нужно ваше мнение).
=========ВМЕСТО ВВЕДЕНИЯ==========
Я старался оформить по-симпатичнее и сформулировать по-проще! Чтобы было не лень читать столько текста. Т.к. нужна помощь бывалых.

Необходимо показать пользователю следующую таблицу:
Фамилия имя отчество куча столбцов книги песни постановки ещё пара подобных столбцов
Иванов Пётр Алексеевич ... Петр-первый \n Обоз \n Сранья как любил тебя \n с днюхой вышел я из тюряги \n любовь-ромашка ...

кол-во строк от нуля до 10 000
\n я тут обозначил новую строку (как ещё мне было её обозначить?).
Как легко (я надеюсь) догадаться, тут центральная таблица "авторы" и к ней прицеплены по ID автора ещё несколько таблиц-сатиллитов - "книги","песни","постановки" (ну, ещё там пара-тройка аналогичных).

Суть предметной области (выдумал её сходу сейчас):
Картотека авторов (несколько тысяч). У каждого есть несколько написанных книг, песен и постановок и т.д.

Суть задачи:
Вывести пользователю полную таблицу этих авторов со всей инфой о книгах, песнях и постановках.

Свой компонент, который будет выводить таблицу в таком виде (в одной ячейке строки несколько доп.строк), я сделаю, это не проблема. Вопрос в том, какими запросами извлекать её из БД.

=========ВАРИАНТЫ РЕШЕНИЯ==========
»» Объединить все таблицы одним общим запросом SELECT (сJOINить их все) и получить таблицу с повторяющимися ФИО. При отображении все повторяющиеся ФИО (скажем, по ID) схлопнуть.
Ну это ж дофига лишних строк тогда получится! Куча повторяющихся книг, куча повторяющихся песен (у одного автора..) и т.д... не пойдёт.

»» Сделать SELECT по таблице авторов. Затем по ID автора делать запрос списка его книг, затем список его постановок и т.д.
Но это жопа! Потому что для 10000 авторов придётся делать 10000 запросов к серверу по книгам, 10000 по песням, ... и так далее.
Хорошо, если юзер отфильтровал список и осталось всего 5 авторов. Тогда придётся делать 1+15=16 запросов к серверу.

»» Сделать 4 запроса - одним получить список авторов, другим список книг, третьим список песен, четвёртым список постановок. И соединять их "вручную" уже на клиенте. Или больше четырёх, если будут ещё таблицы-"сатиллиты".
Насколько вообще это рационально? Книг-то будет уже несколько десятков тысяч. Это реально необходимо всю базу в оперативку выгружать?

=========НЕПОСРЕДСТВЕННО ВОПРОС==========
В общем, как корректнее сформировать запросы к серверу, чтобы получить достаточно информации для формирования такой таблицы?
20 сен 16, 02:26    [19685695]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Charles Weyland,

Очень сомнительная задача....

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

Более разумный вариант все описаное выше, но постранично диапазонами... А вообще - это польный бред, и совершенно не юзабельно: если у автора 200 песен? Или несколько авторов у одной книги.
20 сен 16, 03:10    [19685707]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
Не бред:
1. этот список необходимо получить полностью для вывода на печать и для экспорта.
Для отображения на экране он фильтруется до 5-10 штук.
2. Предметку я придумал на ходу, чтоб проще донести вопрос. У автора не может быть больше 4х песен, книг и т.д. Это физическое ограничение. Нельзя ему ))
20 сен 16, 08:30    [19685876]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
Пойду разбираться, что ещё за clr агрегат..
А что вы имели в виду под "xml-склейкой"? На каком этапе склеив
20 сен 16, 08:32    [19685883]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
982183
Member

Откуда: VL
Сообщений: 3386
test
20 сен 16, 08:48    [19685902]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
982183
Member

Откуда: VL
Сообщений: 3386
Вам надо так:
Фамилия имя отчество куча столбцов книги песни постановки ещё пара подобных столбцов
Иванов Пётр Алексеевич ... Петр-первый \n Обоз \n Сранья как любил тебя \n с днюхой вышел я из тюряги \n любовь-ромашка ...
Петров Алекс Иванович ... Иван-второй \n поезд \n сынья как ненавидел её \n с похоронами вышел я из ЗАГСА \n ненависть-роза ...


или так:
Фамилия имя отчество куча столбцов книги песни постановки ещё пара подобных столбцов
Иванов Пётр Алексеевич ... Петр-первый как любил тебя вышел я из тюряги ...
-//--//--//- ... Обоз с днюхой любовь-ромашка ...
-//--//--//- ... Сранья ...
20 сен 16, 08:49    [19685905]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
ПаWWWлОдАрЕц
Member

Откуда: NSK-PVL
Сообщений: 135
ИМХО, можно попробовать все делать одним большим селектом с джойнами на "вьюхи".
Во вьюхах сделать схлопнутое (агрегированое) представление данных по всем авторам, а данные агрегировать в виде json массива например или конкатенацией строк через \r\n. Т.о. должно получиться что для каждого автора в этой вьюхе будет одна строка, а все остальные данные будут схлопнуты в одно поле этой строки.
Так поступить с каждой сущьностью которую надо выводить.
Затем, обычный левый джоин таблицы авторов на эти вьюхи и вуаля.

Вьюхи можно заменить на CTE, подзапросы, функции и т.д. Все зависит от инструментов.

К сожалению не пользовался SQLite.net и не знаю есть ли в ней представления/процедуры/функции.

Думаю подход понятен.
20 сен 16, 09:10    [19685943]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
ПаWWWлОдАрЕц
Member

Откуда: NSK-PVL
Сообщений: 135
Кстати, у автора C#.
У девэкспрессов есть компонент квантум грид кажется.
Так он может сам, через указание пропертиса на столбцах делать такое визуальное "псевдо объединение" повторяющихся данных в столбцах.
Рекомендую посмотреть в эту сторону
20 сен 16, 09:13    [19685951]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
Если немного подумать, то станет понятно, почему девэкспресс не годится.
Потому что у одного автора, у которого 4 книги, 4 песни и 4 постановки, будет 4*4*4=256 строк. Более того, еще песни и постановками не схлопнутся, потому что не подряд записаны.
20 сен 16, 09:40    [19686016]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
982183,

Первое.
Проблему вывода на экран и создания компонента я не поднимаю.
Мой вопрос исключительно того плана - какими запросами мне получить нужную инфу для отображения таблицы.
Пока перевариваю советы...
20 сен 16, 09:42    [19686025]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Charles Weyland,

проблемы "схлопывания" для вашего красивого вывода на экран это не задачи sql. Вариант 4 судя по всему будет минимальным в затратах
20 сен 16, 09:49    [19686048]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
TaPaK
Charles Weyland,

проблемы "схлопывания" для вашего красивого вывода на экран это не задачи sql. Вариант 4 судя по всему будет минимальным в затратах

я это понимаю. Вообще SQL не особо заточен для решения подобного рода задач.
Вот я и думаю сам, что рациональнее, а что "кривокод" - закачать всё одним запросом, а потом объединять вручную по ID, или закачать список авторов и каким-то образом следующими запросами дополнить его информацией о книгах, песнях и т.д..

А что за варинат 4?
20 сен 16, 09:56    [19686069]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31986
Charles Weyland
1 »» Объединить все таблицы одним общим запросом SELECT (сJOINить их все) и получить таблицу с повторяющимися ФИО. При отображении все повторяющиеся ФИО (скажем, по ID) схлопнуть.
Ну это ж дофига лишних строк тогда получится! Куча повторяющихся книг, куча повторяющихся песен (у одного автора..) и т.д... не пойдёт.

2 »» Сделать SELECT по таблице авторов. Затем по ID автора делать запрос списка его книг, затем список его постановок и т.д.
Но это жопа! Потому что для 10000 авторов придётся делать 10000 запросов к серверу по книгам, 10000 по песням, ... и так далее.
Хорошо, если юзер отфильтровал список и осталось всего 5 авторов. Тогда придётся делать 1+15=16 запросов к серверу.

3 »» Сделать 4 запроса - одним получить список авторов, другим список книг, третьим список песен, четвёртым список постановок. И соединять их "вручную" уже на клиенте. Или больше четырёх, если будут ещё таблицы-"сатиллиты".
Насколько вообще это рационально? Книг-то будет уже несколько десятков тысяч. Это реально необходимо всю базу в оперативку выгружать?
Каждый из вариантов применим в каких то ситуациях.

1 - классический, его легко обрабатывать на клиенте, легко программировать.

2 - хорошо использовать для клиента, оптимизированного для работы с большими таблицами, когда отображается микро-доля общей информации, и данные подтягиваются во время скроллов, перемещений, масштабирования области отображения и т.д.

3 - экономный вариант для вывода очень большого объёма (типа огромного отчёта/выгрузки).
"Фамилия имя отчество куча столбцов" загружается в память (допустим, это 10 000 строк по 1000 байт, получаем 10 мегабайт, копейки), потом остальные данные читаем и выводим потоком, не сохраняя предварительно в памяти; миллион строк, миллиард, выгрузка 100 гигабайт в файл - не проблема, чисто линейная масштабируемость.
20 сен 16, 10:04    [19686100]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Charles Weyland
TaPaK
Charles Weyland,

проблемы "схлопывания" для вашего красивого вывода на экран это не задачи sql. Вариант 4 судя по всему будет минимальным в затратах

я это понимаю. Вообще SQL не особо заточен для решения подобного рода задач.
Вот я и думаю сам, что рациональнее, а что "кривокод" - закачать всё одним запросом, а потом объединять вручную по ID, или закачать список авторов и каким-то образом следующими запросами дополнить его информацией о книгах, песнях и т.д..

А что за варинат 4?

ну т.е. не 4 :) а "Сделать 4 запроса", тем более тот же DevExpress наверняка поддерживат связанные таблицы, т.е грузите авторов, связываете их с песнями и получаете грид с раскрытием, как то так
20 сен 16, 10:06    [19686101]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31986
alexeyvg
Charles Weyland
3 »» Сделать 4 запроса - одним получить список авторов, другим список книг, третьим список песен, четвёртым список постановок. И соединять их "вручную" уже на клиенте. Или больше четырёх, если будут ещё таблицы-"сатиллиты".
Насколько вообще это рационально? Книг-то будет уже несколько десятков тысяч. Это реально необходимо всю базу в оперативку выгружать?
3 - экономный вариант для вывода очень большого объёма (типа огромного отчёта/выгрузки).
"Фамилия имя отчество куча столбцов" загружается в память (допустим, это 10 000 строк по 1000 байт, получаем 10 мегабайт, копейки), потом остальные данные читаем и выводим потоком, не сохраняя предварительно в памяти; миллион строк, миллиард, выгрузка 100 гигабайт в файл - не проблема, чисто линейная масштабируемость.
Точнее, нужно широкую повторяющуюся часть ("словарь") сохранить в памяти, а остальное вывести в готовой развёрнутой "таблице".

Сохранять все данные в памяти и потом выводить уж точно никакого смысла не имеет.
Тогда уж лучше делать вариантом "1" - концептуально это то же самое, но на сервере, который работать с данными умеет хорошо, и у которого для этого есть ресурсы, а клиент в этом случае делает потоковую обработку, выделяя памяти объёмом в одну строку.
20 сен 16, 10:09    [19686109]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
alexeyvg
но на сервере, который работать с данными умеет хорошо, и у которого для этого есть ресурсы

Строго говоря, мощность сервера==мощности клиента, т.к. SQLite.
Видимо, пойду по третьему пути. В принципе, других вариантов в голову не приходит.
20 сен 16, 10:18    [19686143]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1980
Charles Weyland,

Скиньте скрипты создания Ваших таблиц, пожалуйста.
20 сен 16, 11:18    [19686472]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
Charles Weyland
MSVS 2012, C# + SQLite.net

Нужен совет....

Так Ваш вопрос касается SQLite или Microsoft SQL Server?

Если Microsoft SQL Server, то возможно MARS Вам поможет.
20 сен 16, 11:44    [19686678]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
DaniilSeryi
Charles Weyland,

Скиньте скрипты создания Ваших таблиц, пожалуйста.


CREATE TABLE authors(
id INTEGER AUTOINCREMENT,
Name STRING,
Birthdate DATE,
Passport STRING);

//----------представления и связь многи-ко-многим-----------
CREATE TABLE performance(
id INTEGER AUTOINCREMENT,
Name STRING,
date DATETIME,
Description STRING);

CREATE TABLE AuthorsInPerformance(
id INTEGER AUTOINCREMENT,
Author_ID INTEGER,
Perf_ID INTEGER);

//----------книги и связь многи-ко-многим-----------
CREATE TABLE books(
id INTEGER AUTOINCREMENT,
Name STRING,
Description STRING,
Publisher STRING,         //на самом деле, Publisher_ID, но не буду усложнять
Pages INTEGER);

CREATE TABLE AuthorsInBooks(
id INTEGER AUTOINCREMENT,
Author_ID INTEGER,
Book_ID INTEGER);

//---------у каждого автора своя песня (или две-три песни)
CREATE TABLE Songs(
id INTEGER AUTOINCREMENT,
Name STRING,
Author_ID INTEGER,
Lyrics STRING,
Duration SINGLE);

//---------у каждого автора свой номер телефона
CREATE TABLE Phones(
id INTEGER AUTOINCREMENT,
Name STRING,              //номер телефона
Author_ID INTEGER,
IsMobile BOOLEAN);


Склепал на коленках в блокноте. Мог опечататься где-то. Но суть именно такая.
20 сен 16, 11:56    [19686782]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Charles Weyland,

а в ветке по ms Sql это потому что... ?
20 сен 16, 11:59    [19686805]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4374
TaPaK
Charles Weyland,

а в ветке по ms Sql это потому что... ?

... что можно считать, что речь идёт о MS SQL Server.
Тут сидит больше всего опытных SQL-щиков.
20 сен 16, 12:14    [19686880]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1980
Charles Weyland,

В MS SQL Server я бы сделал финт ушами - для каждой таблички с публикациями / книгами / прочими работами выполнил бы запрос, собирающий все публикации / книги / прочее каждого автора в одну строку:

select Author_ID
,Books = (select ISNULL( convert(varchar,[Author_ID]) + ';', '')
from AuthorsInBooks
where Author_ID = x.Author_ID for xml path('')
)
from ( select distinct Author_ID from AuthorsInBooks) as x

id Author_ID Book_ID
1 1 1
2 1 2
3 2 3
4 2 4

получил бы
Author_ID Books
1 1;1;
2 2;2;

А дальше дело техники.
20 сен 16, 12:18    [19686902]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1980
DaniilSeryi
Charles Weyland,

В MS SQL Server я бы сделал финт ушами - для каждой таблички с публикациями / книгами / прочими работами выполнил бы запрос, собирающий все публикации / книги / прочее каждого автора в одну строку:

select Author_ID
,Books = (select ISNULL( convert(varchar,[Author_ID]) + ';', '')
from AuthorsInBooks
where Author_ID = x.Author_ID for xml path('')
)
from ( select distinct Author_ID from AuthorsInBooks) as x

и из исходной таблицы
id Author_ID Book_ID
1 1 1
2 1 2
3 2 3
4 2 4

получил бы
Author_ID Books
1 1;1;
2 2;2;

А дальше дело техники.
20 сен 16, 12:19    [19686904]     Ответить | Цитировать Сообщить модератору
 Re: Как вывести результат сразу из четырёх таблиц? (нет, я не ламер, и гугл тут не поможет)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31986
Charles Weyland
Тут сидит больше всего опытных SQL-щиков.
У вас же не по SQL вопрос, а по архитектуре приложения, с учётом конкретики конкретных платформ, СУБД и т.д.
Логично такой вопрос задавать либо в форуме по вашему средству разработки, либо в форуме по SQLite , либо в "Проектировании"

И вот как раз это подтверждает то, что решение в данном случае зависит от используемых платформ:
Charles Weyland
Строго говоря, мощность сервера==мощности клиента, т.к. SQLite.
Видимо, пойду по третьему пути. В принципе, других вариантов в голову не приходит.

Кстати, для вас "третий путь" означает, что потребуется удвоенный размер памяти; одни и те же данные будут в полном объёме и в SQLite, и в клиенте, а джойны вы будете делать самопиской в своём коде.

Я бы всё таки на вашем месте посоветовался и на профильных форумах :-)
20 сен 16, 14:07    [19687451]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить