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

Откуда:
Сообщений: 20
Доброго всем дня! =)

Есть такая задача:

Есть 1я таблица с такими полями:
CREATE TABLE #T1(f1 INT, f2 VARCHAR(20), t bit, v real)
,
где
Поле t - это тип. Если t = 0, то поле f2 заполнено, если t = 1. то f2 не заполнено.
Поле v - это какой-то коэффициент.
Если t = 0, то поле v действует только для поля f1 и f2 , если же t = 1, то поле v действует только для значения поля f1 и для всех допустимых значений f2. Но она не может перекрыть строку, если есть строка f1,f2

Есть 2я таблица с такими полями:
CREATE TABLE #T2(f2 VARCHAR(20), fName VARCHAR(20))
Это таблица названий полей f2

Вопрос: Надо строки t = 1, заполнить для всех значений поля f2.

Данные для примера:
+
CREATE TABLE #T1(f1 INT, f2 VARCHAR(20), t bit, v real)
INSERT INTO #T1 VALUES(1,10,0,0.5)
INSERT INTO #T1 VALUES(2,11,0,0.5)
INSERT INTO #T1 VALUES(3,12,0,0.75)
INSERT INTO #T1 VALUES(4,13,0,0.1)
INSERT INTO #T1 VALUES(5,14,0,0.2)
INSERT INTO #T1 VALUES(1,'',1,0.8)
INSERT INTO #T1 VALUES(2,'',1,0.8)

CREATE TABLE #T2(f2 VARCHAR(20), fName VARCHAR(20))
INSERT INTO #T2 VALUES(10, 'десять')
INSERT INTO #T2 VALUES(11, 'одинодин')
INSERT INTO #T2 VALUES(12, 'одиндва')
INSERT INTO #T2 VALUES(13, 'одинтри')
INSERT INTO #T2 VALUES(14, 'одинчетыре')
INSERT INTO #T2 VALUES(15, 'одинпять')
INSERT INTO #T2 VALUES(16, 'одиншесть')
INSERT INTO #T2 VALUES(17, 'одинсемь')
INSERT INTO #T2 VALUES(18, 'одинвосемь')
INSERT INTO #T2 VALUES(19, 'одиндевять')

Мое решение:
+
SELECT
 A.f1
,A.f2
,A.t
,A.v
,B.f2
,B.fName
FROM #T1 A
LEFT JOIN #T2 B ON A.f2 = B.f2
WHERE t = 0
UNION
SELECT
 A.f1
,A.f2
,A.t
,A.v
,B.f2
,B.fName
FROM #T1 A
CROSS JOIN #T2 B
WHERE t = 1


В моем решении только осталось как-то убрать строки, которые выполняют вот это условие: "Но она не может перекрыть строку, если есть строка f1,f2".

Спасибо заранее за помощь\советы! Буду рад любому ответу.
14 сен 11, 10:00    [11273455]     Ответить | Цитировать Сообщить модератору
 Re: Как решить задачу с пересечением строк  [new]
Добрый Э - Эх
Guest
Бородатый анекдот
До каких пор будет продолжаться эта хрень со средой.(С)

Видимо, как и положено в среду, я уже так натрудился, что даже после третьего прочтения так и не понял - "чего ж тебе надобно, старче"...
14 сен 11, 10:08    [11273487]     Ответить | Цитировать Сообщить модератору
 Re: Как решить задачу с пересечением строк  [new]
?
Guest
автор
Но она не может перекрыть строку, если есть строка f1,f2


Что бы это могло значить?

Я как и Добрый Э - Эх, не смог размыслить
14 сен 11, 10:14    [11273515]     Ответить | Цитировать Сообщить модератору
 Re: Как решить задачу с пересечением строк  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Добрый Э - Эх,

+1
тоже долго вчитывался так и не понял...

особенно вот эту фразу неплохо было бы прояснить:
"Если t = 0, то поле v действует (что значит действует? как действует?) только для поля f1 и f2 (только для полЯ или для полЕЙ? написано вроде для поля а указаны два поля) , если же t = 1, то поле v действует только для значения поля f1 (опять же как действует? м как это должно отразиться в запрсое?) и для всех допустимых (куда допустимых, допустимых для чего?) значений f2. Но она (кто она?) не может перекрыть строку (что значит перекрыть?), если есть строка f1,f2 (что это за строка такая f1,f2?)"

Borsugg,
мож лучше привести пример того, какие должны получиться данные? ну и постарайтесь перефразировать свою мысль озвученную выше...
14 сен 11, 10:18    [11273535]     Ответить | Цитировать Сообщить модератору
 Re: Как решить задачу с пересечением строк  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Добрый Э - Эх, к хорошему прывыкаешь.
+
Поэтому изарвщения практически не воспринимаются.
Вот выбрал для данной задачи такую архртектуру. Но ип..ся с ней хочу предоставить хомячкам на форуме. Любые предложения по её выравниванию будут посылаться лесом. Вэлкам.
14 сен 11, 10:18    [11273541]     Ответить | Цитировать Сообщить модератору
 Re: Как решить задачу с пересечением строк  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Интересно, откуда эта "задача"?
На собеседовании дали?
sql-ex (простите меня за тупость и неосведомлённость)?
Или реальная система (от неё что-то такое ожидается)?

Не проще ли определять, заполнено поле f2 или нет, проверкой f2 IS NULL? А не каким-то левым полем t
14 сен 11, 10:23    [11273563]     Ответить | Цитировать Сообщить модератору
 Re: Как решить задачу с пересечением строк  [new]
Borsugg
Member

Откуда:
Сообщений: 20
SomewhereSomehow
мож лучше привести пример того, какие должны получиться данные? ну и постарайтесь перефразировать свою мысль озвученную выше...


Каюсь! Да писать на голодный желудок видимо не стоило, после обеда и сам понять не могу что хотел до вас донести. %)

iap
Интересно, откуда эта "задача"?
На собеседовании дали?
sql-ex (простите меня за тупость и неосведомлённость)?
Или реальная система (от неё что-то такое ожидается)?

Не проще ли определять, заполнено поле f2 или нет, проверкой f2 IS NULL? А не каким-то левым полем t

К сожалению реальная система.

Постараюсь перефразировать суть проблемы.
Пояснения по таблице #T1
Поле f1 - пусть будет магазин
Поле f2 - пусть будет код уровня чего-нибудь, к примеру, уровень ассортиментного классификатора.
Поле v - будем считать каким-то нормативом.
Поле t - это тип действия норматива. Если t = 0, то это значит, что норматив действует в этом магазине, только на этот уровень. Если же t = 1, то значит, что норматив действует в этом магазине, только уже на все уровни.

Пояснения по таблице #T2
Это справочник всех уровней в системе.
Поле f2 - это код уровня
Поле fName - это название уровня

Так вот.
Первая задача у меня стояла в том, чтобы все строки, где t = 1 заполнить на все уровни.
Это решил CROSS JOIN (В решении есть)
После появилась вторая проблема - это если уже была строка с нормативом на этот магазин, на этот уровень, то появлялась и вторая строка с нормативом (на все уровни) на этот магазин. Так вот приоритет выше у того, который был заполнен непосредственно на уровень.
Надеюсь объяснил теперь намного доступнее. =)
14 сен 11, 11:19    [11273930]     Ответить | Цитировать Сообщить модератору
 Re: Как решить задачу с пересечением строк  [new]
apply
Guest
http://msdn.microsoft.com/ru-ru/library/ms175156%28SQL.90%29.aspx
14 сен 11, 11:50    [11274222]     Ответить | Цитировать Сообщить модератору
 Re: Как решить задачу с пересечением строк  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Borsugg,

не уверен что понял вас правильно, но м.б. имелось ввиду это (названия таблиц чуть поменял, а то трудно осмысливать):
+
CREATE TABLE #Shop (ShopID INT, ClassCode VARCHAR(20), ApplyKoefToAll bit, Koef real)
CREATE TABLE #Class (ClassCode VARCHAR(20), ClassName VARCHAR(20))

INSERT INTO #Shop VALUES(1,'10',0,0.5)
INSERT INTO #Shop VALUES(2,'11',0,0.5)
INSERT INTO #Shop VALUES(3,'12',0,0.75)
INSERT INTO #Shop VALUES(4,'13',0,0.1)
INSERT INTO #Shop VALUES(5,'14',0,0.2)
INSERT INTO #Shop VALUES(1,'',1,0.8)
INSERT INTO #Shop VALUES(2,'',1,0.8)

INSERT INTO #Class VALUES('10', 'десять')
INSERT INTO #Class VALUES('11', 'одинодин')
INSERT INTO #Class VALUES('12', 'одиндва')
INSERT INTO #Class VALUES('13', 'одинтри')
INSERT INTO #Class VALUES('14', 'одинчетыре')
INSERT INTO #Class VALUES('15', 'одинпять')
INSERT INTO #Class VALUES('16', 'одиншесть')
INSERT INTO #Class VALUES('17', 'одинсемь')
INSERT INTO #Class VALUES('18', 'одинвосемь')
INSERT INTO #Class VALUES('19', 'одиндевять')

-- раз
select ShopID, ClassCode, ApplyKoefToAll, Koef from #Shop where ApplyKoefToAll = 0
union all
select 
	s.ShopID, 
	c.ClassCode, 
	1, 
	0 
from
	(select distinct ShopID from #Shop where ApplyKoefToAll = 1) s -- distinct уберите если магазины не повторяются
	cross join #Class c
where
	not exists (select * from #Shop s1 where s1.ShopID = s.ShopID and s1.ClassCode = c.ClassCode)
order by
	ShopID,
	ClassCode
	
-- два
select 
	s.ShopID, 
	cc.ClassCode, 
	s.ApplyKoefToAll, 
	s.Koef 
from 
	#Shop s
	cross apply (
		select 
			ClassCode 
		from 
			#Class c 
		where 
			s.ClassCode = c.ClassCode or
			s.ClassCode = '' and not exists (select * from #Shop ss where s.ShopID = ss.ShopID and ss.ClassCode = c.ClassCode)
	) cc
order by
	ShopID,
	ClassCode
14 сен 11, 12:02    [11274371]     Ответить | Цитировать Сообщить модератору
 Re: Как решить задачу с пересечением строк  [new]
Borsugg
Member

Откуда:
Сообщений: 20
apply
http://msdn.microsoft.com/ru-ru/library/ms175156%28SQL.90%29.aspx

За ссылку спасибо. Открыл для себя новый оператор.

SomewhereSomehow
Borsugg,
не уверен что понял вас правильно, но м.б. имелось ввиду это (названия таблиц чуть поменял, а то трудно осмысливать):
-- раз


Здесь имелось скорее вот так:
+
select ShopID, ClassCode, ApplyKoefToAll, Koef from #Shop where ApplyKoefToAll = 0
union all
select 
	s.ShopID, 
	c.ClassCode, 
	1, 
	s.koef
from
	(select distinct ShopID, koef from #Shop where ApplyKoefToAll = 1) s -- distinct уберите если магазины не повторяются
	cross join #Class c
where
	not exists (select * from #Shop s1 where s1.ShopID = s.ShopID and s1.ClassCode = c.ClassCode)
order by
	ShopID,
	ClassCode


Да спасибо большое! Думаю то, что нужно.
Сейчас потестю на реальных данных и посмотрю какое из предложенных решений быстрее выполняется.
14 сен 11, 12:36    [11274637]     Ответить | Цитировать Сообщить модератору
 Re: Как решить задачу с пересечением строк  [new]
Borsugg
Member

Откуда:
Сообщений: 20
Borsugg
Сейчас потестю на реальных данных и посмотрю какое из предложенных решений быстрее выполняется.


1-й запрос "раз":
Estimates Subtree Cost: 50.36
Время выполнения: 16 секунд

2-й запрос "два":
Estimates Subtree Cost: 46.18
Время выполнения: 32 секунд

Вот как-то так.
14 сен 11, 13:07    [11274911]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить