php - 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将表连接在一起，您可以为每个用户创建一个(可能)巨大的笛卡尔积。然后您将对此进行筛选以计算不同的 id。直接计数效率更高。

每个用户应该有一行。

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