根据其他列包含多个LEFT JOIN Mysql总结不同

Question

我有5个表，我想LEFT JOIN在一起。表格为： 访问者，优惠，合同1，合同2和合同3。

QUERY：这里

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没有共同的列，以便被接合在一起。所以不是20行的合约1，30的是合同2和50的合约3，我得到了所有这些组合。因为他们根据访问者加入并提供表格。简单的GROUP BY在查询结束时通常会解决问题，但是如果我在END中为其中一个表（或全部）使用GROUP BY，它将创建多行而不是1。而且它也会清除我通过ID计数访问者的部分​​的所有其他结果以及ID提供的部分...我可以在SELECT的count（）部分上使用DISTINCT，但不能使用sum（），因为合同的PRICE可能是相同的，即使身份证不是（你知道，例如2个巧克力是2行不同的ID，但价格相同，每个10美元）。

所以我的问题是：

有什么办法来总结只有contracts1，contracts2和contracts3的那些价格，即有不同的ID，虽然摆脱加起来的复制品？这有可能没有创建VIEW？

我也尝试了GROUP BY里面的LEFT JOIN，但是再次当我左侧连接所有3个合同表时，即使我在他们之前对它们进行了重新分组。预期的结果的

实施例：

在我指出上述我期望的时间范围：具有35个报价和5 contracts1与千欧元总和，12 contracts2与686欧元总和 80访问者3份合同3，总计12欧元。它是8行数据的一行。

取而代之的是我得到的结果： 80个访客，35个offer，180个contract1（总数也不好），180个contract2（总数也不好），180个contract3（总数也不好）。

Answer 1

由于热膨胀系数（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; 

没有热膨胀系数，你可以重写它使用临时表：

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; 

Answer 2

只是一个概念证明，我没有考虑时间和活动约束以及支付类型，但是不可能是这些方面的东西？

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 

的想法是，首先你合并不同的合同到一个结果，你存储的类型在一个名为“类型”列（这是UNION查询的目的），一旦你拥有这样一个漂亮的表，其中每份合约只有一次，您可以非常直接地得到您想要的结果。我刚刚概述了你如何得到每种合同的总和和数量。当然，最终的查询会更复杂一些，但核心思想应该是一样的。

尽管您声明您不想使用（临时）视图，但我会鼓励您尝试一下 - 我有一种感觉，将这些“all_contracts”与优惠和访问者结合进临时视图可以改善性能，如果这是您的担心，但不会使查询太难看，主要是在您希望仅为一位访问者查看统计信息或进一步过滤它们（按时间，活动等）的情况下，因为不必要的行赢得了没有物化。但这只是一个印象，因为我没有在更大的数据集上尝试查询 - 您可以使用它。