Re: Limitting full join to one match
| От | John W Higgins |
|---|---|
| Тема | Re: Limitting full join to one match |
| Дата | |
| Msg-id | CAPhAwGw2n4ZNRL6DNaYV0wgRiidp6LW-dj_GVAxaCJK_JB2Q6A@mail.gmail.com обсуждение исходный текст |
| Ответ на | Limitting full join to one match ("Phil Endecott" <spam_from_pgsql_lists@chezphil.org>) |
| Ответы |
Re: Limitting full join to one match
|
| Список | pgsql-general |
On Wed, Dec 5, 2018 at 4:34 PM Phil Endecott <spam_from_pgsql_lists@chezphil.org> wrote:
Dear Experts,
I have a couple of tables that I want to reconcile, finding rows
that match and places where rows are missing from one table or the
other:
...
So my question is: how can I modify my query to output only two rows,
like this:?
+------------+--------+------------+--------+
| date | amount | date | amount |
+------------+--------+------------+--------+
| 2018-01-01 | 10.00 | 2018-01-01 | 10.00 |
| 2018-02-01 | 5.00 | | |
| | | 2018-03-01 | 8.00 |
| 2018-04-01 | 5.00 | 2018-04-01 | 5.00 |
| 2018-05-01 | 20.00 | 2018-05-01 | 20.00 | 1
| 2018-05-01 | 20.00 | 2018-05-01 | 20.00 | 2
+------------+--------+------------+--------+
Evening Phil,
Window functions are your friend here. I prefer views for this stuff - but subqueries would work just fine.
create view a_rows as (select *,
row_number() OVER (PARTITION BY date, amount) AS pos from a);
create view b_rows as (select *,
row_number() OVER (PARTITION BY date, amount) AS pos from b);
select
a_rows.date,
a_rows.amount,
a_rows.pos,
b_rows.date,
b_rows.amount,
b_rows.pos
from
a_rows full join b_rows using (date,amount,pos);
Example here - http://sqlfiddle.com/#!17/305d6/3
John
Any suggestions anyone?
The best I have found so far is something involving EXCEPT ALL:
db=> select * from a except all select * from b;
db=> select * from b except all select * from a;
That's not ideal, though, as what I ultimately want is something
that lists everything with its status:
+------------+--------+--------+
| date | amount | status |
+------------+--------+--------+
| 2018-01-01 | 10.00 | OK |
| 2018-02-01 | 5.00 | a_only |
| 2018-03-01 | 8.00 | b_only |
| 2018-04-01 | 5.00 | OK |
| 2018-05-01 | 20.00 | OK |
| 2018-05-01 | 20.00 | OK |
+------------+--------+--------+
That would be easy enough to achieve from the JOIN.
Thanks, Phil.
В списке pgsql-general по дате отправления: