mysql - 如何创建超过当日交易次数和交易限额的客户姓名列表

Question

表1

'name'              'amount'           'day'
---------------------------------------------
hemanth        10000      2019-06-21
hemanth        1000       2019-06-21
hemanth        5000       2019-06-21
hemanth        10000      2019-07-21    
kumar          100        2019-06-21
kumar          5000       2019-06-21
kumar          1000      2019-07-21
kiranmai       10000     2019-06-21
kiranmai       500      2019-07-21
kiranmai       10000     2019-06-21

表2包含每日交易限额和交易金额限制

tranlimperday                transamontlim
--------------------------------------------
3                      10000

我已经将日期列转换为日和月，但之后我们需要找到当天的交易计数和金额总和，我们需要用月份(日期)来限制月份列，但我无法找到查询

期望输出

Name
---------
Hemanth
kiranmai

Answer 1

我建议两个单独的查询和union:

select name
from (select name, day,
             sum(amount) as total_amount, count(*) as cnt
      from table1
      group by name, day
     ) nd join
     table2 t2
     on t1.cnt >= t2.tranlimperday
union  -- on purpose to remove duplicates
select name
from (select name, year(day) as yyyy, month(day) as mm,
             sum(amount) as total_amount, count(*) as cnt
      from table1
      group by name, year(day), month(day)
     ) nym join
     table2 t2
     on t1.total_amount >= t2.transamontlim;

在 MySQL 8+ 中，您还可以使用窗口函数:

select distinct name
from (select name, day,
             sum(amount) as daily_total_amount, count(*) as daily_cnt,
             sum(sum(amount)) over (partition by name, year(day), month(day)) as monthly_total_amount,                  
             sum(count(*)) over (partition by name, year(day), month(day)) as monthly_cnt 
      from table1
      group by name, day
     ) nd join
     table2 t2
     on t1.daily_cnt >= t2.tranlimperday or
        t1.monthly_total_amount >= t2.transamontlim;