Создание временной таблицы

@Unston · Регистрация: 27.01.2013

Студворк — интернет-сервис помощи студентам

Здравствуйте.
Есть таблица с полями А,Б оба int. Значения А может повторяться несколько раз, но не более 3х. В Б все значения различны. Нужно в Т-SQL создать временную таблицу из четырех колонок, в которой в первой колонке бы были только уникальные значения, во второй, третьей, четвертой соответсвующие им значения Б. Если соответствующих значений меньше 3х тогда просто заполнялся бы НУЛЛ.
A1 Б1
А2 Б3
А1 Б2
A2 Б4
А2 Б5

Нужно получить:

А1 Б1 Б2 НУЛЛ
А2 Б3 Б4 Б5

Подскажите в каком направление двигаться.

@cygapb-007 · 27.01.2013, 11:45

можно попробовать select...for xml и затем парсинг xml

Добавлено через 9 часов 53 минуты

Сообщение от Unston

Есть таблица с полями А,Б оба int. Значения А может повторяться несколько раз, но не более 3х. В Б все значения различны. Нужно в Т-SQL создать временную таблицу из четырех колонок, в которой в первой колонке бы были только уникальные значения, во второй, третьей, четвертой соответсвующие им значения Б.

Что-то вроде

T-SQL
1
2
3
4
5
6
select a.A, b.Bxml
from (select distinct t.A from tab) a
cross apply (
   select t.B as Bxml
   from tab t where t.A = a.A
   for xml raw )b

а дальше как тут

Ну или еще проще

T-SQL
1
2
3
4
5
6
7
8
9
10
declare @t table(A int, B int, N int)
insert into @t
select t.A, t.B
   ROW_NUMBER() OVER(PARTITION BY t.A ORDER BY t.B)
 
select t.A, t.B, t2.B, t3.B
from @t t
left join @t t2 on t2.A=t.A and t2.N=2
left join @t t3 on t3.A=t.A and t3.N=3
where t.N=1

@Unston · 27.01.2013, 22:18 **[ТС]**

спасибо. попробую.

@Devil_FoX · 28.01.2013, 11:52

cygapb-007, эм. А зачем всё делать через кхм?
Есть специальные временные таблицы
http://msdn.microsoft.com/ru-r... .100).aspx

T-SQL
1
2
3
4
5
6
7
8
9
if object_id('tempdb..#tabl') is not null drop table #tabl  -- проверка на существование таблицы
create table #tabl -- создание временной таблицы
    (A int,
    B int,
    C int)
 
insert into #table -- внос инфы во временную таблицу
select
.....

@cygapb-007 · 28.01.2013, 15:31

Сообщение от Devil_FoX

cygapb-007, эм. А зачем всё делать через кхм?
Есть специальные временные таблицы
http://msdn.microsoft.com/ru-r... .100).aspx

Через кхм - это через @ ? Напомню, что правильное обозначение для кхм - ¤, или "солнышко", если вы играете в преф, например.
В свою очередь тоже могу подкинуть несколько ссылок, например эту: DECLARE @local_variable (Transact-SQL), или вот эту:INF: Frequently Asked Questions - SQL Server 2000 - Table Variables.
Если коротко - то преимущества и недостатки @ по сравнению с # отлично изложены вот здесь. Приведу одну цитату:

Общие рекомендации Microsoft, относительно использования табличных переменных таковы: "Используйте их везде, где это возможно, кроме тех случаев, когда у вас хранятся значительные объёмы данных, и присутствует повторное использование таблиц"

@iap · 28.01.2013, 16:15

Сообщение от cygapb-007

Если коротко - то преимущества и недостатки @ по сравнению с # отлично изложены вот здесь.

Чего-то Денис Резник по данной ссылке напоминает чайника.
Одно утверждение

"На табличных переменных нельзя создавать некластерные индексы"

чего стоит!
А вот тут, например, разве не создаются аж два некластерных индекса?!

T-SQL
1
DECLARE @T TABLE (ID INT NOT NULL IDENTITY PRIMARY KEY NONCLUSTERED, X INT, UNIQUE NONCLUSTERED (X,ID));

@cygapb-007 · 28.01.2013, 16:33

Я так понимаю, по поводу преимуществ возражений не последовало))?

А если учесть дату публикации (Опубліковані 31-01-2010) - то может так оно и было...
просто первая попавшаяся ссылка в поисковике...

@Devil_FoX · 28.01.2013, 17:43

cygapb-007, ну с параметром нет возможности работать в последствии. он правильно не воспринимает команды Delete, Update и т.д.
+ я работаю от миллионников до десятков миллиардов строк - а параметр вроде бы помещает определённое количество строк.

@cygapb-007 · 28.01.2013, 17:57

Сообщение от Devil_FoX

cygapb-007, ну с параметром нет возможности работать в последствии. он правильно не воспринимает команды Delete, Update и т.д.
+ я работаю от миллионников до десятков миллиардов строк - а параметр вроде бы помещает определённое количество строк.

В смысле не параметр, а табличная переменная? Почему же, @table прекрасно понимает и Delete, и Update, и Insert. А поскольку @table хранится в той же TempDB, то и по размеру ограничена только размером жесткого диска. В приводимой сслылке ведь написано, что

Табличные переменные не содержат статистику
Табличные переменные не могут использоваться в INSERT EXEC или SELECT INTO
Запросы, изменяющие табличные переменные, не создают параллельных планов выполнения запроса

Первый из указанных по ссылке недостатков был успешно опровергнут, чему я очень рад (хотя имелась в виду скорее всего невозможность "alter table @"), а вот последний - действительно может убедить в необходимости (в этом конкретном случае) использовать # вместо @
Но опять же

в каждом конкретном случае нужно смотреть и пробовать что вам больше подходит

Например,

T-SQL
1
2
3
4
5
6
7
8
9
10
declare @BAZSPEC TABLE (
    [CUDA] [varchar] (25) COLLATE Cyrillic_General_CI_AS NULL CHECK ([CUDA] > '') ,
    [PRR] [varchar] (1) COLLATE Cyrillic_General_CI_AS NULL ,
    [PRU] [varchar] (1) COLLATE Cyrillic_General_CI_AS NULL ,
    [CHTO] [varchar] (25) COLLATE Cyrillic_General_CI_AS NULL CHECK ([CHTO] > ''),
    [KVO] [float] NULL ,
    [TABN] [int] NULL DEFAULT (0),
    [DAT] [smalldatetime] NULL, 
    check ( [CUDA] <> [CHTO])
    )

прекрасно обругала меня при попытке вставки пустых строк или дубликатов согласно check-ам

(пример c sql.ru)

@Devil_FoX · 28.01.2013, 18:31

cygapb-007, No comments
http://msdn.microsoft.com/ru-r... 75010.aspx

Рекомендации
Не используйте табличные переменные для хранения больших объемов данных (более 100 строк). Плановые запросы могут оказаться неоптимальными или нестабильными при использовании табличных переменных с большим объемом данных. Попробуйте переписать такие запросы, чтобы они использовали временные таблицы или воспользуйтесь указанием запроса USE PLAN с тем, чтобы обеспечить использование оптимизатором существующего плана запроса, который хорошо работает в выбранном сценарии.

Добавлено через 3 минуты
P.S. По своему опыту знаю - автора книг очень часто не правы и у них очень много ошибок, самое качественное вникание в тему это прочитать минимум 3 независимых источника и потом ещё самому поэкспериментировать с данными.

@iap · 28.01.2013, 20:02

Сервер, строя план выполнения запроса с участием табличной переменной, всегда полагает,
что в этой переменной одна запись! Статистики-то нет!
Можно себе представить качество этого плана, если в наличии на самом деле миллион записей.

Однако, есть и плюсы. Например, если надо в транзакции сохранить нечто для того,
чтобы после отката получить это "нечто", то проще всего воспользоваться табличной переменной -
она же в откате не участвует!

@cygapb-007 · 29.01.2013, 12:08

230к строк... Порядок результатов одинаков
Там, где ожидается 1 строка результата, # всегда отстает.

Кликните здесь для просмотра всего текста

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
if OBJECT_ID('tempdb..#msg','U') is not null drop table #msg
create table #msg (
    message_id int, language_id smallint, severity tinyint, is_event_logged bit, 
    text nvarchar(100), hash_text as checksum(text) persisted,
    primary key(message_id,language_id))
create index #msg_hash on #msg (hash_text)
create index #msg_text on #msg (text)
 
insert into #msg
SELECT m.message_id, m.language_id, m.severity, m.is_event_logged, left(m.text,100)--, CHECKSUM(left(m.text,100))
    FROM sys.messages m --where m.language_id in (1049, 1033,1031)
 
declare @msg table (
    message_id int, language_id smallint, severity tinyint, is_event_logged bit, 
    text nvarchar(100), hash_text as checksum(text) persisted,
    primary key(message_id,language_id,text))
insert @msg select message_id, language_id, severity, is_event_logged, text  from #msg
--select * from #msg order by len(text)
 
declare @timing table(id int /*identity*/, timer datetime)
insert @timing values (1,getdate())
SELECT * FROM @msg where hash_text=CHECKSUM('уведомление') and text = 'уведомление'
insert @timing values (2,getdate())
SELECT * FROM #msg where hash_text=CHECKSUM('уведомление') and text = 'уведомление'
insert @timing values (3,getdate())
SELECT * FROM @msg where text like '%уведомление%'
insert @timing values (4,getdate())
SELECT * FROM #msg where text like '%уведомление%'
insert @timing values (5,getdate())
SELECT * FROM @msg where text = 'уведомление'
insert @timing values (6,getdate())
SELECT * FROM #msg where text = 'уведомление'
insert @timing values (7,getdate())
SELECT * FROM @msg where message_id=14210 and language_id=1049
insert @timing values (8,getdate())
SELECT * FROM #msg where message_id=14210 and language_id=1049
insert @timing values (9,getdate())
;with delta as (
    select t.id, t.timer start, n.timer stop, DATEDIFF(mcs,t.timer,n.timer) delta
        from @timing t
        join @timing n on n.id=t.id+1
    )
select *, [as#].delta-[as@].delta [#-@]
    from delta [as@] 
    join delta [as#] on [as#].id=[as@].id+1
    where [as@].id%2=1

Code
1
2
3
4
5
6
id          start                   stop                    delta       id          start                   stop                    delta       #-@
----------- ----------------------- ----------------------- ----------- ----------- ----------------------- ----------------------- ----------- -----------
1           2013-01-29 09:22:09.717 2013-01-29 09:22:09.757 40000       2           2013-01-29 09:22:09.757 2013-01-29 09:22:11.783 2026000     1986000
3           2013-01-29 09:22:11.783 2013-01-29 09:22:14.430 2646000     4           2013-01-29 09:22:14.430 2013-01-29 09:22:16.777 2346000     -300000
5           2013-01-29 09:22:16.777 2013-01-29 09:22:16.817 40000       6           2013-01-29 09:22:16.817 2013-01-29 09:22:17.007 190000      150000
7           2013-01-29 09:22:17.007 2013-01-29 09:22:17.067 60000       8           2013-01-29 09:22:17.067 2013-01-29 09:22:17.443 376000      316000

Добавлено через 7 минут
* порядок результата на отборе 13 строк из 230К - одинаков

Добавлено через 2 часа 29 минут
(Конечно, именно в этом конкретном случае :-))

Новые блоги и статьи Все статьи Все блоги /
Отчёт о спецтехнике находящейся в ремонте Maks 20.04.2026 Отчёт из решения ниже размещен в конфигурации КА2. Задача: отобразить спецтехнику, которая на данный момент находится в ремонте. Есть нетиповой документ "Заявка на ремонт спецтехники" который. . .	Памятка для бота и "визитка" для читателей "Semantic Universe Layer (Слой семантической вселенной)" Hrethgir 19.04.2026 Сгенерировано для краткого описания по случаю сборки и компиляции скелета серверного приложения. И пусть после этого скажут, что статьи сгенерированные AI - туфта и не интересно. И это не реклама -. . .	Запрет удаления строк ТЧ документа при определенном условии Maks 19.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "Аккумуляторы", разработанного в конфигурации КА2. У данного документа есть ТЧ, в которой в зависимости от прав доступа. . .	Модель заражения группы наркоманов alhaos 17.04.2026 Условия задачи сформулированы тут Суть: - Группа наркоманов из 10 человек. - Только один инфицирован ВИЧ. - Колются одной иглой. - Колются раз в день. - Колются последовательно через. . .
Мысли в слух. Про "навсегда". kumehtar 16.04.2026 Подумалось тут, что наверное очень глупо использовать во всяких своих установках понятие "навсегда". Это очень сильное понятие, и я только начинаю понимать край его смысла, не смотря на то что давно. . .	My Business CRM MaGz GoLd 16.04.2026 Всем привет, недавно возникла потребность создать CRM, для личных нужд. Собственно программа предоставляет из себя базу данных клиентов, в которой можно фиксировать звонки, стадии сделки, а также. . .	Знаешь почему 90% людей редко бывают счастливыми? kumehtar 14.04.2026 Потому что они ждут. Ждут выходных, ждут отпуска, ждут удачного момента. . . а удачный момент так и не приходит.	Фиксация колонок в отчете СКД Maks 14.04.2026 Фиксация колонок в СКД отчета типа Таблица. Задача: зафиксировать три левых колонки в отчете. Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка) / / . . .

@Unston 0 / 0 / 0 Регистрация: 27.01.2013 Сообщений: 20

	Создание временной таблицы 27.01.2013, 01:29. Показов 51179. Ответов 11 Метки нет (Все метки) Здравствуйте. Есть таблица с полями А,Б оба int. Значения А может повторяться несколько раз, но не более 3х. В Б все значения различны. Нужно в Т-SQL создать временную таблицу из четырех колонок, в которой в первой колонке бы были только уникальные значения, во второй, третьей, четвертой соответсвующие им значения Б. Если соответствующих значений меньше 3х тогда просто заполнялся бы НУЛЛ. A1 Б1 А2 Б3 А1 Б2 A2 Б4 А2 Б5 Нужно получить: А1 Б1 Б2 НУЛЛ А2 Б3 Б4 Б5 Подскажите в каком направление двигаться. 0

@Unston 0 / 0 / 0 Регистрация: 27.01.2013 Сообщений: 20
	27.01.2013, 22:18 [ТС]
	спасибо. попробую. 0

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	28.01.2013, 16:33
	Я так понимаю, по поводу преимуществ возражений не последовало))? А если учесть дату публикации (Опубліковані 31-01-2010) - то может так оно и было... просто первая попавшаяся ссылка в поисковике... 0

@Devil_FoX 168 / 142 / 1 Регистрация: 01.04.2010 Сообщений: 474
	28.01.2013, 17:43
	cygapb-007, ну с параметром нет возможности работать в последствии. он правильно не воспринимает команды Delete, Update и т.д. + я работаю от миллионников до десятков миллиардов строк - а параметр вроде бы помещает определённое количество строк. 0

@iap 1116 / 761 / 183 Регистрация: 27.11.2009 Сообщений: 2,272
	28.01.2013, 20:02
	Сервер, строя план выполнения запроса с участием табличной переменной, всегда полагает, что в этой переменной одна запись! Статистики-то нет! Можно себе представить качество этого плана, если в наличии на самом деле миллион записей. Однако, есть и плюсы. Например, если надо в транзакции сохранить нечто для того, чтобы после отката получить это "нечто", то проще всего воспользоваться табличной переменной - она же в откате не участвует! 1