on用法 - Mysql根據其他包含多個LEFT JOIN的列來求和




sql on用法 (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

這個想法是,首先你將不同的合約合併成一個結果,把它們的類型存儲在一個名為“TYPE”的列中(這就是UNION查詢的目的),一旦你擁有一個好的表,每個合約只有一次,你可以很直接地得到你想要的結果。 我剛才概述了你如何得到每種合同的總和和數量。 當然,最終的查詢會更複雜一些,但核心思想應該是一樣的。

但是,儘管你不想使用(臨時)觀點,但是我鼓勵你嘗試一下 - 我有一種感覺,把這些“all_contracts”與提議和訪問者結合起來臨時看待會提高性能,如果這是你的擔心,而不會使查詢太難看,主要是當你想看到一個訪問者的統計信息或進一步過濾(按時間,活動等),因為不必要的行將不會實現。 但這只是一個印象,因為我還沒有嘗試更大的數據集的查詢 - 你可以玩它。

我有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'

這裡的問題是,合同1,合同2和合同3沒有共同的列以便被連接在一起。 所以,而不是20行的合同1,30合同2和50合同3我得到所有的組合所有這些。 因為他們根據訪問者加入並提供表格。 簡單的GROUP BY在查詢結束通常會解決問題,但如果我在END中使用GROUP BY作為其中一個表(或所有這些表),它將創建MULTIPLE ROWS而不是1我想要的。 而且它也會擦除所有其他的結果,我通過ID計數訪客的部分,也提供了ID ...我可以使用DISTINCT在SELECT()的部分,但沒有一個sum(),因為合同的價格可能是相同的,即使身份證不是(你知道,例如,2個巧克力是2行不同的ID,但價格相同,每個10美元)。

所以我的問題是:

有沒有什麼辦法可以只有合同1,合同2和合同3,有DISTINCT ID的價格,並儘管擺脫加起來重複? 有沒有可能創建VIEW?

我也嘗試了在左邊的JOIN裡面的GROUP BY,但是當我把所有3個合同表連在一起的時候,儘管我在結束之前對它們進行了分組。

預期結果示例:

在我上面提到的那個時間範圍內,我期望:有80​​個遊客有35個報價和5個合同1,總計1000歐元,12個合同2,總計686歐元,3個合同3,總計12歐元。 這是一行8列的數據。

而不是預期的結果,我得到了:80個訪客,35個報價,180個合同1(總數也不好),180個合同2(總數也不好),180個合同3(總數也不好)。


使用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;

沒有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;






distinct