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

Откуда: Moscow
Сообщений: 2761
Гость333
PlanB
Есть множество однотипных таблиц с наименованием mmyyy&"b1" (mm - месяц, yyyy - год), пример ниже (012008b1 и 012009b1).

Мне вот всегда интересно, зачем так именовать? :)
Разные года идут вперемешку — январь 2008, январь 2009, январь 2010, февраль 2008, февраль 2009, февраль 2010...
Почему не назвать yyyymm&"b1"?
я двумя руками за! это к Банку России вопросы
31 июл 13, 13:05    [14642395]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
PlanB
Glory
пропущено...

В первом сообщение как раз ваш REGN уникален
не уникален во множестве таблиц.

тогда стоит описать закономерность соединения всех таблиц по regn , т.е. если мы имеем несколько записей с одинаковыми regn но разными данными, то что должно попасть в результирующую таблицу.
31 июл 13, 13:12    [14642453]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
Glory
Member

Откуда:
Сообщений: 104760
PlanB
Glory
пропущено...

В первом сообщение как раз ваш REGN уникален
не уникален во множестве таблиц.

И как же соединение по уникальному ключу может дать неуникальный набор ?
31 июл 13, 13:17    [14642491]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
select 1 as regn, 38  as bal into #012008b1
union all select 2, 40 union all select 3, 42 union all select 9, 66

select 1 as regn, 38  as bal into #012009b1
union all select 2, 40 union all select 9, 66 union all select 10, 61;

select 1 as regn, 37  as bal into #012010b1
union all select 3, 38 union all select 10, 86 union all select 20, 99;


declare @tablename varchar(100)
create table _result (regn int )

declare circle cursor local for
select '#012008b1' as TableName 
union select '#012009b1'
union select '#012010b1'
-- все таблицы
open circle
fetch next from circle into @tablename
while @@fetch_status =0
	begin
		exec ('alter table _result add ' +	@tablename + ' int')
		exec ('INSERT INTO _result(regn,'+@tablename+')  select regn,bal from '+@tablename+' t '
		+' where not exists (select * from _result where regn = t.regn)')
		exec ('update r set r.'+@tablename+'=t.bal from _result r JOIN '+@tablename+' t '
		+' on r.regn = t.regn')
		fetch next from circle into @tablename
	end
close	circle

select * from _result
if OBJECT_ID('_result') is not null drop table _result
31 июл 13, 13:41    [14642690]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31488
Гость333
alexeyvg
в запросе ... синтаксическая ошибка

Где именно синтаксическая ошибка?
У join-ов закрувающие скобки лишние
31 июл 13, 14:05    [14642911]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31488
PlanB
alexeyvg, что-то не получается у него.. в смысле, я ожидал увидеть данные без повторений. корме того, считает очень долго, еще не извлек все
Я думаю, вы просто неправильно переписали мой запрос.

Вылоджите его сюда, только без редактирования, прямо как он выполняется.
31 июл 13, 14:07    [14642928]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31488
alexeyvg
Гость333
пропущено...

Где именно синтаксическая ошибка?
У join-ов закрувающие скобки лишние
А, там же написано FROM ((((((SELECT

Шедевр, конечно :-)
31 июл 13, 14:08    [14642941]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
Гость333
Member

Откуда:
Сообщений: 3683
alexeyvg,

Ваш запрос с фулджойнами всё равно не совсем корректен, надо типа такого:
SELECT
    COALESCE(b.regn, c.regn, d.regn, e.regn, f.regn, g.regn) as regn,
    b.dt as [012008b1], 
    c.dt as [012009b1], 
    d.dt as [012010b1], 
    e.dt as [012011b1], 
    f.dt as [012012b1], 
    g.dt as [012013b1]
FROM [dbo].[012008b1] b
  full join [dbo].[012009b1] c on b.regn = c.regn
  full join [dbo].[012010b1] d on d.regn = coalesce(b.regn, c.regn)
  full join [dbo].[012011b1] e on e.regn = coalesce(b.regn, c.regn, d.regn)
  full join [dbo].[012012b1] f on f.regn = coalesce(b.regn, c.regn, d.regn, e.regn)
  full join [dbo].[012013b1] g on g.regn = coalesce(b.regn, c.regn, d.regn, e.regn, f.regn)

Ну и сам подход — джойн 40 таблиц — далеко не самый оптимальный.
31 июл 13, 14:21    [14643040]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
сургей
Member

Откуда:
Сообщений: 5
select *
from
(
select *,'012008b1' [name] from dbo.[012008b1]
union
select *,'012009b1' from dbo.[012009b1]
union
select *,'012010b1' from dbo.[012010b1]
) t
pivot
(min(t.bal)
for [name] in ([012008b1],[012009b1],[012010b1])
)pv
31 июл 13, 16:00    [14643822]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
сургей
Member

Откуда:
Сообщений: 5
Сорри дубликат Гость333. Не заметил!!!
31 июл 13, 16:12    [14643918]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
сургей
Member

Откуда:
Сообщений: 5
;with reg
as
(select regn from dbo.[012008b1] 
union
select regn from dbo.[012009b1]
union
select regn from dbo.[012010b1]
)
select 
rg.*
,t1.[012008b1]
,t2.[012009b1]
,t3.[012010b1]
from reg rg
left join (select regn,bal [012008b1] from dbo.[012008b1]) as t1 on t1.regn=rg.regn
left join (select regn,bal [012009b1] from dbo.[012009b1]) as t2 on t2.regn=rg.regn
left join (select regn,bal [012010b1] from dbo.[012010b1]) as t3 on t3.regn=rg.regn
31 июл 13, 16:58    [14644256]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31488
Гость333
Ваш запрос с фулджойнами всё равно не совсем корректен, надо типа такого:
Да, лоханулся, забыл про условия :-(
Гость333
Ну и сам подход — джойн 40 таблиц — далеко не самый оптимальный.
Да, может PIVOT и лучше.
31 июл 13, 17:15    [14644357]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2761
Добрый день!

По причине того, что ни один из запросов, опубликованных выше не выдавал того, что я ждал, я принял решение сократить набор данных с 80 таблиц до 21 (на тесты взял квартальные данные) и разбил все на два этапа.

1) при помощи запроса под спойлером "1" (гениальный код, не правда ли) я создал таблицу [REGN_DIST] с единственным столбцом REGN - всего 1090 строк.

REGN
1
5
18
...
+ 1
--КВАРТАЛЬНЫЕ ДАННЫЕ ПО БАНКАМ--
SELECT DISTINCT a.regn FROM
(
select regn, dt from [dbo].[122007b1] union all --2008-01-01
select regn, dt from [dbo].[122008b1] union all --2009-01-01
select regn, dt from [dbo].[122009b1] union all --2010-01-01
select regn, dt from [dbo].[122010b1] union all --2011-01-01
select regn, dt from [dbo].[122011b1] union all --2012-01-01
select regn, dt from [dbo].[122012b1] union all --2013-01-01 <--

select regn, dt from [dbo].[032008b1] union all --2008-04-01
select regn, dt from [dbo].[032009b1] union all --2009-04-01
select regn, dt from [dbo].[032010b1] union all --2010-04-01
select regn, dt from [dbo].[032011b1] union all --2011-04-01
select regn, dt from [dbo].[032012b1] union all --2012-04-01

select regn, dt from [dbo].[062008b1] union all --2008-07-01
select regn, dt from [dbo].[062009b1] union all --2009-07-01
select regn, dt from [dbo].[062010b1] union all --2010-07-01
select regn, dt from [dbo].[062011b1] union all --2011-07-01
select regn, dt from [dbo].[062012b1] union all --2012-07-01

select regn, dt from [dbo].[092008b1] union all --2008-10-01
select regn, dt from [dbo].[092009b1] union all --2009-10-01
select regn, dt from [dbo].[092010b1] union all --2010-10-01
select regn, dt from [dbo].[092011b1] union all --2011-10-01
select regn, dt from [dbo].[092012b1] --2012-10-01
) a 
ORDER BY 1

2) я попытался обычными джоинами прикрапить к [REGN_DIST] остальные таблицы. Итого кодом под спойлером 2 выгружается то, что надо за 23 сек. Спойлер 3 содержит код, который мой сервер переварить уже не может (терпение лопнуло через 7 минут). что делать-то?? мне надо туда 18 таблиц приписать, а он три не может.

regn2008-01-012008-04-01
111
2NULLNULL
333
5NULLNULL
18NULLNULL
212121
232323
+ 2
SELECT DISTINCT
a.regn, 
b.regn AS '2008-01-01',
c.regn AS '2008-04-01'
FROM [dbo].[REGN_DIST] a
LEFT OUTER JOIN [dbo].[122007b1] b ON a.regn = b.regn
LEFT OUTER JOIN [dbo].[032008b1] c ON a.regn = c.regn
ORDER BY 1
+ 3
SELECT DISTINCT
a.regn, 
b.regn AS '2008-01-01',
c.regn AS '2008-04-01',
d.regn AS '2008-07-01'
FROM [dbo].[REGN_DIST] a
LEFT OUTER JOIN [dbo].[122007b1] b ON a.regn = b.regn
LEFT OUTER JOIN [dbo].[032008b1] c ON a.regn = c.regn
LEFT OUTER JOIN [dbo].[062008b1] d ON a.regn = d.regn
ORDER BY 1
Всем заранее спасибо за ответы!
5 авг 13, 14:26    [14664118]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
ПЕНСИОНЕРКА
Member

Откуда: Владимирская обл
Сообщений: 4728
PlanB,

40 ТАБЛИЦ
1190 REGN
-----------
а по скольку записей в таблицах
каковы значения regn-max /regn-min
5 авг 13, 15:48    [14664871]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Небольшое дополнение к ранее высказанным советам (предварительная аггрегация):
-- if object_id('tempdb..#012008b1') is not null drop table #012008b1,#012009b1,#012010b1
if object_id('tempdb..#012008b1') is null begin
   create table #012008b1 (RegN int, Bal money);
   create index #012008b1_idx on #012008b1(RegN) include(Bal);
   insert #012008b1 values(1,10),(2,10),(2,5);

   create table #012009b1 (RegN int, Bal money);
   create index #012009b1_idx on #012009b1(RegN) include(Bal);
   insert #012009b1 values(1,15),(3,20),(4,20);

   create table #012010b1 (RegN int, Bal money);
   create index #012010b1_idx on #012010b1(RegN) include(Bal);
   insert #012010b1 values(2,10),(2,5),(2,7),(4,30);
end

select bank,
   RegN,d0801[2008-01],d0901[2009-01],d1001[2010-01]
from(
   select 'Название банка1', 'd0801', RegN, sum(bal) from #012008b1 group by RegN union all
   select 'Название банка1', 'd0901', RegN, sum(bal) from #012009b1 group by RegN union all
   select 'Название банка1', 'd1001', RegN, sum(bal) from #012010b1 group by RegN 
   )u(bank,dt,RegN,bal)
pivot(sum(bal) for dt in (d0801,d0901,d1001))p
order by bank,RegN
bankRegN2008-012009-012010-01
Название банка1110.000015.0000NULL
Название банка1215.0000NULL22.0000
Название банка13NULL20.0000NULL
Название банка14NULL20.000030.0000
5 авг 13, 15:50    [14664889]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2761
ПЕНСИОНЕРКА
PlanB,

40 ТАБЛИЦ
1190 REGN

-----------
а по скольку записей в таблицах
каковы значения regn-max /regn-min


REGN - это четырехзначный номер. изменяется от 1 до ~3500
Колличество строк в таблицах от 134 тыс в самой ранней до 170 тыс. в самой поздней.
Колличество столбцов - по 18 штук
5 авг 13, 16:03    [14664981]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
Alexander Us
Member

Откуда:
Сообщений: 1133
PlanB,

А Вам действительно надо разворачивать (PIVOT) таблицы непосредственно на SQL сервере ?

Если конечная цель это: отчёт в Экселе
и
Ваши юзеры используют версию Элсель от 2007 и выше,
посмотрите в сторону бесплатной библиотеки EPPlus.
Там легко делается (в Экселе) пивот.

В таком случае на SQL Server будет достаточно констрцукции вида
select a,b,c, 'tab1' Tab from tab1
union
select a,b,c, 'tab2' Tab from tab2
union
select a,b,c, 'tab3' Tab from tab3
5 авг 13, 16:33    [14665130]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
ПЕНСИОНЕРКА
Member

Откуда: Владимирская обл
Сообщений: 4728
PlanB,

REGN 012008b1 012009b1
1 38 38
2 40 40
3 42 null
9 66 66
10 null 61


вначале было показано 1 столбец на таблицу
теперь-----------------------------------------18

что еще
5 авг 13, 17:11    [14665356]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
ПЕНСИОНЕРКА
Member

Откуда: Владимирская обл
Сообщений: 4728
PlanB,

вариант из а2007

Код1regnbal
21100
32200
43300
59900
t2
Код2regnbal
21100
32200
43300
59900
6212100
t3
Код3regnbal
21100
32200
43300
59900
6313100
t4
Код4regnbal
21100
32200
43300
59900
6414100
7424200
t5
Код5regnbal
21100
32200
43300
59900
641100
7515100
tregn
Код_ tregn regnz1z2z3z4z5
421100100100100100
432200200200200200
443300300300300300
459900900900900900
46212100
47313100
48414100100
49424200
50515100


Option Compare Database
Option Explicit

Dim dbs As DAO.Database
Dim rst1r As DAO.Recordset
Dim rst1w As DAO.Recordset
Dim jtab, j1, j1k, j2, j2k, s1, s2
Dim xm, x1n, x1k
Sub m130805_1603()
xm = Array("t1", "t2", "t3", "t4", "t5")
x1n = LBound(xm, 1)
x1k = UBound(xm, 1)

Set dbs = CurrentDb
s1 = "delete * from tregn"
DoCmd.RunSQL s1
Set rst1w = dbs.OpenRecordset("select * from tregn")


Do While x1n <= x1k
s1 = "select regn as zregn,sum(bal) as zbal from " & xm(x1n) & " group by regn"
Debug.Print x1n, s1

Set rst1r = dbs.OpenRecordset(s1)
Do While rst1r.EOF = False
s2 = rst1r!zregn
Debug.Print s2;
rst1w.FindFirst "regn=" & s2
If rst1w.NoMatch = True Then
rst1w.AddNew
rst1w.Fields("regn") = s2
rst1w.Fields(x1n + 2).Value = rst1r.Fields("zbal")
rst1w.Update
Else
rst1w.Edit
'rst1!regn = s2
rst1w.Fields(x1n + 2).Value = rst1r.Fields("zbal")
rst1w.Update
End If
rst1r.MoveNext
Loop
rst1r.Close
x1n = x1n + 1
Loop
rst1w.Close
Set rst1r = Nothing
Set rst1w = Nothing
Set dbs = Nothing

'rstEmployees.FindFirst "HireDate > #" & Format(mydate, 'm-d-yy' ) & "#"


End Sub
5 авг 13, 17:20    [14665409]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2761
Alexander Us
PlanB... посмотрите в сторону бесплатной библиотеки EPPlus.
боюсь excel такое не переварит. более того, у меня ощущение, что сервер даже не сведет все таблицы юнионами
6 авг 13, 09:27    [14667454]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
Гость333
Member

Откуда:
Сообщений: 3683
PlanB
ни один из запросов, опубликованных выше не выдавал того, что я ждал

Тема несоответствия ожиданиям не раскрыта. Почему, например, не подошёл запрос с union all и pivot?
6 авг 13, 09:41    [14667538]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2761
Гость333
PlanB
ни один из запросов, опубликованных выше не выдавал того, что я ждал

Тема несоответствия ожиданиям не раскрыта. Почему, например, не подошёл запрос с union all и pivot?
потому, что у меня зависает сервак
6 авг 13, 11:15    [14668104]     Ответить | Цитировать Сообщить модератору
 Re: перечисление множества таблиц в запросе  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
PlanB
Гость333
пропущено...

Тема несоответствия ожиданиям не раскрыта. Почему, например, не подошёл запрос с union all и pivot?
потому, что у меня зависает сервак
Можете показать предполагаемый план выполнения этого запроса?
6 авг 13, 11:23    [14668165]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Microsoft SQL Server Ответить