代码之家 › 专栏 › 技术社区 › user7424312

从两个不同的日期获取数据并进行比较

sql mysql php

user7424312 · 技术社区 · 7 年前

id      idm         amount           date
1        5          10              2017-08-23 12:12:12
2        5          20              2017-08-23 12:14:16
3        6          13              2017-08-23 18:00:00
4        5          25              2017-08-24 19:00:00
5        5          160             2017-08-24 19:30:00

因此,我们的想法是从日期获得金额的总和 2017-08-23 2017-08-24 . 如果一个用户的这两个值之间的差值大于20,例如,在本例中,我找到了一个用户。

我的建议是制作两个sql:

select sum(amount) as previous_amount, idm 
FROM table
WHERE date >= '2017-08-23 00:00:00' AND date <= '2017-08-23 23:59:59' 
GROUP By idm

select sum(amount) as actual_amount, idm 
FROM table
WHERE date >= '2017-08-24 00:00:00' AND date <= '2017-08-24 23:59:59' 
GROUP By idm

并在php中进行处理,但可能存在一种在sql中进行处理的方法。你能帮我吗?提前谢谢,我的英语很抱歉。

4 回复 | 直到 7 年前

Oto Shavadze 7 年前

如果我理解正确,这就是你想要的:

select 
idm,
sum(case when date >= '2017-08-23 00:00:00' AND date <= '2017-08-23 23:59:59' then amount end)  -
sum(case when date >= '2017-08-24 00:00:00' AND date <= '2017-08-24 23:59:59' then amount end)  as diff
from your_table
group by idm
having diff not between -20 and 20

Shuddh 7 年前

select (sum(b.amount) - sum(a.amount)) as result, idm
from table a join table b on a.idm = b.idm
where a.date = date('2017-08-23') and b.date = date('2017-08-24')
group by idm

如果你能提供一把小提琴,可能会帮助我们帮助你。

urban 7 年前

idm 和 day datetime 操作函数从日期时间值中删除时间元素,该值将日期仅保留为字符串。这是我们现在可以分组讨论的内容:

select sum(amount) as previous_amount, idm, DATE_FORMAT(`date`, '%Y-%m-%d') as day 
FROM test
GROUP By idm, day
order by idm, day

这将为您提供以下信息:

previous_amount idm day
30              5   2017-08-23
185             5   2017-08-24
13              6   2017-08-23

select 
    diff1.idm, 
    previous_amount, 
    actual_amount, 
    diff2.actual_amount-diff1.previous_amount as difference, 
    diff1.day as from_day, 
    diff2.day as to_day
from (
  select sum(amount) as previous_amount, idm, DATE_FORMAT(`date`, '%Y-%m-%d') as day 
  FROM test
  GROUP By idm, day
  order by idm, day
) as diff1
left join (
  select sum(amount) as actual_amount, idm, DATE_FORMAT(`date`, '%Y-%m-%d') as day 
  FROM test
  GROUP By idm, day
  order by idm, day
) as diff2 on diff1.idm=diff2.idm
where DATE(diff2.day) > DATE(diff1.day) 
  and diff2.actual_amount-diff1.previous_amount > 20;

idm previous_amount actual_amount   difference  from_day    to_day
5   30              185             155         2017-08-23  2017-08-24

差别然而,我认为它可以很容易地扩展到这样做。

sqlfiddle 在这里

如果您想保证单日差异,请更换 where 具有的条件 TIMESTAMPDIFF(DAY, diff1.day, diff2.day) = 1 fiddle

Benvorth 7 年前

SELECT 
  idm,
  sum(IF(date(`date`) = '2017-08-23', amount, 0)) as amountDay1,
  sum(IF(date(`date`) = '2017-08-24', amount, 0)) as amountDay1,

  sum(IF(date(`date`) = '2017-08-23', amount, 0)) 
  - sum(IF(date(`date`) = '2017-08-24', amount, 0)) as diffDay1Day2
FROM 
  table
GROUP BY
  idm