SQL:从复杂选择中进行更新 [英] SQL: UPDATE from a complex select

问题描述

在电话系统中，我有2张桌子.

In a phone system scenario i have 2 tables.

• 表1由customer_id，call_duration，calldate，skip_billing组成.
• table2由以下组成:customer_id，bonus_seconds.

• table1 is composed by: customer_id, call_duration, calldate, skip_billing .
• table2 is composed by: customer_id, bonus_seconds.

表1存储了所有客户的所有呼叫，表2存储了bonus_seconds，代表代表已定义客户的免费通话时间(即:对于客户1，头40秒钟是免费的).

table1 stores all the calls for all customers and table2 stores the bonus_seconds which represents free conversation time allowed for a defined customer(ie: for customer 1 the FIRST 40 cumulative seconds are free).

我必须根据以下条件编写查询以更新table1: 在table2中定义为免费的调用中设置skip_billing.

I have to write a query to update table1 according to the condition explained below: set skip_billing within calls which are defined free in table2.

因此，我首先需要按customer_id分组，然后遍历调用，在call_duration上增加一个累积变量(cumsec)，并相应地设置skip_billing.

So I first need to group by customer_id and then iterate over the calls, incrementing a cumulative variable(cumsec) over call_duration and set skip_billing accordingly.

table1的示例是:

table1 example is:

|sqlid |customer_id |billsec | skipbill|
|0     |1           |12      | 1       |<--need to set 1 due to cume=12 for customer_id=1
|1     |1           |10      | 1       |<--need to set 1 due to cume=22 for customer_id=1
|2     |1           |15      | 1       |<--need to set 1 due to cume=37 for customer_id=1
|3     |1           |8       | 0       |<--nop(no operation) due to cume=45
|4     |2           |12      | 1       |<--need to set 1 due to cume=12 for customer_id=2
|5     |3           |12      | 1       |<--need to set 1 due to cume=12 for customer_id=3
|6     |2           |12      | 0       |<--nop due to cume=24 for customer_id=2
|7     |1           |12      | 0       |<--nop due to cume=49 for customer_id=1
|8     |3           |15      | 0       |<--nop due to cumsec=27 for customer_id=3

|customer_id |bonus_seconds|
|1           |40           |
|2           |20           |
|3           |15           |

我尝试使用这样的查询(感谢Gordon Linoff)，该查询返回了正确的行集:

I tried with query like this (thanks to Gordon Linoff) which returns the right set of row:

    SELECT t.cume, t.calldate, t.customer_id FROM (SELECT t.*, (@cume := @cume + billsec) AS cume FROM table1 t CROSS JOIN (SELECT @cume := 0) vars ORDER BY calldate) t, table2 sct WHERE t.cume <= sct.bonus_seconds AND t.customer_id=sct.customer_id ;

但是当我尝试将UPDATE与下面的命令一起使用时，由于没有任何匹配，它不起作用.

But when i try to use withing the UPDATE like below command it does not work because does not match anything.

    UPDATE table1 SET skipbill=1 WHERE sqlid=(SELECT t.sqlid FROM (SELECT t.*, (@cume := @cume + billsec) AS cume FROM table1 t CROSS JOIN (SELECT @cume := 0) vars ORDER BY calldate) t, table2 sct WHERE t.cume <= sct.bonus_seconds AND t.customer_id=sct.customer_id ) ;

如何使用该查询或更好的查询来编写更新任务?

How I can write the update task using that query or something better ?

提前谢谢

推荐答案

UPDATE table1 
SET skipbill = 1 
WHERE sqlid IN (
    SELECT DISTINCT t.sqlid 
    FROM (
        SELECT t.*, (@cume := @cume + billsec) AS cume 
        FROM table1 t 
        CROSS JOIN (SELECT @cume := 0) vars 
        ORDER BY calldate
    ) t, table2 sct 
    WHERE t.cume <= sct.bonus_seconds 
          AND t.customer_id = sct.customer_id
);

这篇关于SQL:从复杂选择中进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！