Mysql sum distinct基于包含多个左联接的其他列

Question

我有5张桌子要放在一起。表格包括：访客、报价、合同1、合同2和合同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并没有公共列以便连接在一起。因此，我得到的不是合同1的20行，合同2的30行，合同3的50行，而是所有这些的组合。因为它们是根据访问者和提供的表加入的。查询结束时使用简单的GROUP BY通常可以解决问题，但如果我在最后对其中一个表（或所有表）使用GROUP BY，它将创建多行，而不是我想要的1行。而且它也会删除我按ID统计访客和按ID提供服务的部分的所有其他结果。。。我可以使用SELECT的DISTINCT on count（）部分，但不能使用sum（）部分，因为即使ID不一样，合同的价格也可能是相同的（例如，您知道，两块巧克力有两行不同的ID，但每个10美元的价格相同）。

所以我的问题是：

有没有办法只对合同1、合同2和合同3的价格求和，这些价格具有不同的ID，但不必累加重复项？不创建视图也可以吗？

我还尝试在左联接内部按分组，但当我离开时，再次将所有3个合同表合并在一起，尽管我在得到重复项之前对它们进行了分组。

预期结果示例：

在我上面提到的时间范围内，我预计： 80名游客有35个报价和5个合同1，总价为1000欧元，12个合同2，总价为686欧元，3个合同3，总价为12欧元。它是一个有8列数据的行。

我得到的不是预期的结果：80名访客，35份报价，180份合同1（金额也不好），180份合同2（金额也不好），180份合同3（金额也不好）。

Answer 1

只是一个概念证明，我没有考虑时间和活动限制以及支付类型，但它不能是这样的吗？

SELECT
   VISITOR_ID,
   SUM(CASE WHEN TYPE="contract1" THEN 1 else 0 END) as c1_count,
   SUM(CASE WHEN TYPE="contract1" THEN PRICE else 0 END) as c1_total_price,
   SUM(CASE WHEN TYPE="contract2" THEN 1 else 0 END) as c2_count,
   SUM(CASE WHEN TYPE="contract2" THEN PRICE else 0 END) as c2_total_price,
   SUM(CASE WHEN TYPE="contract3" THEN 1 else 0 END) as c3_count,
   SUM(CASE WHEN TYPE="contract3" THEN PRICE else 0 END) as c3_total_price 
FROM (
    (SELECT "contract1" as TYPE, ID, PRICE, ID_OFFER, PAYMENT FROM contracts1) 
    UNION
    (SELECT "contract2" as TYPE, ID, PRICE, ID_OFFER, PAYMENT FROM contracts2)
    UNION
    (SELECT "contract3" as TYPE, ID, PRICE, ID_OFFER, PAYMENT FROM contracts3)
 ) as all_contracts 
 JOIN offers on offers.id = all_contracts.ID_OFFER
 JOIN visitors on visitors.ID = offers.VISITOR_ID
 GROUP BY visitors.ID

其思想是，首先将不同的契约合并到一个结果中，将它们的类型存储在一个名为“type”的列中（这是UNION查询的目的），一旦有了这样一个很好的表，其中每个契约只存在一次，您就可以非常直接地获得所需的结果。我刚才概述了如何获得每种合同的金额和数量。当然，最终的查询会有点复杂，但核心思想应该是一样的。

但是，尽管你声明不想使用（临时）视图，我还是鼓励你尝试一下-我有一种感觉，将那些“所有合同”与报价和访客一起放入临时视图将提高性能，如果这是你关心的问题，而不会使查询太难看，主要是在您希望只查看一个访问者的统计数据或进一步过滤它们（按时间、活动等）的情况下，因为不必要的行不会具体化。但这只是一个印象，因为我还没有在更大的数据集上尝试过这个查询——你可以使用它。

Answer 2

有了CTE（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;

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

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;