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

Откуда:
Сообщений: 5
Доброго времени суток,

может быть сталкивались с подобной задачей (БД MS SQL 2008):

Задача:
В таблицу 2 добавить поле Entry_No_T1.

Условия:

есть 2 источника (для простоты можно таблицы)

Поля таблицы 1: Doc_Item_No, Entry_No_T1, Quantity_T1
Поля таблицы 2: Doc_Item_No, Entry_No_T2, Quantity_T2

Если соединить таблицы1 и 2 по Doc_Item_No, то для Doc_Item_No sum(Quantity_T1) = sum(Quantity_T2).
В рамках каждого Doc_Item_No поле Entry_No уникально для каждой из таблиц и расположено по возрастанию (Entry_No тип int).
Entry_No_T1 и Entry_No_T2 никак не связаны, но в рамках одного Doc_Item_No, если идти по порядку по полю Entry_No_T2 таблицы 2, то в определенный момент количества Quantity_T1 = sum(Quantity_T2[..]) и => в таблице 2 можно проставить значение поля Entry_No_T1.

Предполагаемое решение:
Пока что есть представление, как решить эту задачу с помощью циклического прохода по этим таблицам, сравнивая текущую сумму Quantity_T2 со значением Quantity_T1.

Поделитесь, пожалуйста, если знаете более красивое решение, которое позволит избежать построчного перебора?
19 июн 12, 15:13    [12739165]     Ответить | Цитировать Сообщить модератору
 Re: Объединение логики в один запрос  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Samurai111, на ум приходит только посчитать для Quantity_T2 любым способом сумму с накоплением в таблице 2 в пределах Doc_Item_No порядке возрастания Entry_No_T2, и затем результат связать с таблицей 1 по полям Doc_Item_No и Quantity_T1.
Примерно так:

select a.Doc_Item_No,a.Entry_No_T2,a.Quantity_T2,b.Quantity_T1
from (
  select a.*,
    (select sum(Quantity_T2) 
     from Таблица_2 b 
     where b.Doc_Item_No=a.Doc_Item_No 
        and b.Entry_No_T2<=a.Entry_No_T2) as Q2
  from Таблица_2 a
) a
  inner join Таблица_1 b on b.Doc_Item_No=a.Doc_Item_No and b.Quantity_T1=a.Q2
19 июн 12, 21:14    [12741570]     Ответить | Цитировать Сообщить модератору
 Re: Объединение логики в один запрос  [new]
Samurai111
Member

Откуда:
Сообщений: 5
Спасибо Serg_77m,

отвлекся немного, реализовывал неоптимизированный подход :)

Это почти то, что нужно и частично я использовал накопительную сумму, как Вы и писали. Только в запросе Вы вместо Entry_No_T1 к таблице 2 добавили количество. Это не совсем то, что нужно.

В результате запроса дожно получиться следующее:

таблица 2.* и Entry_No_T1

Т.е. мы фактически добавляем к таблице 2 еще одно поле и это можно сделать,
1) т.к. суммы в по Doc_Item_No одинаковы в обоих таблицах
2) и точно известно, что количество записи Doc_Item_No + Entry_No_T1 без остатка включает в себя сумму количества по записям Doc_Item_No + Entry_No_T2
3) и Entry_No_T1 и Entry_No_T2 целочисленные значения с инкрементом 1.
25 июн 12, 12:45    [12769728]     Ответить | Цитировать Сообщить модератору
 Re: Объединение логики в один запрос  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Проблема в том что вы очень плохо формализовали задачу. Скорее налюдается даже противоречие.
Тем более по правилам форума вы обязаны написать скрипты с тестовыми данными и то что вы хотите получить в итоге.

У меня в голове крутятся несколько решений, но мысли сбивает эта вероятная противоречивость.
25 июн 12, 15:41    [12771451]     Ответить | Цитировать Сообщить модератору
 Re: Объединение логики в один запрос  [new]
Samurai111
Member

Откуда:
Сообщений: 5
Mnior, действительно сриптов не хватает...

Задача:

В таблицу 2 добавить поле Entry_No_T1. Т.е. целевая таблица включит в себя следующие поля:
Doc_Item_No, Entry_No_T2, Quantity_T2, Entry_No_T1.

Условия:

есть 2 таблицы:

Таблица 1: Doc_Item_No, Entry_No_T1, Quantity_T1

/*Table 1*/
CREATE TABLE [dbo].[Table_1](
	[Doc_Item_No] [varchar](30) NOT NULL,
	[Entry_No_T1] [int] NOT NULL,
	[Quantity_T1] [int] NOT NULL
) ON [PRIMARY];

INSERT INTO [dbo].[Table_1] ([Doc_Item_No] ,[Entry_No_T1] ,[Quantity_T1])
	VALUES ('V31578', 154678934, 15);
      
INSERT INTO [dbo].[Table_1] ([Doc_Item_No] ,[Entry_No_T1] ,[Quantity_T1])
	VALUES ('V31578', 263456708, 1);
	
INSERT INTO [dbo].[Table_1] ([Doc_Item_No] ,[Entry_No_T1] ,[Quantity_T1])
	VALUES ('L12349', 246756555, 3);
	
INSERT INTO [dbo].[Table_1] ([Doc_Item_No] ,[Entry_No_T1] ,[Quantity_T1])
	VALUES ('V53789', 129354578, 3);
	
INSERT INTO [dbo].[Table_1] ([Doc_Item_No] ,[Entry_No_T1] ,[Quantity_T1])
	VALUES ('V53789', 129354579, 23);
	
INSERT INTO [dbo].[Table_1] ([Doc_Item_No] ,[Entry_No_T1] ,[Quantity_T1])
	VALUES ('V53789', 129354588, 78);



Таблица 2: Doc_Item_No, Entry_No_T2, Quantity_T2

/*Table 2*/
CREATE TABLE [dbo].[Table_2](
	[Doc_Item_No] [varchar](30) NOT NULL,
	[Entry_No_T2] [int] NOT NULL,
	[Quantity_T2] [int] NOT NULL
) ON [PRIMARY];

INSERT INTO [dbo].[Table_2] ([Doc_Item_No] ,[Entry_No_T2] ,[Quantity_T2])
	VALUES ('V31578', 456818247, 5);
	
INSERT INTO [dbo].[Table_2] ([Doc_Item_No] ,[Entry_No_T2] ,[Quantity_T2])
	VALUES ('V31578', 456818248, 10);
	
INSERT INTO [dbo].[Table_2] ([Doc_Item_No] ,[Entry_No_T2] ,[Quantity_T2])
	VALUES ('V31578', 456818249, 1);

INSERT INTO [dbo].[Table_2] ([Doc_Item_No] ,[Entry_No_T2] ,[Quantity_T2])
	VALUES ('L12349', 456625452, 3);
	
INSERT INTO [dbo].[Table_2] ([Doc_Item_No] ,[Entry_No_T2] ,[Quantity_T2])
	VALUES ('V53789', 153123879, 3);
	
INSERT INTO [dbo].[Table_2] ([Doc_Item_No] ,[Entry_No_T2] ,[Quantity_T2])
	VALUES ('V53789', 153123880, 11);
	
INSERT INTO [dbo].[Table_2] ([Doc_Item_No] ,[Entry_No_T2] ,[Quantity_T2])
	VALUES ('V53789', 153123885, 12);
	
INSERT INTO [dbo].[Table_2] ([Doc_Item_No] ,[Entry_No_T2] ,[Quantity_T2])
	VALUES ('V53789', 153123886, 4);
	
INSERT INTO [dbo].[Table_2] ([Doc_Item_No] ,[Entry_No_T2] ,[Quantity_T2])
	VALUES ('V53789', 153123887, 19);
	
INSERT INTO [dbo].[Table_2] ([Doc_Item_No] ,[Entry_No_T2] ,[Quantity_T2])
	VALUES ('V53789', 153123888, 55);


В прикрепленном Excel файле описаны источники и целевая таблица, которая должна получиться в результате. Условия задачи видны из Excel файла, но все же продублирую их текстовым описанием:

1. После группировки по Doc_Item_No суммы по каждому из них в обоих таблицах должны быть равны.
select a.Doc_Item_No, (a.Quantity_T1 - b.Quantity_T2) as Validation_Value
from
	(select Doc_Item_No, SUM(Quantity_T1) as Quantity_T1
	from Table_1
	group by Doc_Item_No) a
	inner join 
 	(select Doc_Item_No, SUM(Quantity_T2) as Quantity_T2
	from Table_2
	group by Doc_Item_No) b
		on a.Doc_Item_No = b.Doc_Item_No;

2. Значения Quantity_T1 и Quantity_T2 для обоих таблиц всегда > 0.
3. Поля Entry_No_T1 для Table_1 и Entry_No_T2 для Table_2
уникальны,тип полей int.
4. В рамках одного значения Doc_Item_No, Entry_No_T1 для Table_1
и Entry_No_T2 для Table_2 идут всегда от меньшего к большему, т.е. по возрастанию.
5. В рамках одного значения Doc_Item_No, Entry_No_T1 для Table_1 цело по количеству
включит в себя несколько Entry_No_T2 для Table_2. Entry_No_T1 и Entry_No_T2 никак не связаны,
но в рамках одного Doc_Item_No, если идти по порядку по полю Entry_No_T2 таблицы 2,
то в определенный момент количества Quantity_T1 = sum(Quantity_T2[..])
и => в таблице 2 можно проставить значение поля Entry_No_T1.

К сообщению приложен файл (Задача.xls - 28Kb) cкачать
16 июл 12, 13:03    [12872187]     Ответить | Цитировать Сообщить модератору
 Re: Объединение логики в один запрос  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
+ Решение в лоб
Подправив решение Serg_77m
DECLARE @Table_1 TABLE (
	 Doc_Item_No	VarChar(30)	NOT NULL
	,Entry_No	Int		NOT NULL
	,PRIMARY KEY (
		 Doc_Item_No
		,Entry_No
	)
	,Quantity	Int		NOT NULL
)
DECLARE @Table_2 TABLE (
	 Doc_Item_No	VarChar(30)	NOT NULL
	,Entry_No	Int		NOT NULL
	,PRIMARY KEY (
		 Doc_Item_No
		,Entry_No
	)
	,Quantity	Int		NOT NULL
)
INSERT @Table_1 (Doc_Item_No,Entry_No,Quantity) VALUES
 ('V31578', 154678934, 15)
,('V31578', 263456708,  1)
,('L12349', 246756555,  3)
,('V53789', 129354578,  3)
,('V53789', 129354579, 23)
,('V53789', 129354588, 78)

INSERT @Table_2 (Doc_Item_No,Entry_No,Quantity) VALUES
 ('V31578', 456818247,  5)
,('V31578', 456818248, 10)
,('V31578', 456818249,  1)
,('L12349', 456625452,  3)
,('V53789', 153123879,  3)
,('V53789', 153123880, 11)
,('V53789', 153123885, 12)
,('V53789', 153123886,  4)
,('V53789', 153123887, 19)
,('V53789', 153123888, 55)
--------------------------------------------------------------------------------
;WITH Table_1 AS (
	SELECT	*
	FROM	@Table_1 T OUTER APPLY (
			SELECT	Sum(S.Quantity) AS QuantityAgg
			FROM	@Table_1 S
			WHERE	    S.Doc_Item_No	 = T.Doc_Item_No
				AND S.Entry_No		<= T.Entry_No
		)	S
), Table_2 AS (
	SELECT	*
	FROM	@Table_2 T OUTER APPLY (
			SELECT	Sum(S.Quantity)	AS QuantityAgg
			FROM	@Table_2 S
			WHERE	    S.Doc_Item_No	 = T.Doc_Item_No
				AND S.Entry_No		<= T.Entry_No
		)	S
)
SELECT	 A.*
	,B.*
FROM	          Table_2	A
	LEFT JOIN Table_1	B ON B.Doc_Item_No	 = A.Doc_Item_No
				 AND B.QuantityAgg	 = A.QuantityAgg
ORDER BY A.Doc_Item_No
	,A.Entry_No
	,B.Entry_No
+ Для 2012-го
;WITH Table_1 AS (
	SELECT	 *
		,Sum(Quantity)OVER(
			PARTITION BY Doc_Item_No
			ORDER BY Entry_No)	AS QuantityAgg
	FROM	@Table_1 T
), Table_2 AS (
	SELECT	 *
		,Sum(Quantity)OVER(
			PARTITION BY Doc_Item_No
			ORDER BY Entry_No)	AS QuantityAgg
	FROM	@Table_2 T
)
SELECT	 A.*
	,B.*
FROM	          Table_2	A
	LEFT JOIN Table_1	B ON B.Doc_Item_No	 = A.Doc_Item_No
				 AND B.QuantityAgg	 = A.QuantityAgg
ORDER BY A.Doc_Item_No
	,A.Entry_No
	,B.Entry_No
Для неправильных данных и отрицательных Quantity и симметричного случая не работает.

Если надо решить это на момент вставки данных, то проще.
16 июл 12, 16:13    [12873274]     Ответить | Цитировать Сообщить модератору
 Re: Объединение логики в один запрос  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Samurai111, т.е. я не понял в чём вопрос, в вашем же запросе 80% ответа.
16 июл 12, 16:15    [12873294]     Ответить | Цитировать Сообщить модератору
 Re: Объединение логики в один запрос  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Надо было не в Exel а тут в виде CSV выложить:
Doc_Item_NoEntry_No_T2Quantity_T2Entry_No_T1
V315784568182475154678934
V3157845681824810154678934
V315784568182491263456708
L123494566254523246756555
V537891531238793129354578
V5378915312388011129354579
V5378915312388512129354579
V537891531238864129354588
V5378915312388719129354588
V5378915312388855129354588
Вам нужно не при равенстве, а тупо разбить по группам.

А вот про противоречение:
Такое быть может?:
Q1Q2
12
33
21
Т.е. несколько с одной таблицы сопоставляется с несколькими из другой. В этом примере типа это одна цельная группа.
16 июл 12, 16:39    [12873456]     Ответить | Цитировать Сообщить модератору
 Re: Объединение логики в один запрос  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Тот же в лоб по группам:
;WITH Table_1 AS (
	SELECT	*
	FROM	@Table_1 T OUTER APPLY (
			SELECT	Sum(S.Quantity) AS QuantityAgg
			FROM	@Table_1 S
			WHERE	    S.Doc_Item_No	 = T.Doc_Item_No
				AND S.Entry_No		<= T.Entry_No
		)	S
), Table_2 AS (
	SELECT	*
	FROM	@Table_2 T OUTER APPLY (
			SELECT	Sum(S.Quantity)	AS QuantityAgg
			FROM	@Table_2 S
			WHERE	    S.Doc_Item_No	 = T.Doc_Item_No
				AND S.Entry_No		<= T.Entry_No
		)	S
)
SELECT	 B.Doc_Item_No
	,B.Entry_No
	,B.Quantity
	,A.Entry_No
FROM	     Table_1	A
	JOIN Table_2	B ON B.Doc_Item_No	 = A.Doc_Item_No
			 AND B.QuantityAgg	>  A.QuantityAgg - A.Quantity
			 AND B.QuantityAgg	<= A.QuantityAgg
ORDER BY A.Doc_Item_No
	,A.Entry_No
	,B.Entry_No
16 июл 12, 16:47    [12873510]     Ответить | Цитировать Сообщить модератору
 Re: Объединение логики в один запрос  [new]
Samurai111
Member

Откуда:
Сообщений: 5
Спасибо, Mnior! Все заработало, как нужно :)
26 июл 12, 12:12    [12918745]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить