INSERT 有条件 [英] INSERT with a condition

问题描述

我有这个表 pageview

 id    |   visitor   |   id_realestate  |   time
 -----------------------------------------------

如果只有最后一个 time 至少对于该特定 id_realestate 已被该特定 visitor 检查过，我希望插入记录

I want the record to be inserted if only the last time is 30 mins old at least for that particular id_realestate which has been checked by that particular visitor

为了更清楚.如果我准备好将这些数据插入表中

to make it clearer. If I have this data ready to be inserted in the table

visitor:"axax"
id_realestate:120
time:NOW()

如果只有具有这些值的最后一条记录，则应插入这些值 visitor:"axax" &id_realestate:120 超过 30 分钟(通过检查字段 time 以获取我提到的两个值的最后一条记录，并将其与 NOW() 进行比较代码>如果相差大于30分钟.如果是，则应插入数据)

these value should be inserted if only the last record with these values visitor:"axax" & id_realestate:120 is older than 30 minutes (by checking the field time for the last record of the two values I mentioned and compare it with NOW() if the difference is greater than 30 minutes. if so then the data should be inserted)

我现在有这些数据.如您所见，时间字段包含昨天的值，因此应插入任何新数据:

I have this these data now. as you can see the time field contains a value from yesterday so any new data should be inserted:

我按照 anubhava 的建议使用了这个查询，但什么也没发生(没有添加新记录/没有错误)

I used this query as suggested by anubhava but nothing happened ( no new record added/ no errors)

$query='insert into pageview
select "q17872745t", 150, now() 
from pageview a
where id_realestate=150 and DATE_ADD(now(),INTERVAL -30 MINUTE) > (
  select max(time) from pageview b where a.id_realestate=b.id_realestate AND   a.visitor=b.visitor
)';

更新

我将查询修改为以下内容并且它正在工作但是 它与每个时间进行比较如果最新时间是30分钟> 并相应地插入一条记录(这意味着它将记录加倍).因此，如果我有两个具有相同 visitor 和 id_realestate 的记录，如果两者中的 time 都是 30 分钟，那么它将与这两个记录进行比较插入 2 条相同的记录，而应该插入一条记录.

update

I modified the query to the following and it's working BUT It compares with every time if the latest time is 30 mint old and accordingly it inserts a record( meaning it doubles the records). so if I have two records with same visitor and id_realestate it will compare with these two records if the time in both is 30 minutes old then it inserts 2 identical records whereas it should insert one record.

      $query='insert into pageview (visitor,id_realestate,time)
      select "q17872745t", 150, now() 
         from pageview a
         where id_realestate=150 and DATE_ADD(now(),INTERVAL -30 MINUTE) > (
            select max(time) from pageview b where a.id_realestate=b.id_realestate AND a.visitor=b.visitor
      )';

更新:[已解决]

我在上述查询的末尾添加了 LIMIT 1，现在它可以正常工作了.这是最后的查询:

update: [solved]

I added LIMIT 1 at the end of the above query, now it's working as it should. here is the final query:

      $query='insert into pageview (visitor,id_realestate,time)
      select "q17872745t", 150, now() 
         from pageview a
         where id_realestate=150 and DATE_ADD(now(),INTERVAL -30 MINUTE) > (
            select max(time) from pageview b where a.id_realestate=b.id_realestate AND a.visitor=b.visitor
      ) LIMIT 1';

推荐答案

不是一个很明确的问题，但您可以使用这样的查询:(untested)

Not a very clear question but you can use a query like this: (untested)

insert into pageview
  select 15, 'A VISITOR', 10, now() 
  from pageview a
  where id_realestate=10 and DATE_ADD(now(),INTERVAL -30 MINUTE) > (
      select max(news_release) from pageview b where a.id_realestate=b.id_realestate
  );

这篇关于INSERT 有条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！