Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Может кому пригодиться, ниже приведен простой (механический) способ как в конце полученной таблицы вывести итоговое значение строк, пример того, как после select вывести count одним sql-запросом. Если у кого есть иные варианты решения данной задачки, предлагайте, интересно посмотреть. Для чего это нужно: для получения данных из БД SQL для отчетов, используемых во внешних системах, потому на выходе необходимо иметь как сам "отчет", так и итоговое число строк, вошедших в такой "отчет".


-- Создаем временную табличку
declare @t1 table ([Дата] varchar(50), [Номер] varchar(50), Тема varchar(max))

-- Добавляем в табличку все строки, полученные селектом
insert @t1
select CONVERT(varchar(10), t_CardDoc.f_dtStart, 103) 'Дата', t_CardDoc.f_AutoNumber 'Номер', t_CardDoc.f_Topic 'Тема'
from t_CardDoc
where t_CardDoc.f_ControlType=40
-- если нужно, отсортировываем данные
order by t_CardDoc.f_dtStart

-- Добавляем в ту же самую временную табличку завершающую строку с итоговым количеством документов, вошедших в отчет и фразой "Всего документов:"
insert @t1
select '','','Всего документов: '+CONVERT(varchar(10),(select count(*) from t_CardDoc where t_CardDoc.f_ControlType=40))
-- Поскольку результат count это всегда тип int, а записать мы его хотим в поле varchar, которое уже имеет текстовое значение, то нужно сделать конвертацию int в varchar

-- Апогей склейки, просто выводим все строки из временной таблички, которая уже содержит итоговое значение с количеством строк
select * from @t1


Картинка с результатом sql-запроса прилагается.

К сообщению приложен файл. Размер - 11Kb
5 июн 17, 13:43    [20540087]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Василиус,

Чем вам union не угодил то? Да и вообще такие задачи должны на клиенте решаться.
5 июн 17, 13:51    [20540152]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Remind
Василиус,

Чем вам union не угодил то? Да и вообще такие задачи должны на клиенте решаться.


Хм, а как вы "order by" будете внутри union применять? ;)
5 июн 17, 13:55    [20540185]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Василиус, ну не "внутри", а после
order by isnull([Дата], '2999-12-31')
5 июн 17, 14:01    [20540230]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Remind
Василиус, ну не "внутри", а после
order by isnull([Дата], '2999-12-31')


Да, вариант с union в целом тоже рабочий, можно и после, просто в моем реальном примере, 11 колонок и для order by используется 6 различных параметров и в случае, и если сортировка вдруг пойдет по колонке, в которой выводится итоговое значение, то последняя строка может оказаться не последней) Приведенный здесь пример сильно упрощен для наглядности) Конечно, такие вещи правильнее делать на клиенте, но только в том случае, если клиент такое позволяет, в моем случае клиент умеет отправить только 1 sql запрос и получить 1 результат его выполнения.

Ради интереса, кроме указанного варианта и аналогичного варианта с union кто-нибудь знает иные хитрые варианты, которые позволят вывести итог запроса типа "(10 row(s) affected)" в конце таблички и лучше за пределами таблички, в идеале, добавить необрамленную строку после таблицы?)
5 июн 17, 14:14    [20540303]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18326
Василиус
Remind
Василиус, ну не "внутри", а после
order by isnull([Дата], '2999-12-31')



Да, вариант с union в целом тоже рабочий, можно и после, просто в моем реальном примере, 11 колонок и для order by используется 6 различных параметров и в случае, и если сортировка вдруг пойдет по колонке, в которой выводится итоговое значение, то последняя строка может оказаться не последней) Приведенный здесь пример сильно упрощен для наглядности) Конечно, такие вещи правильнее делать на клиенте, но только в том случае, если клиент такое позволяет, в моем случае клиент умеет отправить только 1 sql запрос и получить 1 результат его выполнения.

Ради интереса, кроме указанного варианта и аналогичного варианта с union кто-нибудь знает иные хитрые варианты, которые позволят вывести итог запроса типа "(10 row(s) affected)" в конце таблички и лучше за пределами таблички, в идеале, добавить необрамленную строку после таблицы?)
А где в вашем примере сортировка?
select * from @t1 не гарантирует вам вывод в определенной сортировке.
5 июн 17, 14:39    [20540429]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Василиус,
автор
-- Апогей склейки,...


спрячьте этот апогей и больше никому не показывайте.
declare @t table (data date, number varchar(10))
insert @t select '20170101', 'sdsdf1'
insert @t select '20170102', 'asdsd3'
insert @t select '20170103', 'dfgdf'
insert @t select '20170104', 'sdsasddf1'
insert @t select '20170105', 'asdf'
insert @t select '20170106', 'asds'
insert @t select '20170107', 'asdas'
insert @t select '20170108', 'вапвп'
insert @t select '20170109', 'asd'
insert @t select '20170111', 'ccvbvb'

select data, number,  case when data is not null then null else tema end  tema
from 
(select *, count(*) tema 
from @t 
group by data, number
with rollup) a
where number is not null or data is null
5 июн 17, 14:51    [20540502]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Remind
Василиус, ну не "внутри", а после
order by isnull([Дата], '2999-12-31')


Проверено, такой вариант не работает, тут как минимум надо формат привести для правильной сортировки по дате, например так:

order by cast([Дата] as date)

В этом варианте строки сортируются по дате верно, но строка с итогами, увы, окажется сверху:

К сообщению приложен файл. Размер - 17Kb
5 июн 17, 14:55    [20540519]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Василиус,

Зачем вы храните дату в varchar это конечно не мое дело, но неужели не смогли сами додумать:
order by isnull(cast([Дата] as date), '2999-12-31')
5 июн 17, 15:05    [20540568]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
WarAnt
Василиус,
автор
-- Апогей склейки,...


спрячьте этот апогей и больше никому не показывайте.
declare @t table (data date, number varchar(10))
insert @t select '20170101', 'sdsdf1'
insert @t select '20170102', 'asdsd3'
insert @t select '20170103', 'dfgdf'
insert @t select '20170104', 'sdsasddf1'
insert @t select '20170105', 'asdf'
insert @t select '20170106', 'asds'
insert @t select '20170107', 'asdas'
insert @t select '20170108', 'вапвп'
insert @t select '20170109', 'asd'
insert @t select '20170111', 'ccvbvb'

select data, number,  case when data is not null then null else tema end  tema
from 
(select *, count(*) tema 
from @t 
group by data, number
with rollup) a
where number is not null or data is null


Хм, идея понятна, но для того, что бы вывести count зачем добавлять отдельную колонку tema? Весь смысл с том, что бы не плодить отдельных колонок) В моем случае "Тема" была просто очищена, т.к. в ней содержится конфиденциальная информация) Кроме того, если будет не 10, а 15000 строк, то такой вариант запроса не видится более дорогим? Подождем прятать апогей))

К сообщению приложен файл. Размер - 11Kb
5 июн 17, 15:06    [20540572]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Remind
Василиус,

Зачем вы храните дату в varchar это конечно не мое дело, но неужели не смогли сами додумать:
order by isnull(cast([Дата] as date), '2999-12-31')


Дату храним в smalldatetime)) то для целей вывода в отчете дату конвертим к тому виду, как юзерам надо: CONVERT(varchar(10), t_CardDoc.f_dtStart, 103), затем если используем union, то для целей сортировки нужно обратно конвертить к date. Издержки union)

Дудомал, не пошел isnull, потому написал)

К сообщению приложен файл. Размер - 19Kb
5 июн 17, 15:13    [20540601]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Василиус,

Василиус,

Естественно не пошел, у вас же не null, а пустая строка...
order by isnull(nullif(cast([Дата] as date), ''), '2999-12-31')
5 июн 17, 15:28    [20540667]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Deff
Василиус
-- если нужно, отсортировываем данные
order by t_CardDoc.f_dtStart

А где в вашем примере сортировка?
select * from @t1 не гарантирует вам вывод в определенной сортировке.

order by t_CardDoc.f_dtStart
в первом селекте почему не гарантирует сортировку по дате?)
5 июн 17, 15:29    [20540671]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Remind
Василиус,

Естественно не пошел, у вас же не null, а пустая строка...
order by isnull(nullif(cast([Дата] as date), ''), '2999-12-31')


Да, так робит! (like)

К сообщению приложен файл. Размер - 21Kb
5 июн 17, 15:32    [20540680]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18326
Василиус
Deff
пропущено...
А где в вашем примере сортировка?
select * from @t1 не гарантирует вам вывод в определенной сортировке.

order by t_CardDoc.f_dtStart
в первом селекте почему не гарантирует сортировку по дате?)
Итоговый селект у вас без сортировки.
И несмотря на ваши наблюдения, что записи выводятся в том же порядке, такой результат не гарантирован. В определенный момент записи могу выйти в другом порядке.
5 июн 17, 15:51    [20540766]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Deff
Василиус
пропущено...

order by t_CardDoc.f_dtStart
в первом селекте почему не гарантирует сортировку по дате?)
Итоговый селект у вас без сортировки.
И несмотря на ваши наблюдения, что записи выводятся в том же порядке, такой результат не гарантирован. В определенный момент записи могу выйти в другом порядке.

Да, согласен, есть вероятность того, что выполнение второго селекта может начаться не дождавшишь окончания первого (приходилось с таким сталкиваться), но проверяю на рабочей БД, пока всё ок, итоговая строчка выходит внизу, после строки-разделителя.=)

К сообщению приложен файл. Размер - 37Kb
5 июн 17, 16:05    [20540819]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Благодарю всех за реально конструктивную критику, но главный вопрос таки остался на повестке дня: есть ли в принципе иные варианты решения данной задачки?

Кроме того, если кому интересно, сам запрос несколько оптимизирован в части удешевления стоимости его выполнения, что бы не использовать дважды один и тот же селект из БД с теми же самыми условиями insert @t1 .., select count(*) from @t1)), т.е. подсчет количества записей делаем не из БД, а из @t1:
declare @t1 table ([Дата рег.] varchar(50), [Номер рег.] varchar(50), [Тема документа] varchar(max))  

insert @t1
select CONVERT(varchar(10), t_CardDoc.f_dtStart, 103), t_CardDoc.f_AutoNumber, t_CardDoc.f_Topic
from t_CardDoc
where t_CardDoc.f_ControlType=40
order by t_CardDoc.f_dtStart

insert @t1
select '','','Всего документов: '+CONVERT(varchar(10),(select count(*) from @t1))

select * from @t1

Однако, вот засада, проявилось таки то, о чем говорил(а?) Deff: при значительном количестве записей, оконечная строка полезла наверх(?!?) Это значит, что есть union что нет union, sql живет по своим никому не ведомым правилам и сортировку order by isnull надо таки обязательно ставить после завершающего селекту?

К сообщению приложен файл. Размер - 27Kb
6 июн 17, 16:17    [20543979]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Все последующие добавления в @t1 происходят в начало таблицы, вопреки порядку следования селектов. По логике, новые строки должны должны добавляться в конец таблицы и добавляются, когда количество записей не превышает 10 т. Чем вообще такое можно объяснить?

К сообщению приложен файл. Размер - 31Kb
6 июн 17, 16:37    [20544058]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
o-o
Guest
Василиус
Чем вообще такое можно объяснить?

конец света близок, как минимум
6 июн 17, 16:38    [20544064]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Василиус,

SQL Server "живет" по вполне четким правилам.

автор
Порядок, в котором строки возвращаются в результирующем наборе, не гарантируется, если не указано предложение ORDER BY.


В Вашем случае, вы указываете order by в момент insert'a в таблицу, что вообще не имеет никакого смысла.
6 июн 17, 16:41    [20544075]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Василиус,

автор
Хм, идея понятна, но для того, что бы вывести count зачем добавлять отдельную колонку tema? Весь смысл с том, что бы не плодить отдельных колонок)

Вам виднее зачем, она у вас на скрине появилась в виде желаемого формата вывода, уберите если не нужна, я показал лишь суть идеи, а не конечную реализацию задачи.

автор
В моем случае "Тема" была просто очищена, т.к. в ней содержится конфиденциальная информация)

Об этом надо было догадаться?

автор
Кроме того, если будет не 10, а 15000 строк, то такой вариант запроса не видится более дорогим? Подождем прятать апогей))

вы научились читать планы выполнения без построения самих планов, лучше самого сервера?
6 июн 17, 16:44    [20544084]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
Remind
В Вашем случае, вы указываете order by в момент insert'a в таблицу, что вообще не имеет никакого смысла.
"В данном случае не имеет". "Вообще" смысл бывает большой.
6 июн 17, 16:44    [20544085]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Василиус
Member

Откуда: Москва
Сообщений: 39
Remind
Василиус,

SQL Server "живет" по вполне четким правилам.

В Вашем случае, вы указываете order by в момент insert'a в таблицу, что вообще не имеет никакого смысла.


Хм, и в чем же заключаются эти самые "четкие правила"? Если в запросе не более 100 записей, то добавляем последующие строки снизу, а если более 100 строк, то лепим в начало? :))

К сообщению приложен файл. Размер - 32Kb
6 июн 17, 16:48    [20544105]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7758
Василиус,

у сервера нет любимого порядка строк. Нет ни "снизу" ни "сверху". Правила процитировали выше.
6 июн 17, 16:52    [20544125]     Ответить | Цитировать Сообщить модератору
 Re: Как в конце таблицы вывести итоговое значение строк  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7758
Гавриленко Сергей Алексеевич
Remind
В Вашем случае, вы указываете order by в момент insert'a в таблицу, что вообще не имеет никакого смысла.
"В данном случае не имеет". "Вообще" смысл бывает большой.

+1. Например для минимального протоколирования очень даже может иметь.
6 июн 17, 16:54    [20544134]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить