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

Откуда: VRN
Сообщений: 192
добрый день господа, решил задачу следующим образом:

DECLARE @TEST_SPISOK TABLE (NUMPART VARCHAR(50),FORLOOK INT,SPISOK VARCHAR(20))

DECLARE @ITOG_SPISOK TABLE (NUMPART VARCHAR(50),FORLOOK INT,SPISOK VARCHAR(20))

DECLARE @TEST_SERTIFICAT TABLE (ID INT,NAME_SERTIFICAT VARCHAR(50))

DECLARE @SPISOK VARCHAR(20), @NUMPART VARCHAR(50), @FORLOOK INT

SET @NUMPART = '1'

-----------------------------------------------

INSERT INTO @TEST_SPISOK SELECT '20014::1302-134',7,'36,37,38'

INSERT INTO @TEST_SPISOK SELECT '20014::1303-004',9,'50,51,52'

----

INSERT INTO @TEST_SERTIFICAT SELECT 36,'ÑÅÐÒÈÔÈÊÀÒ 36'

INSERT INTO @TEST_SERTIFICAT SELECT 37,'ÑÅÐÒÈÔÈÊÀÒ 37'

INSERT INTO @TEST_SERTIFICAT SELECT 38,'ÑÅÐÒÈÔÈÊÀÒ 38'

INSERT INTO @TEST_SERTIFICAT SELECT 50,'ÑÅÐÒÈÔÈÊÀÒ 50'

INSERT INTO @TEST_SERTIFICAT SELECT 51,'ÑÅÐÒÈÔÈÊÀÒ 51'

INSERT INTO @TEST_SERTIFICAT SELECT 52,'ÑÅÐÒÈÔÈÊÀÒ 52'

-----------------------------------------------

WHILE (@NUMPART IS NOT NULL)

BEGIN

SET @NUMPART = (SELECT TOP 1 NUMPART FROM @TEST_SPISOK)

SET @FORLOOK = (SELECT FORLOOK FROM @TEST_SPISOK WHERE NUMPART = @NUMPART)

SET @SPISOK = (SELECT SPISOK+',S' FROM @TEST_SPISOK WHERE NUMPART = @NUMPART)

 

WHILE ((LEN(@SPISOK) > 0) AND (@SPISOK <> 'S'))

BEGIN

INSERT INTO @ITOG_SPISOK SELECT @NUMPART,@FORLOOK,(CASE WHEN CHARINDEX(',',@SPISOK) > 0

THEN SUBSTRING(@SPISOK,0,CHARINDEX(',',@SPISOK))

ELSE @SPISOK END)

SET @SPISOK = (SUBSTRING(@SPISOK,CHARINDEX(',',@SPISOK)+1,LEN(@SPISOK)))

END

DELETE FROM @TEST_SPISOK WHERE NUMPART = @NUMPART

END

----------

SELECT ITS.NUMPART,ITS.FORLOOK,SRT.ID,SRT.NAME_SERTIFICAT

FROM @ITOG_SPISOK AS ITS INNER JOIN @TEST_SERTIFICAT AS SRT ON ITS.SPISOK = SRT.ID


где ',S' - некий указатель конца множителя, может возможно как то еще упростить решение т.к. пугает цикл в цикле
21 янв 14, 18:14    [15448521]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
DmitryVT
Member

Откуда: VRN
Сообщений: 192
сори чо то все поползло

DECLARE @TEST_SPISOK TABLE (NUMPART VARCHAR(50),FORLOOK INT,SPISOK VARCHAR(20))
DECLARE @ITOG_SPISOK TABLE (NUMPART VARCHAR(50),FORLOOK INT,SPISOK VARCHAR(20))
DECLARE @TEST_SERTIFICAT TABLE (ID INT,NAME_SERTIFICAT VARCHAR(50))
DECLARE @SPISOK VARCHAR(20), @NUMPART VARCHAR(50), @FORLOOK INT
SET @NUMPART = '1'
-----------------------------------------------

INSERT INTO @TEST_SPISOK SELECT '20014::1302-134',7,'36,37,38'

INSERT INTO @TEST_SPISOK SELECT '20014::1303-004',9,'50,51,52'

----
INSERT INTO @TEST_SERTIFICAT SELECT 36,'Сертификат 36'
INSERT INTO @TEST_SERTIFICAT SELECT 37,'Сертификат 37'
INSERT INTO @TEST_SERTIFICAT SELECT 38,'Сертификат 38'
INSERT INTO @TEST_SERTIFICAT SELECT 50,'Сертификат 50'
INSERT INTO @TEST_SERTIFICAT SELECT 51,'Сертификат 51'
INSERT INTO @TEST_SERTIFICAT SELECT 52,'Сертификат 52'

-----------------------------------------------
WHILE (@NUMPART IS NOT NULL)
BEGIN
SET @NUMPART = (SELECT TOP 1 NUMPART FROM @TEST_SPISOK)
SET @FORLOOK = (SELECT FORLOOK FROM @TEST_SPISOK WHERE NUMPART = @NUMPART)
SET @SPISOK = (SELECT SPISOK+',S' FROM @TEST_SPISOK WHERE NUMPART = @NUMPART)

WHILE ((LEN(@SPISOK) > 0) AND (@SPISOK <> 'S'))
BEGIN
INSERT INTO @ITOG_SPISOK SELECT @NUMPART,@FORLOOK,(CASE WHEN CHARINDEX(',',@SPISOK) > 0
THEN SUBSTRING(@SPISOK,0,CHARINDEX(',',@SPISOK))
ELSE @SPISOK END)
SET @SPISOK = (SUBSTRING(@SPISOK,CHARINDEX(',',@SPISOK)+1,LEN(@SPISOK)))

END

DELETE FROM @TEST_SPISOK WHERE NUMPART = @NUMPART

END

----------
SELECT ITS.NUMPART,ITS.FORLOOK,SRT.ID,SRT.NAME_SERTIFICAT
FROM @ITOG_SPISOK AS ITS INNER JOIN @TEST_SERTIFICAT AS SRT ON ITS.SPISOK = SRT.ID
21 янв 14, 18:18    [15448541]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
Glory
Member

Откуда:
Сообщений: 104751
DmitryVT
решил задачу следующим образом:

Круто. А что за задача то ?
21 янв 14, 19:15    [15448845]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Glory,

Ноги возможно отседова растут.
Но ответ уже написали 15163693

Имхо, он кому-то написал ответ и ссылку сюда дал. SQL.ru просто как помойка сообщений использовалась.

Ответы на форумах не поддаются внятному анализу. Школьнег детектед.
22 янв 14, 00:26    [15449881]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
DmitryVT
Member

Откуда: VRN
Сообщений: 192
Glory нет, ноги не оттуда растут, сессия совсем запарился да еще и писал с телефона возможно что то не скопировал.
Суть задачи в следующем: в Таблице1 имеется строка (перечень номеров сертификатов) которая содержит (1 и более) номер, каждый номер разделен запятой от следующего
, в Таблице2 хранятся номера сертификатов и их название. Необходимо сделать Таблица1 JOIN Таблица2 по преобразованному полю Таблица1.список_сертификатов.

Мой пример Выше выдают правильный результат, но не нравится цикл в цикле, да и запись во временную таблицу "@ITOG_SPISOK" происходит. Возможно ли упростить листинг ??


Заранее всем спасибо!
22 янв 14, 10:33    [15450746]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
Добрый Э - Эх
Guest
join по like-у. проще некуда, но производительность на больших объемах будет страдать.
22 янв 14, 10:36    [15450764]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
Glory
Member

Откуда:
Сообщений: 104751
DmitryVT
Суть задачи в следующем: в Таблице1 имеется строка (перечень номеров сертификатов) которая содержит (1 и более) номер, каждый номер разделен запятой от следующего
, в Таблице2 хранятся номера сертификатов и их название. Необходимо сделать Таблица1 JOIN Таблица2 по преобразованному полю Таблица1.список_сертификатов.

Нормальные формы ? Нет, не слышал
22 янв 14, 10:38    [15450779]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
Добрый Э - Эх
Guest
DmitryVT,

на досуге почитай статейку: Массивы и списки в MS SQL Server...
22 янв 14, 10:39    [15450785]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
DmitryVT
Member

Откуда: VRN
Сообщений: 192
Glory

Нормальные формы ? Нет, не слышал

это не реальная БД, это задание с сессии из головы педагога, которое сегодня сдать нужно, к сожалению таблички и есть исходные данные.
22 янв 14, 10:42    [15450800]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
aleks2
Guest
DmitryVT
Glory нет, ноги не оттуда растут, сессия совсем запарился да еще и писал с телефона возможно что то не скопировал.
Суть задачи в следующем: в Таблице1 имеется строка (перечень номеров сертификатов) которая содержит (1 и более) номер, каждый номер разделен запятой от следующего
, в Таблице2 хранятся номера сертификатов и их название. Необходимо сделать Таблица1 JOIN Таблица2 по преобразованному полю Таблица1.список_сертификатов.

Мой пример Выше выдают правильный результат, но не нравится цикл в цикле, да и запись во временную таблицу "@ITOG_SPISOK" происходит. Возможно ли упростить листинг ??


Заранее всем спасибо!


CREATE function [dbo].[f_StrToTableEx](@str varchar(8000), @delimiter varchar(64)=',')
returns table as
return(
WITH str_nums ( n1, n2, Number ) 
AS 
( 
select  1-DATALENGTH(@delimiter) as n1, charindex(@delimiter, @str+@delimiter) as n2, 0 as Number  
UNION ALL 
select n2 as n1, charindex(@delimiter, @str+@delimiter, n2+DATALENGTH(@delimiter)) as n2, Number+1 as Number
from str_nums
WHERE n2<DATALENGTH(@str)
) 
SELECT SUBSTRING(@str, n1+DATALENGTH(@delimiter), n2-n1-DATALENGTH(@delimiter)) as Value, Number, n1+DATALENGTH(@delimiter) as StartPosition FROM str_nums
)
22 янв 14, 10:49    [15450829]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
Glory,

Ноги возможно отседова растут.
Но ответ уже написали 15163693

Имхо, он кому-то написал ответ и ссылку сюда дал. SQL.ru просто как помойка сообщений использовалась.

Ответы на форумах не поддаются внятному анализу. Школьнег детектед.
Файспалм.
Коль лень ходить по ссылке перебью вариант aleks2
Mnior
CREATE FUNCTION [dbo].[fnList] (
	@List		NVarChar(4000)
,	@Delimeter	NChar(1) = N','
) RETURNS TABLE AS RETURN	-- SELECT Item FROM dbo.fnList(N'1,22,333',Default)
WITH [List] ([No],[Pos],[To]) AS (
	SELECT	0,0,0
UNION ALL
	SELECT	L.[No] + 1
	,	L.[To] + 1
	,	I.[To]
	FROM	[List] L CROSS APPLY (SELECT CharIndex(@Delimeter,@List + @Delimeter,L.[To] + 1)) I ([To])
	WHERE	I.[To] > 0
)	SELECT	[No]
	,	SubString(@List,[Pos],[To]-[Pos])	AS Item
	,	[Pos]
	FROM	[List]
	WHERE	[To] > [Pos]
GO
В результате получаем:
DECLARE @TEST_SPISOK		TABLE (NUMPART VARCHAR(50),FORLOOK INT,SPISOK VARCHAR(20))
DECLARE @TEST_SERTIFICAT	TABLE (ID INT,NAME_SERTIFICAT VARCHAR(50))
INSERT	@TEST_SPISOK		VALUES
 ('20014::1302-134',7,'36,37,38')
,('20014::1303-004',9,'50,51,52')
INSERT	@TEST_SERTIFICAT	VALUES
 (36,'Сертификат 36')
,(37,'Сертификат 37')
,(38,'Сертификат 38')
,(50,'Сертификат 50')
,(51,'Сертификат 51')
,(52,'Сертификат 52')

SELECT	SPS.NUMPART
,	SPS.FORLOOK
,	SRT.ID
,	SRT.NAME_SERTIFICAT
FROM	@TEST_SPISOK				AS SPS
CROSS APPLY dbo.fnList(SPS.SPISOK,Default)	AS LST
JOIN	@TEST_SERTIFICAT			AS SRT ON SRT.ID = Convert(Int,LST.Item)

DmitryVT
ноги не оттуда растут
Оттуда растут, оттуда. Вопрос идентичный.
22 янв 14, 12:16    [15451515]     Ответить | Цитировать Сообщить модератору
 Re: Подмножество  [new]
DmitryVT
Member

Откуда: VRN
Сообщений: 192
Как то сильно для моих знаний SQL, спасибо буду разбираться.

ВСЕМ спасибо за помощь
22 янв 14, 20:27    [15454027]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить