Mysql sum distinct 基于包含多个LEFT JOIN的其他列

Question

我有 5 个表，我想一起 LEFT JOIN。表格是:访问者、要约、契约(Contract) 1、契约(Contract) 2 和契约(Contract) 3。

查询:

SELECT 
        count(DISTINCT visitors.ID) as visitors, 
        sum(
        CASE
        WHEN offers.ACTIVE = 1 THEN 1
        ELSE 0
        END) as offers, 
        count(contracts1.ID) as contracts1, sum(contracts1.PRICE) as sum_contracts1, 
        count(contracts2.ID) contracts2, 
        sum(
        CASE
        WHEN contracts2.PAYMENT = 'YEARLY' THEN contracts2.PRICE
        WHEN contracts2.PAYMENT = 'TWICE' THEN contracts2.PRICE*2
        ELSE contracts2.PRICE*4
        END) as sum_contracts2,
        count(contracts3.ID) as contracts3, sum(contracts3.PRICE) as sum_contracts3
        FROM visitors 
        LEFT JOIN offersON offers.VISITOR_ID = visitors.ID AND (offers.IP > 100 OR offers.IP < 0)
        LEFT JOIN contracts1 ON 
        (offers.ID = contracts1.ID_OFFER)
        LEFT JOIN contracts2 ON 
        (offers.ID = contracts2.ID_OFFER)
        LEFT JOIN contracts3 ON 
        (offers.ID = contracts3.ID_OFFER)
        WHERE  visitors.TIME >= '2017-01-01 00:00:00' AND visitors.TIME <= '2017-05-25 23:59:59'

这里的问题是，contracts1、contracts2 和 contracts3 没有共同的列以便连接在一起。因此，不是 contracts1 的 20 行，contracts2 的 30 行和 contracts3 的 50 行，我得到了所有这些的所有组合。因为它们是根据访问者和优惠表加入的。查询末尾的简单 GROUP BY 通常可以解决问题，但是如果我在 END 中对其中一个表(或所有表)使用 GROUP BY，它将创建 MULTIPLE ROWS 而不是我想要的 1。而且它还会删除我按 ID 计算访问者并按 ID 提供的部分的所有其他结果......我可以在 SELECT 的 count() 部分上使用 DISTINCT 但不能在 sum() 上使用 DISTINCT 因为契约(Contract)的价格可能相同，即使 ID 不同(例如，您知道 2 block 巧克力是 2 行，ID 不同但价格相同，每 block 10 美元)。

所以我的问题是:

有什么方法可以仅对具有 DISTINCT ID 的 contracts1、contracts2 和 contracts3 的 PRICES 求和，同时避免添加重复项？不创建 VIEW 是否可行？

我还在 LEFT JOIN 内部尝试了 GROUP BY，但是当我将所有 3 个契约(Contract)表一起 LEFT JOINED 时再次尝试，即使我在结束重复之前对它们进行了分组。

预期结果示例:

在我上面提到的那个时间范围内，我预计:80 名参观者有 35 份报​​价和 5 份总金额为 1000 欧元的契约(Contract) 1、12 份总金额为 686 欧元的契约(Contract) 2 和 3 份总金额为 12 欧元的契约(Contract) 3。它是一行，有 8 列数据。

我得到的不是预期的结果:80 个访客，35 个报价，180 个契约(Contract) 1(总和也不好)，180 个契约(Contract) 2(总和也不好)，180 个契约(Contract) 3(总和也不好)。

Answer 1

使用 CTE ( Supported by MariaDB 10.2.1 ) 我会写这样的东西:

WITH v AS (
    SELECT ID as VISITOR_ID
    FROM visitors 
    WHERE visitors.TIME >= '2017-01-01 00:00:00'
      AND visitors.TIME <= '2017-05-25 23:59:59'
), o AS (
    SELECT offers.ID as ID_OFFER
    FROM v
    JOIN offers USING(VISITOR_ID)
    WHERE offers.ACTIVE = 1
      AND (offers.IP > 100 OR offers.IP < 0)
), c1 AS (
    SELECT count(*) as contracts1, sum(contracts1.PRICE) as sum_contracts1
    FROM o JOIN contracts1 USING(ID_OFFER)
), c2 AS (
    SELECT
        count(*) contracts2, 
        sum(CASE contracts2.PAYMENT
            WHEN 'YEARLY' THEN contracts2.PRICE
            WHEN 'TWICE'  THEN contracts2.PRICE*2
            ELSE contracts2.PRICE*4
        END) as sum_contracts2
    FROM o JOIN contracts2 USING(ID_OFFER)
), c3 AS (
    SELECT count(*) as contracts3, sum(contracts3.PRICE) as sum_contracts3
    FROM o JOIN contracts3 USING(ID_OFFER)
)
    SELECT c1.*, c2.*, c3.*,
        (SELECT count(*) FROM v) as visitors,
        (SELECT count(*) FROM o) as offers,
    FROM c1, c2, c3;

如果没有 CTE，您可以重写它以使用临时表:

CREATE TEMPORARY TABLE v AS
    SELECT ID as VISITOR_ID
    FROM visitors 
    WHERE visitors.TIME >= '2017-01-01 00:00:00'
      AND visitors.TIME <= '2017-05-25 23:59:59';

CREATE TEMPORARY TABLE o AS
    SELECT offers.ID as ID_OFFER
    FROM v
    JOIN offers USING(VISITOR_ID)
    WHERE offers.ACTIVE = 1
      AND (offers.IP > 100 OR offers.IP < 0);

CREATE TEMPORARY TABLE c1 AS
    SELECT count(*) as contracts1, sum(contracts1.PRICE) as sum_contracts1
    FROM o JOIN contracts1 USING(ID_OFFER);

CREATE TEMPORARY TABLE c2 AS
    SELECT
        count(*) contracts2, 
        sum(CASE contracts2.PAYMENT
            WHEN 'YEARLY' THEN contracts2.PRICE
            WHEN 'TWICE'  THEN contracts2.PRICE*2
            ELSE contracts2.PRICE*4
        END) as sum_contracts2
    FROM o JOIN contracts2 USING(ID_OFFER);

CREATE TEMPORARY TABLE c3 AS
    SELECT count(*) as contracts3, sum(contracts3.PRICE) as sum_contracts3
    FROM o JOIN contracts3 USING(ID_OFFER);

SELECT c1.*, c2.*, c3.*,
    (SELECT count(*) FROM v) as visitors,
    (SELECT count(*) FROM o) as offers,
FROM c1, c2, c3;