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

Откуда:
Сообщений: 229
Добрый день.
Мне нужно чтобы поле в таблице заполнялось автоматически при вставке новой записи в таблицу.
Поле должно формироваться как "дата+число", например, для сегодняшнего дня это будет:
21122011_1, 21122011_2, 21122011_3 и т.д. (т.е. слитно число, месяц, год, а затем число, которое должно увеличиваться последовательно для данного дня).
Как такое сделать? Как взять маску даты, допустим понятно, а что с числом?
21 дек 11, 14:39    [11803413]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
iap
Member

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

это надо только в SELECTе на момент выборки или чтобы хранилось?
Например, удаляете запись за какую-нибудь дату, и значение этого поля у оставшихся не может меняться?
Есть ли в таблице поле IDENTITY?
21 дек 11, 15:12    [11803702]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
Boneshock
Member

Откуда:
Сообщений: 229
iap,
Это нужно при вставке, т.е. вставили запись, а в это поле значение подставилось автоматически по шаблону что я написал.
автор
Есть ли в таблице поле IDENTITY?

есть
автор
Например, удаляете запись за какую-нибудь дату, и значение этого поля у оставшихся не может меняться?

не должно
21 дек 11, 15:36    [11803931]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
Jovanny
Member

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

Что-то типа этого:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U'))
DROP TABLE [dbo].[Table_1]

CREATE TABLE [dbo].[Table_1](
	[MyString] [varchar](20) NULL
)
DECLARE @i int = 0

WHILE @i<100
BEGIN
	INSERT INTO dbo.Table_1 (MyString)
	SELECT REPLACE(CONVERT(varchar(20), GETDATE(), 103), '/', '') + '_' +  
	CONVERT(varchar(10), ISNULL(MAX(CONVERT(int, SUBSTRING(MyString, 10, 10))) + 1, 1))
	FROM [WebCrawler3DW].[dbo].[Table_1]

SET @i += 1
END

SELECT * FROM dbo.Table_1


Только если вставляться будет несколько строк в одном операторе, то, наверное , триггер INSTEAD OF надо писать.
21 дек 11, 16:37    [11804523]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Jovanny
Boneshock,

Что-то типа этого:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U'))
DROP TABLE [dbo].[Table_1]

CREATE TABLE [dbo].[Table_1](
	[MyString] [varchar](20) NULL
)
DECLARE @i int = 0

WHILE @i<100
BEGIN
	INSERT INTO dbo.Table_1 (MyString)
	SELECT REPLACE(CONVERT(varchar(20), GETDATE(), 103), '/', '') + '_' +  
	CONVERT(varchar(10), ISNULL(MAX(CONVERT(int, SUBSTRING(MyString, 10, 10))) + 1, 1))
	FROM [WebCrawler3DW].[dbo].[Table_1]

SET @i += 1
END

SELECT * FROM dbo.Table_1


Только если вставляться будет несколько строк в одном операторе, то, наверное , триггер INSTEAD OF надо писать.
Что за ужасы?
Пока этот цикл будет работать, кто-нибудь тоже попытается вставить - и что будет с максимумом?
Делать надо в триггере FOR INSERT и опираться на поле IDENTITY в inserted. IMHO

Да и цикл построчный нехарактерен для SQL...
21 дек 11, 16:47    [11804604]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
libru
Member

Откуда:
Сообщений: 877
use tempdb
go
create table t(i1 int identity,
n varchar(10),
dd datetime not null default getdate(),
i2 as convert(varchar(100), dd, 112) + '_' + convert(varchar(100), i1) persisted)
go
insert t(n) values('aaaaa')
go
select i1,i2 from t
go
drop table t
go
21 дек 11, 16:55    [11804693]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
libru
Member

Откуда:
Сообщений: 877
не, не то
21 дек 11, 17:03    [11804768]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
Jovanny
Member

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

Уговорили,

INSERT INTO dbo.Table_1 (MyString) WITH(TABLOCK)
	SELECT REPLACE(CONVERT(varchar(20), GETDATE(), 103), '/', '') + '_' +  
	CONVERT(varchar(10), ISNULL(MAX(CONVERT(int, SUBSTRING(MyString, 10, 10))) + 1, 1))
	FROM [dbo].[Table_1]


Я в общем только идею подал, а при множественной вставке, наверное, такой цикл нужно либо в курсор вставить, либо CTE и оконные функции использовать. И всё это в триггер можно INSTEAD OF, а можно AFTER INSERT.
21 дек 11, 17:09    [11804836]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Boneshock
а затем число, которое должно увеличиваться последовательно для данного дня


Если опираться на IDENTITY, то нужно вычислять максимальный IDENTITY за последний день и учитывать возможные пропуски в последовательности.
21 дек 11, 17:14    [11804903]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
libru
Member

Откуда:
Сообщений: 877
use tempdb
go
create table t(i1 int identity,
n varchar(10),
dd datetime not null default getdate())
go
create function f() returns int as
begin
return isnull((select max(i1) from t where dd < convert(date, getdate())), 0)
end
go
alter table t add l int not null default dbo.f()
go
alter table t add i2 as convert(varchar(100), dd, 112) + '_' + convert(varchar(100), i1 - l) persisted
go
insert t(n, dd) values('aaaaa', '20111220')--yestoday
insert t(n) values('bbbbbb')
go
select i1,i2 from t
go
drop table t
go
drop function f
go
21 дек 11, 17:27    [11805023]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Вот такое решение не устроит?
USE tempdb;
SET NOCOUNT ON;

IF OBJECT_ID(N'T','U') IS NOT NULL DROP TABLE T;
GO
CREATE TABLE T
(
 ID INT NOT NULL IDENTITY,
 D DATE NOT NULL CONSTRAINT DefDinT DEFAULT CURRENT_TIMESTAMP,
 DayID AS REPLACE(CONVERT(VARCHAR(10),D,104),'.','')+'_'+CAST(ID AS VARCHAR)
);
GO
INSERT T DEFAULT VALUES;
INSERT T DEFAULT VALUES;
INSERT T DEFAULT VALUES;
INSERT T DEFAULT VALUES;
SELECT * FROM T;
Правда, номер в вычисляемом поле не будет начинаться с единицы каждый день. Так сказать, сквозной он получается.
Зато в остальном будет работать очень неплохо.
В своём роде оптимальный вариант
21 дек 11, 17:33    [11805060]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Jovanny
Boneshock
а затем число, которое должно увеличиваться последовательно для данного дня


Если опираться на IDENTITY, то нужно вычислять максимальный IDENTITY за последний день и учитывать возможные пропуски в последовательности.
Не за последний день, а за каждый день из inserted (GROUP BY).
И не максимальный IDENTITY, а количество за каждый день с меньшими ID по сравнению с ID текущей строки, к которому прибавлять 1.
Опять же с участием inserted.

По крайней мере, другой юзер, вставляющий в этот же момент, будет иметь дело с другим диапазоном IDENTITY.
И о блокировках заботиться не придётся.
21 дек 11, 17:39    [11805109]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
Jovanny
пропущено...


Если опираться на IDENTITY, то нужно вычислять максимальный IDENTITY за последний день и учитывать возможные пропуски в последовательности.
Не за последний день, а за каждый день из inserted (GROUP BY).
И не максимальный IDENTITY, а количество за каждый день с меньшими ID по сравнению с ID текущей строки, к которому прибавлять 1.
Опять же с участием inserted.

По крайней мере, другой юзер, вставляющий в этот же момент, будет иметь дело с другим диапазоном IDENTITY.
И о блокировках заботиться не придётся.
Хотя да, я неправ!

Считать придётся максимальный суффикс для ID<inserted.ID за день
плюс количество ID в inserted за этот же день, меньших или равных ID текущей строки.

Кстати говоря, лучше дату кодировать в обратном порядке - YYYYMMDD для простоты получения максимума

P.S. Может, я глупость написал...
21 дек 11, 17:48    [11805196]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
А ещё как-то так можно извратиться.

CREATE TRIGGER dbo.Table_1OnInsert 
   ON  dbo.Table_1 
   AFTER INSERT
AS 
BEGIN
WHILE EXISTS(SELECT NULL FROM dbo.Table_1 AS T INNER JOIN inserted AS I 
		ON T.Id = I.Id WHERE T.MyString IS NULL)
	UPDATE TOP(1) T
	SET	T.MyString = (SELECT REPLACE(CONVERT(varchar(20), GETDATE(), 103), '/', '') + '_' +  
		CONVERT(varchar(10), ISNULL(MAX(CONVERT(int, SUBSTRING(T2.MyString, 10, 10))) + 1, 1))
				FROM [dbo].[Table_1] AS T2)
		FROM dbo.Table_1 AS T INNER JOIN inserted AS I 
			ON T.Id = I.Id WHERE T.MyString IS NULL
END
21 дек 11, 17:48    [11805197]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Да, и WITH (TABLOCK) добавить:
CREATE TRIGGER dbo.Table_1OnInsert 
   ON  dbo.Table_1 
   AFTER INSERT
AS 
BEGIN
WHILE EXISTS(SELECT NULL FROM dbo.Table_1 AS T INNER JOIN inserted AS I 
		ON T.Id = I.Id WHERE T.MyString IS NULL)
	UPDATE TOP(1) T WITH (TABLOCK)
	SET	T.MyString = (SELECT REPLACE(CONVERT(varchar(20), GETDATE(), 103), '/', '') + '_' +  
		CONVERT(varchar(10), ISNULL(MAX(CONVERT(int, SUBSTRING(T2.MyString, 10, 10))) + 1, 1))
				FROM [dbo].[Table_1] AS T2)
		FROM dbo.Table_1 AS T INNER JOIN inserted AS I 
			ON T.Id = I.Id WHERE T.MyString IS NULL
END
21 дек 11, 17:57    [11805258]     Ответить | Цитировать Сообщить модератору
 Re: Сложное вычисляемое поле  [new]
Boneshock
Member

Откуда:
Сообщений: 229
Огромное спасибо за ответы!
Оказывается решение не такое уж простое как кажется(ну для меня, во всяком случае). Постараюсь разобраться в приведенных кодах, отпишусь.
автор
Правда, номер в вычисляемом поле не будет начинаться с единицы каждый день. Так сказать, сквозной он получается.

Вообще, смысл этого поля такой: Оно должно быть уникальным, но кроме этого - нести определенную смысловую нагрузку, в данном случае - показывать дату вставки записи и, если возможно, последовательность, т.е. чтобы можно было понять, какая запись в этот день была вставлена первой, какая пятой, а какая последней.
23 дек 11, 21:31    [11818088]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить