ReSQL.ru
     
powered by simpleCommunicator - 2.0.55     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / FoxPro, Visual FoxPro [игнор отключен] [закрыт для гостей] / Изменение данных сразу в двух таблицах. как лучше?
11 сообщений из 11, страница 1 из 1
Изменение данных сразу в двух таблицах. как лучше?
    #36034224
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Есть 2 таблицы.
В первой (Т1) id1,id2,сумма - таблица генерится каждый месяц новая.
Во второй (Т2) id1,id2,дата,сумма - идёт добавление записей каждый месяц, причём (id1+id2) может повторяться в разных месяцах.
Задача: уменьшить суммы в Т1 на общую (по всем датам) величину сумм из Т2 (по соответствующим id1 и id2), при этом не уйти в Т1 ниже нуля И уменьшить общую сумму в Т2 на соответствующую величину. Желательно уменьшать, начиная с более старых значений.
Т.е. например, в Т1 есть запись {1,1,100}, а в Т2 есть 2 записи {1,1,дата1,65}{1,1,дата2,90} Должно получиться соответственно {1,1,0} и {1,1,дата1,0}{1,1,дата2,55}
В голове пока нашлось место для двух вариантов. Какой лучше, и, может, есть "более нормальное" решение?
Уменьшение сумм по Т1 - очевидно, так
Код: plaintext
1.
2.
3.
4.
5.
SELECT id1,id2,SUM(ost_sum) AS ost_sum FROM T2 INTO CURSOR rcc
SELECT T1.id1,T1.id2,IIF(T1.summa<NVL(rcc.summa, 0 ),T1.summa,NVL(rcc.summa, 0 )) AS sn_sum;
  FROM T1 FULL JOIN rcc ON T1.id1=rcc.id1 AND T1.id2=rcc.id2;
  INTO CURSOR rcr
UPDATE T1 SET summa=summa-sn_sum;
  FROM T1 LEFT JOIN rcr ON T1.id1=rcr.id1 AND T1.id2=rcr.id2
Там FULLJOIN - потому что не гарантировано, что id1+id2 из Т1 обязательно найдётся в Т2 и наоборот (ну вот такие данные нам присылают), а списочек-то нужен будет весь.
А вот варианты обработки Т2
В1
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
	SELECT T2
	SET ORDER TO ID   && STR(id1, 7 )+STR(USL, 2 )
	SELECT rcr
	SCAN FOR sn_sum> 0 
	  _id=STR(id1, 7 )+STR(USL, 2 )
	  snsum=rcc.sn_sum
	  SELECT T2
	  IF SEEK(_id)
	    DO WHILE snsum> 0  AND ID=_id
	      tmpsum=MIN(snsum,ostsum)
	      REPLACE ostsum WITH ostsum-tmpsum
	      snsum=snsum-tmpsum
	      SKIP
	    ENDDO
	  ENDIF
	  SELECT rcr
	ENDSCAN
В2
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT DISTINCT dt FROM T2 ORDER BY  1  INTO CURSOR cr_dt
SELECT cr_dt
SCAN
  SELECT * FROM T2 WHERE T2.dt=cr_dt.dt INTO CURSOR T2_tmp READWRITE
  UPDATE T2_tmp SET ostsum=ostsum-IIF(sn_sum<ostsum,sn_sum,ostsum);
    FROM T2_tmp inner JOIN rcr ON T2_tmp.id1=rcr.id1 AND T2_tmp.id2=rcr.id2;
  UPDATE rcr        SET sn_sum=sn_sum-IIF(sn_sum<ostsum,sn_sum,ostsum);
    FROM T2     inner JOIN rcr ON T2.id1    =rcr.id1 AND T2.id2    =rcr.id2;
    WHERE T2.dt=cr_dt.dt
  UPDATE T2 SET ostsum=T2_tmp.ostsum;
    FROM T2 inner JOIN T2_tmp ON T2.id1=T2_tmp.id1 AND T2.id2=T2_tmp.id2;
    WHERE T2.dt=cr_dt.dt
ENDSCAN
...
Рейтинг: 0 / 0
09.06.2009, 20:02
| Ответить | Цитировать | Написать  
Изменение данных сразу в двух таблицах. как лучше?
    #36034773
Tohan_ORA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tohan_ORA
Гость
tanglir,

Если в t1 id1,id2-уникальны в пределах все таблички, то можно попробовать нижеприведенным способом,
а остатки ,если они есть , по второй табличке должны приходится всегда на последнюю дату если я ничего не намудрил ...:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE CURSOR t1(id1 int ,id2 int,sm int)
CREATE CURSOR t2(id1 int ,id2 int,dt date,sm int)

INSERT INTO t1 values( 1 , 1 , 100 )
INSERT INTO t1 values( 1 , 2 , 30 )
INSERT INTO t1 values( 1 , 3 , 10 )
INSERT INTO t1 values( 1 , 4 , 9 )

INSERT INTO t2 values( 1 , 1 ,DATE(), 65 )
INSERT INTO t2 values( 1 , 1 ,DATE()+ 5 , 90 )
INSERT INTO t2 values( 1 , 2 ,DATE(), 30 )
INSERT INTO t2 values( 1 , 2 ,DATE()+ 5 , 90 )
INSERT INTO t2 values( 1 , 3 ,DATE(), 1 )
INSERT INTO t2 values( 1 , 3 ,DATE()+ 5 , 1 )

SELECT * FROM t1 INTO CURSOR t1upd readwrite
SELECT * FROM t2 INTO CURSOR t2upd readwrite

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT t1.id1,t1.id2,t1.sm,IIF(t1.sm<SUM(t2.sm),CAST( 0  as int),t1.sm-SUM(t2.sm)) as ost_t1,MAX(t2.dt) dt,IIF(t1.sm>SUM(t2.sm),CAST( 0  as int),SUM(t2.sm)-t1.sm) ost_t2;
from t1 JOIN t2;
on t1.id1 =t2.id1 AND t1.id2 =t2.id2;
GROUP BY t1.id1,t1.id2,t1.sm INTO CURSOR result

UPDATE a SET sm=b.ost_t1;
from t1upd a join result b;
on a.id1=b.id1 and a.id2=b.id2

UPDATE a SET sm=IIF(a.dt<b.dt,CAST( 0  as int),ost_t2);
from t2upd a join result b;
on a.id1=b.id1 and a.id2=b.id2
...
Рейтинг: 0 / 0
10.06.2009, 10:07
| Ответить | Цитировать | Написать  
Изменение данных сразу в двух таблицах. как лучше?
    #36035164
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Tohan_ORA, это было то, о чём я подумал в первую очередь. Но, насколько я понимаю, в результате этого
Код: plaintext
1.
2.
UPDATE a SET sm=IIF(a.dt<b.dt,CAST( 0  as int),ost_t2);
from t2upd a join result b;
on a.id1=b.id1 and a.id2=b.id2
запроса от каждой соответствующей суммы в Т2(кроме последней) отнимется сумма, снимаемая с Т1. А если в Т2 несколько записей с такими id1,id2, то она отнимется ведь несколько раз и получится, что с Т1 сняли например 100, а с Т2 в сумме сняли 100*(кол-во записей с соотв-ми id1,id2 в Т2, кроме записи с максимальным для Т2 временем). А вот как раз этого мне и не надо.
Tohan_ORAа остатки ,если они есть , по второй табличке должны приходится всегда на последнюю дату Но мне-то надо отслеживать, с какой даты они уже "ушли", а на какой ещё "остались". Скинуть всё в кучу на последнюю дату - решило бы проблему "параллельного" изменения данных, но создало бы другую - отследить, откуда взялась и почему снялась какая-то конкретная сумма, стало бы вообще невозможно
...
Рейтинг: 0 / 0
10.06.2009, 12:06
| Ответить | Цитировать | Написать  
Изменение данных сразу в двух таблицах. как лучше?
    #36035441
Tohan_ORA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tohan_ORA
Гость
tanglirTohan_ORA, это было то, о чём я подумал в первую очередь. Но, насколько я понимаю, в результате этого
Код: plaintext
1.
2.
UPDATE a SET sm=IIF(a.dt<b.dt,CAST( 0  as int),ost_t2);
from t2upd a join result b;
on a.id1=b.id1 and a.id2=b.id2
запроса от каждой соответствующей суммы в Т2(кроме последней) отнимется сумма, снимаемая с Т1. А если в Т2 несколько записей с такими id1,id2, то она отнимется ведь несколько раз и получится, что с Т1 сняли например 100, а с Т2 в сумме сняли 100*(кол-во записей с соотв-ми id1,id2 в Т2, кроме записи с максимальным для Т2 временем).
Честно говоря не совсем понятно,что вы имеете в виду .
в табл 2 проставится итоговый остаток вычисленный в result который всегда будет приходится на последнюю дату по группе ид1,ид2
Приведите пример данных когда запрос будет выдавать 'не правильный' с вашей точки зрения результат.
Что есть:
t1
id1id2sm1110012301310149
t2
id1id2dtsm1110-Jun-09651115-Jun-09901210-Jun-09291215-Jun-09901310-Jun-0911315-Jun-091
Результат запроса
result
id1id2smost_t1dtost_t211100015-Jun-09551230015-Jun-09891310815-Jun-090
Что будет:
t1upd
id1id2sm110120138149
t2upd
id1id2dtsm1110-Jun-0901115-Jun-09551210-Jun-0901215-Jun-09901310-Jun-0901315-Jun-090
...
Рейтинг: 0 / 0
10.06.2009, 13:34
| Ответить | Цитировать | Написать  
Изменение данных сразу в двух таблицах. как лучше?
    #36035464
Tohan_ORA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tohan_ORA
Гость
Поправлю:
Result
id1id2smost_t1dtost_t211100015-Jun-09551230015-Jun-09901310815-Jun-090
...
Рейтинг: 0 / 0
10.06.2009, 13:42
| Ответить | Цитировать | Написать  
Изменение данных сразу в двух таблицах. как лучше?
    #36036445
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Tohan_ORAв табл 2 проставится итоговый остаток вычисленный в result который всегда будет приходится на последнюю дату по группе ид1,ид2
Пример: Т1
1 1 1001 2 200
Т2
1 1 2009-03-01 201 1 2009-04-01 1001 1 2009-05-01 2501 2 2009-04-01 95
Должно получиться
1 1 01 2 105
1 1 2009-03-01 01 1 2009-04-01 80 1 1 2009-05-01 250 1 2 2009-04-01 0

А получается
1 1 01 2 105
1 1 2009-03-01 01 1 2009-04-01 0 1 1 2009-05-01 330 1 2 2009-04-01 300

То есть ВСЯ сумма уходит на последнюю дату, чего мне категорически не надо.
...
Рейтинг: 0 / 0
10.06.2009, 19:54
| Ответить | Цитировать | Написать  
Изменение данных сразу в двух таблицах. как лучше?
    #36036453
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Поправлюсь
надо так
Должно получиться
1 1 01 2 105
1 1 2009-03-01 01 1 2009-04-01 20 1 1 2009-05-01 250 1 2 2009-04-01 0
А выходит
1 1 01 2 105
1 1 2009-03-01 01 1 2009-04-01 0 1 1 2009-05-01 270 1 2 2009-04-01 300
...
Рейтинг: 0 / 0
10.06.2009, 19:58
| Ответить | Цитировать | Написать  
Изменение данных сразу в двух таблицах. как лучше?
    #36036477
Tohan_ORA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tohan_ORA
Гость
Так пойдет?
Называется это остаток с нарастающим итогом.
Возможно можно ещё более упростить,это запрос на вскидку ... :-)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE CURSOR t1(id1 int ,id2 int,sm int)
CREATE CURSOR t2(id1 int ,id2 int,dt date,sm int)
INSERT INTO t1 values( 1 , 1 , 100 )
INSERT INTO t1 values( 1 , 2 , 30 )
INSERT INTO t1 values( 1 , 3 , 10 )
INSERT INTO t1 values( 1 , 4 , 9 )
INSERT INTO t2 values( 1 , 1 ,DATE(), 65 )
INSERT INTO t2 values( 1 , 1 ,DATE()+ 5 , 90 )
INSERT INTO t2 values( 1 , 2 ,DATE(), 30 )
INSERT INTO t2 values( 1 , 2 ,DATE()+ 5 , 90 )
INSERT INTO t2 values( 1 , 3 ,DATE(), 1 )
INSERT INTO t2 values( 1 , 3 ,DATE()+ 5 , 1 )

INSERT INTO t1 values( 1 , 5 , 3 )
INSERT INTO t2 values( 1 , 5 ,DATE(), 4 )
INSERT INTO t2 values( 1 , 5 ,DATE()+ 1 , 10 )
SELECT * FROM t1 INTO CURSOR t1upd readwrite
SELECT * FROM t2 INTO CURSOR t2upd readwrite

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SELECT t1.id1,t1.id2,MIN(dt)- 1  dt,-t1.sm as sm FROM t2 JOIN t1 on t1.id1 =t2.id1 AND t1.id2 =t2.id2;
GROUP BY t1.id1,t1.id2,t1.sm union all select id1,id2,dt,sm from t2 INTO CURSOR pl

SELECT a.id1,a.id2,a.dt,;
IIF(a.sm+IIF(SUM(b.sm)< 0 ,SUM(b.sm),CAST( 0  as int))>= 0 ,a.sm+IIF(SUM(b.sm)< 0 ,SUM(b.sm),CAST( 0  as int)),CAST( 0  as int)) ost2;
from t2 a LEFT JOIN pl b;
on a.id1 =b.id1 AND a.id2 =b.id2 and b.dt<a.dt;
GROUP BY a.id1,a.id2,a.dt,a.sm;
into CURSOR result2

SELECT t1.id1,t1.id2,t1.sm,IIF(t1.sm<SUM(t2.sm),CAST( 0  as int),t1.sm-SUM(t2.sm)) as ost_t1;
from t1 JOIN t2;
on t1.id1 =t2.id1 AND t1.id2 =t2.id2;
GROUP BY t1.id1,t1.id2,t1.sm INTO CURSOR result

UPDATE a SET sm=b.ost_t1;
from t1upd a join result b;
on a.id1=b.id1 and a.id2=b.id2

UPDATE a SET sm=ost2;
from t2upd a join result2 b;
on a.id1=b.id1 and a.id2=b.id2 AND a.dt=b.dt
...
Рейтинг: 0 / 0
10.06.2009, 20:22
| Ответить | Цитировать | Написать  
Изменение данных сразу в двух таблицах. как лучше?
    #36036531
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Tohan_ORA,
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
SELECT t1.id1,t1.id2,MIN(dt)- 1  dt,-t1.sm as sm FROM t2 JOIN t1 on t1.id1 =t2.id1 AND t1.id2 =t2.id2;
GROUP BY t1.id1,t1.id2,t1.sm union all select id1,id2,dt,sm from t2 INTO CURSOR pl

SELECT a.id1,a.id2,a.dt,;
IIF(a.sm+IIF(SUM(b.sm)< 0 ,SUM(b.sm),CAST( 0  as int))>= 0 ,a.sm+IIF(SUM(b.sm)< 0 ,SUM(b.sm),CAST( 0  as int)),CAST( 0  as int)) ost2;
from t2 a LEFT JOIN pl b;
on a.id1 =b.id1 AND a.id2 =b.id2 and b.dt<a.dt;
GROUP BY a.id1,a.id2,a.dt,a.sm;
into CURSOR result2
Вот оно, то, о чём я спрашивал! Спасибо!
PS. Узнал/понял кое-что новое про join'ы с условием неравенства и агрегатные функции.
Оппаньки. Ошибочка с моей стороны. Забыл упомянуть, что в Т2 могут таки быть и отрицательные суммы (соответственно увеличивающие значения в Т1). С ними не работает :(
Хм, интересно, а что мне вообще с минусами надо будет делать? Если заменить в последнем примере строку "1 1 2009-05-01 250" на "1 1 2009-05-01 -80", то снимать надо будет только 40 рублей, но мои алгоритмы снимут их с первой сотни и оставят 60,20 и -80 рублей соответственно, Ваш же запрос просто проигнорирует минуса.
Короче, буду с минусами разбираться, отпишусь, когда станет ясно, что с ними вообще делать.
...
Рейтинг: 0 / 0
10.06.2009, 21:38
| Ответить | Цитировать | Написать  
Изменение данных сразу в двух таблицах. как лучше?
    #36036577
Фотография ВладимирМ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ВладимирМ
Участник
Можно, конечно, придумать "хитромудрый" запрос (например, в последнем примере еще проверять SUM() на знак), только, как ты потом во всей этой "каше" разбираться будешь? Через месяц...полтора, когда понадобиться кое-что изменить в алгоритме?

Пиши циклы. Во-первых, они понятнее, а, во-вторых, в них проще вносить модификации, если понадобиться.

Насколько я понимаю, T1 - это некоторый "кредит" и надо вычесть этот "кредит" из сумм платежей. Причем вычитание идет по датам. Ну, и решай эту задачу "в лоб"

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
* Предполагаю, что пара кодов id1 и id2 уникальны в пределах таблицы T1
* Тогда таблицы T1 становится управляющей таблицей всего процесса
select T1
SCAN
	* Сумма кредита
	lnTotalSum = T1.summa

	* Делаю выборку из таблицы T2, упорядочивая по убыванию даты
	Select dt,ost_sum ;
	from T2 ;
	into cursor curNext nofilter ;
	where t2.id1 = t1.id1 and t2.id2 = t1.id2 ;
	order by dt
	
	* организую сканирование по выборке до тех пор, пока
	* сумма не превысит общую сумму кредита
	lnDetailSum =  0 
	ldLastDate = {}
	Select curNext
	Go top
	Scan while lnDetailSum < lnTotalSum
		lnDetailSum = lnDetailSum + curNext.ost_sum
		ldLastDate = curNext.dt
	EndScan
	
	* Цикл может закончится по  2  причинам: выполнилось условие или закончилась выборка
	* В любом случае, ldLastDate будет содержать дату той записи после которой модифицировать 
	* ничего не надо. До нее надо все суммы обнулить. А в ней самой записать разницу
	* сумм кредита и полученной итоговой суммы строк платежей
	
	* Обнуление всех записей с датой меньше ldLastDate
	Update T2 ;
	set ost_sum =  0  ;
	where t2.id1 = t1.id1 and t2.id2 = t1.id2 and t2.dt < ldLastDate
	
	* Для записи с датой равной ldLastDate
	* Если итоговая сумма меньше или равна сумме кредита, то просто обнуляю ее
	* Если больше, то записываю разницу 
	Update T2 ;
	set ost_sum = iif(lnDetailSum <= lnTotalSum,  0 , lnDetailSum - lnTotalSum) ;
	where t2.id1 = t1.id1 and t2.id2 = t1.id2 and t2.dt = ldLastDate

	* Теперь изменение итоговой суммы кредита в ТЕКУЩЕЙ записи таблицы T1
	* Если итоговая сумма меньше или равна сумме кредита, то записываю разницу
	* Если больше, то обнуляю
	REPLACE summa WITH iif(lnDetailSum <= lnTotalSum, lnDetailSum - lnTotalSum,  0 )
	
ENDSCAN

Если в таблице T2 возможно существование нескольких записей с одинаковой датой для одних и тех же кодов id1 и id2, то вместо ldLastDate надо записывать идентификатор записи таблицы T2. Т.е. поле или набор полей по которым можно однозначно идентифицировать запись.
...
Рейтинг: 0 / 0
10.06.2009, 22:47
| Ответить | Цитировать | Написать  
Изменение данных сразу в двух таблицах. как лучше?
    #36036708
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
ВладимирМЕсли в таблице T2 возможно существование нескольких записей с одинаковой датой для одних и тех же кодов id1 и id2, то вместо ldLastDate надо записывать идентификатор записи таблицы T2. Т.е. поле или набор полей по которым можно однозначно идентифицировать запись. Да нет, такого не предвидится (тройка id1,id2,dt уникальна в Т2). А в общем, Вы подтвердили мою первоначальную мысль - делать через циклы. Спасибо всем за помощь!
...
Рейтинг: 0 / 0
11.06.2009, 06:45
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / FoxPro, Visual FoxPro [игнор отключен] [закрыт для гостей] / Изменение данных сразу в двух таблицах. как лучше?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=41&msg=36036453&tid=1586335]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 33ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 41ms
get tp. blocked users:
 1ms
others: 348ms
total:  463ms
созд. / загр.: 463ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]