使用多个连接时，COUNT返回双精度值

Question

当我运行这个查询时，我整齐地得到了我想要的结果。

SELECT 
referer_trackings.cookie_first_url AS url,
sum(purchases.price) AS sales, 
count(purchases.id) AS volume
FROM purchases
JOIN referer_trackings 
ON referer_trackings.trackable_id = purchases.id
JOIN items
ON purchases.item_id = items.id
WHERE items.seller_id = '39'
AND referer_trackings.trackable_type = 'Purchase'
AND purchases.workflow_state = 'payment_successful'
GROUP BY referer_trackings.cookie_first_url

这将返回：

url             sales          volume
======================================
UrlName1.com    8743           332
UrlName2.com    234            123
UrlName3.com    2352           2345

这是正确的，但我也将登录页面视图存储在另一个单独的表中，因为我真正需要的是：

url             sales          volume       landing_page_views
===============================================================
UrlName1.com    8743           332          34563
UrlName2.com    234            123          52443
UrlName3.com    2352           2345         84563

为了得到这个结果，我运行这个查询：

SELECT 
referer_trackings.cookie_first_url AS url,
sum(purchases.price) AS sales, 
count(purchases.id) AS volume,
COUNT(DISTINCT product_landing_page_views.id) AS ulpvs
FROM purchases
JOIN referer_trackings 
ON referer_trackings.trackable_id = purchases.id
JOIN items
ON purchases.item_id = items.id
JOIN product_landing_page_views
ON product_landing_page_views.item_id = items.id
WHERE items.seller_id = '39'
AND referer_trackings.trackable_type = 'Purchase'
AND purchases.workflow_state = 'payment_successful'
GROUP BY referer_trackings.cookie_first_url

这会导致数据看起来加倍，而且远远超过实际计数。我知道这是因为打给

JOIN product_landing_page_views
ON product_landing_page_views.item_id = items.id

将行添加到结果中，从而使计数相乘。

所以我的问题是:如何确保在连接product_landing_page_views表时，查询仍然产生正确的计数？

Answer 1

试试这个：-

SELECT 
referer_trackings.cookie_first_url AS url,
sum(purchases.price) AS sales, 
count(purchases.id) AS volume,
product_landing_page_views.ulpvs
FROM purchases
JOIN referer_trackings 
ON referer_trackings.trackable_id = purchases.id
JOIN items
ON purchases.item_id = items.id
JOIN (SELECT item_id, count(*) as ulpvs from product_landing_page_views GROUP BY item_id) as product_landing_page_views
ON product_landing_page_views.item_id = items.id
WHERE items.seller_id = '39'
AND referer_trackings.trackable_type = 'Purchase'
AND purchases.workflow_state = 'payment_successful'
GROUP BY referer_trackings.cookie_first_url

(这是假设我正确理解了您的表的结构。如果没有的话，很抱歉)。