Sql联合和分组依据

Question

我有一个问题：

SELECT users.name, 
COUNT(DISTINCT leads.id) as leads,
COUNT(DISTINCT properties.id) as properties,
COUNT(DISTINCT buyers.id) as buyers,
null
FROM users
LEFT JOIN (SELECT * from leads WHERE date(created_at) BETWEEN '2019-04-13' AND'2019-04-13') leads ON users.id = leads.user_id
LEFT JOIN (SELECT * from properties WHERE date(contract_start_date) BETWEEN '2019-04-13' AND'2019-04-13') as properties ON users.id = properties.user_id
LEFT JOIN (SELECT * from buyers WHERE date(created_at) BETWEEN '2019-04-13' AND'2019-04-13') as buyers ON users.id = buyers.user_id
LEFT JOIN buyer_searches ON buyers.id = buyer_searches.buyer_id
GROUP BY users.id
UNION
SELECT users.name,
null, null, null,
COUNT(DISTINCT buyer_searches.id) as buyer_searches
FROM users
LEFT JOIN buyers ON users.id = buyers.user_id
LEFT JOIN (SELECT * from buyer_searches WHERE date(created_at) BETWEEN '2019-04-08' AND'2019-04-13') as buyer_searches ON buyers.id = buyer_searches.buyer_id
GROUP BY users.id

当然，我得到了一个包含重复用户的表，但参数正确：

users field1 field2 field3 etc....
user1 data . null . null etc...
user2 data . null . null etc...
user1 null . data . data etc...
user2 null . data .data etc...
etc...

我想按用户连接这些行。

Answer 1

在SELECT中使用子查询执行以下操作：

SELECT u.name, 
       (SELECT COUNT(*)
        FROM leads l
        WHERE l.user_id = u.id AND
              l.created_at >= '2019-04-13' AND
              l.created_at < '2019-04-14'
       ) as num_leads,
       (SELECT COUNT(*)
        FROM properties p
        WHERE p.user_id = u.id AND
              p.created_at >= '2019-04-13' AND
              p.created_at < '2019-04-14'
       ) as num_buyers,
       (SELECT COUNT(*)
        FROM buyers b
        WHERE b.user_id = u.id AND
              b.created_at >= '2019-04-13' AND
              b.created_at < '2019-04-14'
       ) as num_buyers,
       (SELECT COUNT(*)
        FROM buyers b JOIN
             buyer_searches bs
             ON b2.buyer_id = b.id AND
        WHERE b.user_id = u.id AND
              bs.created_at >= '2019-04-13' AND
              bs.created_at < '2019-04-14'
       ) as num_buyer_searches
FROM users u;

通过将这些表JOIN在一起，您正在为每个用户创建一个(潜在的)巨大的笛卡尔产品。然后对此进行筛选，以计算不同的ids。直接对它们进行计数效率更高。

每个用户都应该有一行。

请注意，我已经更改了日期比较。列上的函数几乎总是排除了索引的使用。这个版本的查询可以利用表上的索引--比如leads(user_id, created_at) --如果它们可用的话。