Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Написать запрос, выполняющий вставку данных в таблицу. Идентификатор должен вычисляться  [new]
dinimus
Member

Откуда:
Сообщений: 2
Имеется таблица @People. Необходимо написать запрос, выполняющий вставку данных в таблицу: поле Name - "Лампов", в поле ID - пропущенный идентификатор.
Иденитфикатор не должен быть задан явно значением "5", а должен вычисляться. Для поиска пропущенного идентификатора нельзя использовать курсоры и временные таблицы
В результирующей выборке необходимо отсортироваться по полю Name, добавив в отбор новое поле ID2 - со сквозной нумерацией строк (без курсора и промежуточных таблиц).

DECLARE @People AS TABLE
(
[id] INT NOT NULL, — идентификатор

[Name] NVARCHAR(255) —имя
)

/* Тестовая ситуация */
INSERT INTO @People
(
[id],
[Name]
)
VALUES
(1, 'Иванов')

, (2, 'Петров')

, (3, 'Сидоров')

, (4, 'Быков')

, (6, 'Тополь')

, (7, 'Зинин')
8 ноя 16, 13:30    [19870123]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос, выполняющий вставку данных в таблицу. Идентификатор должен вычисляться  [new]
aleks2
Guest
Спасем космос!

insert @People (id, Name)
  select max(id) + 1, 'Лампов' from @People with(tablockx);

  select *, id2 = row_number() over(order by Name) from @People;
8 ноя 16, 14:22    [19870422]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос, выполняющий вставку данных в таблицу. Идентификатор должен вычисляться  [new]
aleks2
Guest
Ой-ой...

insert @People (id, Name)
  select min(id) + 1, 'Лампов' from @People as t with(tablockx) 
    where not exists( select  * from @People where id = t.id + 1 );
8 ноя 16, 14:25    [19870442]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос, выполняющий вставку данных в таблицу. Идентификатор должен вычисляться  [new]
PaNik
Member

Откуда: Москва
Сообщений: 1143
INSERT INTO @People(id, [Name])
SELECT MIN(p.id) + 1, 'Лампов' FROM @People AS p WHERE NOT EXISTS (SELECT 1 FROM @People AS p2 WHERE p.id + 1 = p2.id)
8 ноя 16, 14:26    [19870443]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос, выполняющий вставку данных в таблицу. Идентификатор должен вычисляться  [new]
PaNik
Member

Откуда: Москва
Сообщений: 1143
aleks2,

на кнопке :-)
8 ноя 16, 14:26    [19870448]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос, выполняющий вставку данных в таблицу. Идентификатор должен вычисляться  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 119
А я обычно делаю так:
;WITH [CTE]
AS
(
    SELECT [Id]
          ,ROW_NUMBER() OVER(ORDER BY [Id]) AS [RowNumber]
      FROM @People
)
INSERT @People
      ([id]
      ,[Name])
SELECT ISNULL(MAX([Id]) + 1, 1)
      ,N'Лампов'
  FROM [CTE]
 WHERE [RowNumber] = [Id];


Задался вопросом, а что быстрее. Быстренько склепал таблицу:
CREATE TABLE [dbo].[Table_1]
(
	[id] int NOT NULL PRIMARY KEY,
	[Name] nvarchar(150) NULL,
)

И забубенил туда 9999998 записей подряд, пропустив идентификатор 5000000.
Далее смотрим на результат выполнения запроса
SET STATISTICS TIME ON;

;WITH [CTE]
AS
(
    SELECT [Id]
          ,ROW_NUMBER() OVER(ORDER BY [Id]) AS [RowNumber]
      FROM [dbo].[Table_1]
)
SELECT ISNULL(MAX([Id]) + 1, 1)
      ,N'Лампов'
  FROM [CTE]
 WHERE [RowNumber] = [Id];

 SELECT MIN(p.id) + 1, 'Лампов'
   FROM [dbo].[Table_1] AS p
  WHERE NOT EXISTS (SELECT 1 FROM [dbo].[Table_1] AS p2 WHERE p.id + 1 = p2.id)

У меня получилось так:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 1763 ms, elapsed time = 1765 ms.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 2683 ms, elapsed time = 2673 ms.
8 ноя 16, 15:17    [19870701]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос, выполняющий вставку данных в таблицу. Идентификатор должен вычисляться  [new]
o-o
Guest
Sybex
Задался вопросом, а что быстрее.
...
SET STATISTICS TIME ON;
...

надо было еще и set statistics io включить, ну или план посмотреть.
увидели бы, что в первом решении 1 скан таблицы, а во втором 2.
и чтений ровно в 2 раза больше
8 ноя 16, 15:41    [19870823]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос, выполняющий вставку данных в таблицу. Идентификатор должен вычисляться  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 119
o-o
надо было еще и set statistics io включить, ну или план посмотреть.

Да, сплоховал с IO. Надо было написать. А план то я глянул, естественно там было в два раза больше чтений и два скана для второго варианта. Кстати, по стоимости запроса первый 30%, второй 70%.
8 ноя 16, 16:23    [19871101]     Ответить | Цитировать Сообщить модератору
 Re: Написать запрос, выполняющий вставку данных в таблицу. Идентификатор должен вычисляться  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Sybex
А я обычно делаю так:
;WITH [CTE]
AS
(
    SELECT [Id]
          ,ROW_NUMBER() OVER(ORDER BY [Id]) AS [RowNumber]
      FROM @People
)
INSERT @People
      ([id]
      ,[Name])
SELECT ISNULL(MAX([Id]) + 1, 1)
      ,N'Лампов'
  FROM [CTE]
 WHERE [RowNumber] = [Id];
А если id в таблице не с 1 начинается?
Sybex
У меня получилось так:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 1763 ms, elapsed time = 1765 ms.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 2683 ms, elapsed time = 2673 ms.
Так могло получится, только если в вашем запросе был MIN, а не MAX.
+
use tempdb;
go

create table dbo.t (id int primary key);

insert into dbo.t
select top (1000000)
 row_number() over (order by (select 1)) 
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

insert into dbo.t
select top (1000000)
 1000002 + row_number() over (order by (select 1)) 
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

declare @id int;

print 'row_number() + min';
set statistics time on;
with a as
(
 select
  id, row_number() over (order by id) as rn
 from
  dbo.t
)
select
 @id = min(id)
from
 a
where
 a.id = a.rn
option
 (maxdop 1);
set statistics time off;

print 'row_number() + max';
set statistics time on;
with a as
(
 select
  id, row_number() over (order by id) as rn
 from
  dbo.t
)
select
 @id = max(id)
from
 a
where
 a.id = a.rn
option
 (maxdop 1);
set statistics time off;

print '"правильный" row_number()';
set statistics time on;
with a as
(
 select
  id, row_number() over (order by id) as rn
 from
  dbo.t
)
select
 @id = min(id)
from
 (select min(id) - 1 from dbo.t) b(min_id) cross join
 a
where
 a.id <> a.rn + b.min_id
option
 (maxdop 1);
set statistics time off;

print 'not exists()';
set statistics time on;
select
 @id = min(a.id)
from
 dbo.t a
where
 not exists(select 1 from dbo.t where id <> a.id + 1)
option
 (maxdop 1);
set statistics time off;
go

drop table dbo.t;
go

row_number() + min

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

row_number() + max

SQL Server Execution Times:
CPU time = 374 ms, elapsed time = 381 ms.

"правильный" row_number()

SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 447 ms.

not exists()

SQL Server Execution Times:
CPU time = 358 ms, elapsed time = 355 ms.
8 ноя 16, 16:38    [19871180]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить