MYSQL:如何使用内部连接连接两个表，然后为以下示例计算第二个表的总数

Question

我遇到了以下要求，我发现很难破解它的查询。

考虑一个包含以下字段的customer表

id   signup_date  first_payment_date 
10    2015-03-20     null
11    2015-03-20     null
12    2015-03-20     null
13    2015-03-20     null
14    2015-05-23     null
15    2015-05-23     null

考虑另一个表transaction_history

id   product_name
10    vod trial
10    vod trial 
11    vod trial
12    vod trial
12    vod
13    vod trial
14    vod trial
15    vod trial
15    vod trial

我需要从 customer 表中选择 id 并根据 signup_date 在 transaction_history 表中查找first_payment_date 为 null。

现在我需要检查此 id 是否出现在 transaction_history 中，并检查他是否至少有 1 个条目为 product_name = "vod trial"。如果他有那么他就是我想要的结果中的一行。

最后，我需要计算 transaction_history 中至少有一行 product_name="vod_trial" 的 ID 总数，这应该是在某个日期customer 表中 signup_date 中提到的基础。

我按以下方式写了一个查询:

SELECT 
    ts.guid,
    cs.signup_date,
    (SELECT 
        COUNT(ts2.guid)
     FROM
        transaction_history ts2
     WHERE
        cs.guid = ts2.guid
        AND ts2.product_name = "vod trial"
    HAVING COUNT(ts2.guid) = 1) AS count_ts_guid
FROM
    customer AS cs,
    transaction_history AS ts
WHERE
    cs.guid = ts.guid
    AND cs.first_payment_date IS NULL;

但是在上面的查询中我无法计算总计数 signup_datewise。

如果有人能帮助我，那就太好了。

示例结果:

date         new trials
2015-03-20      2
2015-05-23      1

Answer 1

我不确定我是否完全理解。您想让没有 first_payment_date 的客户在交易表中有试用条目吗？

select *
from customer
where first_payment_date is null
and id in (select id from transaction_history where product_name = 'vod trial');

好的，从您上一条评论看来，您也希望在交易表中没有试用条目的客户。你想用他们的试用交易计数来显示它们。所以:

select signup_date, 
  (
    select count(*) 
    from transaction_history th
    where th.product_name = 'vod trial'
    and th.id = c.id
  )
from customer c
where first_payment_date is null;

如果你甚至想按日期分组，然后聚合:

select signup_date, 
  sum((
    select count(*) 
    from transaction_history th
    where th.product_name = 'vod trial'
    and th.id = c.id
  ))
from customer c
where first_payment_date is null
group by signup_date;

下一步尝试:加入所有客户和交易，例如仅让客户出现在交易表中。然后聚合。

select c.signup_date, count(*) 
from customer c
join transaction_history th on th.id = c.id and th.product_name = 'vod trial'
where c.first_payment_date is null
group by c.signup_date;

或者你想要这个:

select c.signup_date, count(case when th.product_name = 'vod trial' then 1 end) 
from customer c
join transaction_history th on th.id = c.id
where c.first_payment_date is null
group by c.signup_date;