PostgreSQL Duplicate String Counting Connection

Question

I have one difficult question. I will try to explain this with an example:

there is one table with a primary key, and I want to join another table, there the first primary key of the table is the foreign key, and I want the second table to have a duplicate of the foreign key to select the amount of repeatability. For instance:

1st table:

id name --- ----- 1 Greg 2 Alan 3 George 4 John 5 Peter 

2nd table

  id aid data --- ----- ------- 1 2 CCCV 2 2 VVVV 3 3 DDDDD 4 3 SSSS 5 4 PPPPP 

I want the result of the union to be:

  id(1st table) aid name Data Number ----------- ---- ----- ----- ----- 1 null Greg null 1 2 1 Alan CCCV 1 2 2 Alan VVVV 2 3 3 George DDDDD 1 3 4 George SSSS 2 4 5 John PPPPP 1 5 null Peter null 1 

I searched a lot, could not find anything. Maybe I don’t know how to look, or there isn’t such a thing as what I want to do.

Answer 1

 SELECT Table1.id, Table2.id as aid, Table1.name, Table2.data, GREATEST(1, (SELECT COUNT(*) FROM Table2 t2 WHERE t2.aid = Table1.id AND t2.id <= Table2.id)) AS number FROM Table1 LEFT JOIN Table2 ON Table2.aid = Table1.id ORDER BY id, aid; 

works in both MySQL and PostgreSQL.

Answer 2

According to my comment, you have noted this with both MySQL and PostgreSQL.

This answer is for PostgreSQL.

 SELECT table1.id, table2.aid, table1.name, table2.data, ROW_NUMBER() OVER (PARTITION BY table1.id ORDER BY table2.aid) AS number FROM table1 LEFT JOIN table2 ON table1.id = table2.aid 

Answer 3

^{Queries for PostgreSQL 8.3 that do not have window functions.}
With large tables, it is often much faster to use a JOIN instead of a correlated subquery .
The first query aggregates the values ​​for Table2 before joining Table1 , which should also be faster:

 SELECT t1.id, t2.aid, t1.name, t2.data, COALESCE(t2.ct, 1) AS number FROM Table1 t1 LEFT JOIN ( SELECT x.aid, x.data, count(y.aid) + 1 AS ct FROM Table2 x LEFT JOIN Table2 y ON x.aid = y.aid AND x.id > y.id GROUP BY x.aid, x.data ) t2 ON t2.aid = t1.id ORDER BY t1.id, t2.ct; 

And ORDER BY needs to be fixed.

Alternative without subquery. Perhaps it will be faster:

 SELECT t1.id, t2.aid, t1.name, t2.data, count(*) + count(t3.id) AS number FROM Table1 t1 LEFT JOIN Table2 t2 ON t2.aid = t1.id LEFT JOIN Table2 t3 ON t3.aid = t2.aid AND t3.id < t2.id GROUP BY t1.id, t2.aid, t1.name, t2.data ORDER BY t1.id, count(t3.id); 

Not sure, not tested with a large set. Check performance with EXPLAIN ANALYZE . Could you report your results?