Mysql为每次执行选择切换行 [英] Mysql select switching rows for every execution

问题描述

我正在开发自动出价系统，有一个棘手的场景.也就是说，我希望在每次执行代码时从table_machine"中选择下一台机器进行投标.这意味着，我正在从活动(状态 = 1)机器中挑选出价进行出价.

I am developing auto bidding system,There is a tricky scenario. That is, I want select next machine to bid from "table_machine" on each execution of code. Which means, I am picking bids from active (status=1) machine to bid.

execution 1 bidz from mid 1
execution 2 bidz from mid 2
.......
execution 4 bidz from mid 4
execution 5 bidz from mid 1

执行后 1 如果用户 2 在直播中出价，则机器出价应忽略中间 2，其应出价中间 3

after execution 1 if user 2 bidz on live then machine bid should negleate mid 2 and its should bid mid3

我有两张桌子

table_bid_history

table_bid_history

history_id    user_id      product_id         price        type 
1               1               1              10          live         
2               2               1              10.5        live 
3               1               1              11         machine     
4               2               1              11.5       machine     
5               3               1              12         machine              
6               4               1             12.5       machine       
7               1               1              13         machine 

table_machine

table_machine

mid      user_id          product_id    start       end     num_bidz  status               
1           1                1             8          12        5        1
2           2                1            10          15        15       1
3           3                1            11          16        10       1
4           4                1            11          16        10       1 

我该怎么做，我觉得这很棘手但必须有办法做到这一点.请给我主意.请给我提意见 ！谢谢

How can i do this , I am feeling this is tricky But there must be way to do this. give me idea please . Please advise me ! Thanks

推荐答案

如果我理解正确，您可以这样做:在您的 machine_table 上创建一个新的假字段并将其命名为 lastSelect 并在您的 Where 子句中使用以下内容:

If i understand correctly you could do this : create a new fake field on your machine_table and named it lastSelect and in your Where Clause Use something like this :

SELECT FROM ............. 
    LEFT JOIN table_machine tm ON tm.lastSelect=1
WHERE id = IF(tm.id=MAX(tm.id),1,tm.id)

然后将 table_machine 上的当前 lastSelect 更新为 1

Then update current lastSelect on table_machine to 1

这篇关于Mysql为每次执行选择切换行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！