postgreSQL如何访问不同表的元素?

Question

我有这个表[pets]

<表类> 动物 prev_store curr_store tbody><<tr>猫道明>DEF狗ABCGHI鱼DEFXYZ蛇XYZJKM

您必须在查询中以两个角色使用pets_store两次:

WITH
-- your data, don't use in query ..
pets(Animal,prev_store,curr_store) AS (
SELECT 'Cat'  ,'ABC','DEF'
UNION ALL SELECT 'Dog'  ,'ABC','GHI'
UNION ALL SELECT 'Fish' ,'DEF','XYZ'
UNION ALL SELECT 'Snake','XYZ','JKM'
)
,
pets_store(Store,Country) AS (
SELECT 'ABC','England'
UNION ALL SELECT 'DEF','Denmark'
UNION ALL SELECT 'GHI','England'
UNION ALL SELECT 'XYZ','Denmark'
UNION ALL SELECT 'JKM','Denmark'
)
-- real query starts here ...
SELECT
prev_stores.country AS country
, COUNT(*)            AS occurrences
FROM pets
JOIN pets_store prev_stores ON prev_store = prev_stores.store
JOIN pets_store curr_stores ON curr_store = curr_stores.store
WHERE prev_stores.country = curr_stores.country
GROUP BY prev_stores.country;
-- out  country | occurrences 
-- out ---------+-------------
-- out  Denmark |           2
-- out  England |           1