postgresql - 跨两个表一对一左连接

Question

所以..这是我的查询:

WITH cat AS (
    SELECT day, domain
    FROM table1
    GROUP BY day, domain
), dog AS (
    SELECT day, domain, SUM(count)
    FROM table2
    GROUP BY day, domain
)
SELECT c.domain,
    COALESCE(SUM(d.count),0) AS count
FROM cat c
LEFT JOIN dog d
    ON c.domain = d.domain
    AND c.day <= d.day
GROUP BY c.domain;

这是“猫”返回的内容:

    day     |  domain 
------------+-----------
 2015-10-01 | nba.com 
 2015-10-02 | nba.com 

这是“狗”返回的内容:

    day     | domain     | count 
------------+----------------+--------
 2015-10-03 | nba.com    |   2 

下面是完整查询返回的内容:

domain     | count 
------------+-------
nba.com    |   4

计数为 4，因为 LEFT JOIN 满足“cat”中的两行。但是，我希望左连接只应用一次……(即计数为 2 而不是 4)。那就是 2 的“狗”计数只能应用一次(如果它曾经被满足的话)......并且不会超过那个。这可能吗？我希望我在这里说得通

Answer 1

你的问题不是很清楚。

如果你想从 dog 中找到第一行:

WITH cat AS (
    SELECT day, domain
    FROM table1
    GROUP BY day, domain
), dog AS (
    SELECT day, domain, SUM(count) count
    FROM table2
    GROUP BY day, domain
)
SELECT DISTINCT on (c.domain) c.domain,
    COALESCE(d.count, 0) AS count
FROM cat c
LEFT JOIN dog d
    ON c.domain = d.domain
    AND c.day <= d.day
ORDER BY c.domain, c.day, d.day;

如果要对dog(满足条件)的所有行求和:

WITH cat AS (
    SELECT day, domain
    FROM table1
    GROUP BY day, domain
), dog AS (
    SELECT day, domain, SUM(count) count
    FROM table2
    GROUP BY day, domain
), cat_and_dogs as (
    SELECT DISTINCT ON(c.domain, d.day) c.domain, d.count
    FROM cat c
    LEFT JOIN dog d
        ON c.domain = d.domain
        AND c.day <= d.day
)
SELECT domain,
    COALESCE(sum(count), 0) AS count
FROM cat_and_dogs
GROUP BY domain;