ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Запрос на объединение двух таблиц (Акт сверки)
13 сообщений из 13, страница 1 из 1
Запрос на объединение двух таблиц (Акт сверки)
    #39612108
Tsimfer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tsimfer
Участник
Добрый день.
С некоторых пор решил перевести свою задачу на SQL. Простые отчеты/запросы уже получаются. Возникла проблема с "Актом сверки". Создается по 4 таблицам, но можно упростить для понимания до 2 таблиц:
zim.ttn1 - накладные по отгруженному товару (Дата отгрузки, Номер накладной, Сумма)
zim.pay1 - поступившие платежи (Дата платежа, Номер платежки, Сумма платежа)
 
При помощи генератора создаю две таблички
Код: sql
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.
49.
50.
51.
52.
53.
54.
CREATE TABLE zim.pay1 (
   Id                  integer PRIMARY KEY NOT NULL,
   Dat                 date,
   Doc                 integer,
   Pay                 integer );

INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('1','2018-02-01','937','911252');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('2','2018-02-28','181','326052');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('3','2018-03-03','467','470024');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('4','2017-12-14','600','106685');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('5','2018-02-14','921','645725');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('6','2018-02-12','758','110126');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('7','2018-02-02','610','890294');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('8','2017-12-18','959','993302');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('9','2018-02-19','770','33713');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('10','2018-03-04','785','172951');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('11','2017-12-08','770','528012');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('12','2017-12-20','374','514790');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('13','2018-01-17','832','621752');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('14','2018-02-19','146','763796');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('15','2018-01-25','134','955523');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('16','2018-02-03','98','884199');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('17','2018-03-01','199','38606');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('18','2018-01-03','859','242682');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('19','2018-02-13','909','310913');
INSERT INTO zim.pay1 (Id,Dat,Doc,Pay) VALUES ('20','2018-02-11','366','847180');

CREATE TABLE zim.ttn1 (
   Id                  integer PRIMARY KEY NOT NULL,
   Dat                 date,
   Ttn                 integer,
   Pay                 integer
);

INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('1','2017-12-19','1','885013');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('2','2017-12-10','3','361943');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('3','2017-12-28','5','56815');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('4','2018-01-16','7','353525');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('5','2018-01-02','9','391232');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('6','2018-01-17','11','931402');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('7','2018-01-05','13','304527');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('8','2018-02-24','15','763902');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('9','2018-01-15','17','488186');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('10','2018-01-09','19','501740');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('11','2018-03-06','21','366875');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('12','2018-03-06','23','165024');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('13','2017-12-21','25','355546');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('14','2018-02-26','27','332432');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('15','2018-01-26','29','540607');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('16','2017-12-26','31','364214');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('17','2017-12-25','33','502502');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('18','2017-12-27','35','421590');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('19','2018-03-08','37','480366');
INSERT INTO zim.ttn1 (Id,Dat,Ttn,Pay) VALUES ('20','2018-01-18','39','75247');



Создал два запроса, первый выводит инфу по отгрузке, другой по платежам, объединил их в одну таблицу через UNION ALL. Первая колонка - Год/Месяц, для последующего объединения/сортировки, последняя не нужна, но без нее GROUP не будет работать.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT s.mm, s.dat1, s.doc1, s.pay1, s.dat2, s.doc2, s.pay2, count(s.mm) as mmm
FROM (
( SELECT Extract(year from h.dat)*100 + Extract(month from h.dat) AS mm, h.dat as dat1, h.ttn as doc1, h.pay AS pay1, NULL as dat2, 0 as doc2, 0 as pay2
FROM zim.ttn1 h
WHERE h.dat BETWEEN '20180101' and '20180301' ) 
UNION ALL
( SELECT Extract(year from p.dat)*100 + Extract(month from p.dat) AS mm, NULL as dat1, 0 as doc1, 0 as pay1, p.dat as dat2, p.doc as doc2, p.pay as pay2
FROM zim.pay1 p
WHERE p.dat BETWEEN '20180101' and '20180301' ) 
) s
GROUP BY s.mm, s.dat1, s.doc1, s.pay1, s.dat2, s.doc2, s.pay2
ORDER BY  s.mm, s.dat1


Результат следующий:
Не совсем разобрался с картинками, прикрепил к вопросу. Результат есть, но можно лучше. Надо платежи (значения в 5,6,7 колонках) поднять вверх, к началу соответствующего месяца, чтобы было меньше строк в отчете. За первый месяц должно быть 8 строчек (а не 11), за второй - 10 строчек (а не 12). То-есть первые строчки должны быть:
2018012018-01-02 93912322018-01-038592426822018012018-01-05133045272018-01-17832621752
Да, вообще-то я использую MS SQL, но дома стоит Postgre, поэтому привожу на Postgre. Скажите пожалуйста, куда копать? Буду благодарен за любой совет
...
Рейтинг: 0 / 0
07.03.2018, 23:52
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612171
sereginseregin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sereginseregin
Гость
Tsimfer,


Как в MS SQL не скажу.

В PostreSQL делаем также два подзапроса. В каждом подзапросе дополнительное поле с нумерацией последовательности операций внутри месяца. Затем, подзапросы соединяем не по UNION, а через FULL JOIN по месяцу и номеру последовательности.

Нумерацию делаем двумя вариантами:
- Оконный row_number() OVER (PARTITION BY Extract(year from h.dat), Extract(month from h.dat) ORDER BY h.dat)
- Когда оконных функций не было, использовали (костыль) функцию, которая создавала временные сиквенсы CREATE TEMP SEQUENCE для каждого месяца в процессе отбора записей
...
Рейтинг: 0 / 0
08.03.2018, 11:15
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612211
Tsimfer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tsimfer
Участник
sereginseregin,
Спасибо, буду пробовать. Да, а мне сказали, что MS SQL и Postgres совместимы, а тут читаю, ну "все не по нашему" !
...
Рейтинг: 0 / 0
08.03.2018, 12:50
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612227
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Tsimfer,

А что есть в MSSQL, чего вам не хватает для решения данной задачи?..
...
Рейтинг: 0 / 0
08.03.2018, 13:31
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612344
Tsimfer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tsimfer
Участник
sereginseregin,
Еще раз благодарю, у меня все получилось!
 
Запрос следующий
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT s.m1, s.num1, s.dat, s.ttn, s.pay, t.dat, t.doc, t.pay
FROM 
( SELECT Extract(year from h.dat)*100 + Extract(month from h.dat) AS m1, row_number() over (PARTITION BY Extract(year from h.dat),Extract(month from h.dat) ORDER BY h.dat) as num1, h.dat, h.ttn, h.pay
FROM zim.ttn1 h
WHERE h.dat BETWEEN '20180101' and '20180301' 
ORDER BY h.dat ) s
FULL JOIN
( SELECT Extract(year from p.dat)*100 + Extract(month from p.dat) AS m2, row_number() over (PARTITION BY Extract(year from p.dat),Extract(month from p.dat) ORDER BY p.dat) as num2, p.dat, p.doc, p.pay
FROM zim.pay1 p
WHERE p.dat BETWEEN '20180101' and '20180301' 
ORDER BY p.dat ) t
ON  s.m1=t.m2 and s.num1=t.num2


Результат прикреплен снизу.
vyegorov,
я так понимаю, в MS SQL нет row_number. Завтра буду читать форум, искать аналог, точнее уже нашел топик, буду разбираться
...
Рейтинг: 0 / 0
08.03.2018, 21:35
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612482
cnfhbxjr
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
cnfhbxjr
Гость
Tsimferкуда копать? Буду благодарен за любой совет
хм, тёзка, фома неверующий, говорил же - "см. оконные функции..."
...
Рейтинг: 0 / 0
09.03.2018, 11:51
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612574
Tsimfer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tsimfer
Участник
cnfhbxjr,
Да, ты прав оказался
 
Вариант для MS SQL
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT s.m1, s.num1, t.m2, t.num2, s.dat1, s.doc1, s.pay1, t.dat2, t.doc2, t.pay2
FROM 
( SELECT year(h.dat)*100 + month(h.dat) AS m1, ROW_NUMBER() OVER(PARTITION BY year(h.dat)*100 + month(h.dat) ORDER BY h.dat) AS num1, h.dat as dat1, h.ttn as doc1, h.pay AS pay1
FROM db.zim.ttn1 as h 
WHERE h.dat BETWEEN '20180101' and '20180301' 
 ) s
FULL JOIN
( SELECT year(p.dat)*100 + month(p.dat) AS m2, ROW_NUMBER() OVER(PARTITION BY year(p.dat)*100 + month(p.dat) ORDER BY p.dat) AS num2, p.dat as dat2, p.doc as doc2, p.pay as pay2
FROM db.zim.pay1 as p 
WHERE p.dat BETWEEN '20180101' and '20180301' 
 ) t
ON s.m1=t.m2 and s.num1=t.num2


Результат внизу. Конечно в итоговый репорт в колонки m1 и num1 надо вместо NULL добавить значения из m2 и num2, и отсортировать. Но это уже в самом отчете можно сделать. Всем спасибо
...
Рейтинг: 0 / 0
09.03.2018, 15:00
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612620
cnfhbxjr
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
cnfhbxjr
Гость
Tsimfer,

"неправильно это", если была идея ВСЁ на сервере, значит надо до конца вгрызаться..
...
Рейтинг: 0 / 0
09.03.2018, 16:48
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612727
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Tsimfer,

Конечно в итоговый репорт в колонки m1 и num1 надо вместо NULL добавить значения из m2 и

Есть магическая функция coalesce для таких задач:
coalesce(s.m1, t.m2), coalesce(s.num1, t.num2)
...
Рейтинг: 0 / 0
10.03.2018, 04:30
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612838
cnfhbxjr
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
cnfhbxjr
Гость
Maxim Boguk,

+
Тёзка, как говорит молодежь - круто, это настоящий Динозавр PG тебе подсказал, цени..
...
Рейтинг: 0 / 0
10.03.2018, 19:09
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612965
Tsimfer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tsimfer
Участник
Maxim Boguk, спасибо, буду пробовать...
...
Рейтинг: 0 / 0
11.03.2018, 13:20
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39612994
Tsimfer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Tsimfer
Участник
cnfhbxjr,
тут еще один ляп увидел, см. в последней таблице строки 11-12, колонка DOC2.
По хорошему, два платежа за один день надо бы отсортировать по возрастанию, а ORDER BY тут уже не прокатит.
Да и накладных может быть несколько в один день. Придется сливать селекты во временные таблицы.
 
Примерно так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
if object_id('tempdb..#table0') is not null drop table #table0  -- проверка на существование таблиц
if object_id('tempdb..#table2') is not null drop table #table2 

SELECT year(h.dat)*100 + month(h.dat) AS m1, ROW_NUMBER() OVER(PARTITION BY year(h.dat)*100 + month(h.dat) ORDER BY h.dat) AS num1, h.dat as dat1, h.ttn as doc1, h.pay AS pay1
INTO #table0
FROM db.zim.ttn1 as h 
WHERE h.dat BETWEEN '20180101' and '20180301' 
ORDER BY h.dat, h.ttn

SELECT year(p.dat)*100 + month(p.dat) AS m2, ROW_NUMBER() OVER(PARTITION BY year(p.dat)*100 + month(p.dat) ORDER BY p.dat) AS num2, p.dat as dat2, p.doc as doc2, p.pay as pay2
INTO #table2
FROM db.zim.pay1 as p 
WHERE p.dat BETWEEN '20180101' and '20180301' 
ORDER BY p.dat, p.doc

SELECT s.m1, s.num1, t.m2, t.num2, s.dat1, s.doc1, s.pay1, t.dat2, t.doc2, t.pay2
FROM #table0 s FULL JOIN #table2 t  ON s.m1=t.m2 and s.num1=t.num2


В результате платежи за 2018-02-19 стоят как надо (см. строки 16-17). Да, не думал, что маленький "Акт сверки" так разрастется.
...
Рейтинг: 0 / 0
11.03.2018, 15:40
| Ответить | Цитировать | Написать  
Запрос на объединение двух таблиц (Акт сверки)
    #39613087
cnfhbxjr
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
cnfhbxjr
Гость
Tsimfercnfhbxjr,
тут еще один ляп увидел, см. в последней таблице строки 11-12, колонка DOC2.
По хорошему, два платежа за один день надо бы отсортировать по возрастанию, а ORDER BY тут уже не прокатит.
Да и накладных может быть несколько в один день. Придется сливать селекты во временные таблицы.

вероятно что не надо этого, ты "погорячился" из-за фразы "все на сервере...") - если результат запроса потом все равно будешь в своей среде считывать и писать в свою базу... - так и считываешь записи в любой последовательности/сортировке из результата запроса, а потом при записи в твою итоговую базу или врем.таблицу уже все сразу и построится как надо по ключам для бровза/репорта или я что-то не так понял?
...
Рейтинг: 0 / 0
11.03.2018, 22:35
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Запрос на объединение двух таблиц (Акт сверки)
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39612965&tid=1995899]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 32ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 285ms
total:  408ms
созд. / загр.: 408ms
x
x
Закрыть


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