如何在每次重复值时计算/邮寄？(How to count / mail every time a duplicate value has been made?)

如何在每次重复值时计算/邮寄？(How to count / mail every time a duplicate value has been made?)

我想让自己每天晚上0:01用cronjob发送一封电子邮件，当一个特定值在过去的24小时内重复填充。

到目前为止我所拥有的是：

$query = "SELECT id, detection.number FROM detection INNER JOIN(SELECT number FROM detection GROUP BY number HAVING COUNT(id) >= 2)temp ON detection.number = temp.number WHERE DATE(date_submit) = CURDATE() - INTERVAL 1 DAY ORDER BY number";

每天我都想要一个在过去24小时内填写的重复列表，所以当一个数字填写为重复并且几天前已经填写时，我希望列表中的那个数字和我想要的重复项仅在过去24小时内填写。

知道怎么创建这个吗？

也许有一个额外的列与重复数量的计数器？

或者我可以只用mysql查询来实现这个吗？

这里有一些样本数据，希望能够理解我想要实现的目标。

表名=检测 number - date_submit 111 ----- 2016-02-29 222 ----- 2016-02-29 333 ----- 2016-02-29 111 ----- 2016-03-01 333 ----- 2016-03-01 111 ----- 2016-04-02 111 ----- 2016-04-02 444 ----- 2016-04-02

当我在2016-04-03 00:01 am运行cronjob时，我想在整个数据库中搜索昨天填写的数字，例如111和444.并且使用这些数字，我想要搜索这些数字的重复并将所有找到的重复项放入电子邮件中，例如 111 ----- 2016-02-29 111 ----- 2016-03-01 111 ----- 2016-04-02 111 ----- 2016-04-02

亲切的问候，

阿里

I'm trying to get myself send an email every night at 0:01 with a cronjob when a certain value has been filled in duplicate the last 24 hour.

What I have so far is this:

$query = "SELECT id, detection.number FROM detection INNER JOIN(SELECT number FROM detection GROUP BY number HAVING COUNT(id) >= 2)temp ON detection.number = temp.number WHERE DATE(date_submit) = CURDATE() - INTERVAL 1 DAY ORDER BY number";

Every day I want a list of duplicates that has been filled in the last 24 hour so when a number is filled in as duplicate and has been filled in a few days ago, I want that number in the list and offcourse I want the duplicates which has been filled only the last 24 hours.

Any idea how to create this?

Maybe with an extra column with a counter of number of duplicates?

Or can I make this one happens with only a mysql query?

Here's some sample data to hopefully understand what I'm trying to achieve.

Table name = detection number - date_submit 111 ----- 2016-02-29 222 ----- 2016-02-29 333 ----- 2016-02-29 111 ----- 2016-03-01 333 ----- 2016-03-01 111 ----- 2016-04-02 111 ----- 2016-04-02 444 ----- 2016-04-02

When I run the cronjob on 2016-04-03 00:01 am I want to search through whole the database for numbers which are filled in yesterday, e.g. 111 and 444. And with these numbers, I want a search for duplications of that numbers and put all of the found duplicates in an e-mail, e.g. 111 ----- 2016-02-29 111 ----- 2016-03-01 111 ----- 2016-04-02 111 ----- 2016-04-02

Kind regards,

Arie

最满意答案

Select * from detection D1 where exists (Select 1 from detection D2 where date_Submit >=now()-interval 1 day and D1.Number = D2.Number) Select * from detection D1 where exists (Select 1 from detection D2 where date_Submit >=now()-interval 1 day and D1.Number = D2.Number)