mysql - 如何创建变量并将其传递给另一个查询？

Question

UPDATE uye SET bakiye=bakiye+100 WHERE ID IN 
(SELECT uye FROM kupon WHERE ID IN 
( select c.kupon from kuponbahis c 
join bahis b on b.sonuc = c.secim and b.ID = c.bahis 
group by c.kupon 

having sum(c.bahis = 24) > 0 and count(*) = (SELECT COUNT(*) FROM kuponbahis WHERE kupon = c.kupon)))

这个查询有效，但我想用内部变量来更改它，而不是“100”；

UPDATE uye SET bakiye=bakiye+VARIABLE WHERE ID IN 
(SELECT uye, VARIABLE FROM kupon WHERE ID IN 
( select c.kupon from kuponbahis c 
join bahis b on b.sonuc = c.secim and b.ID = c.bahis 
group by c.kupon 

我可以用 PHP 代码来完成，但我想将所有过程直接放到 SQL 中。

(注意:是的，我想要的变量位于 kupon 表内部)

Answer 1

我怀疑您不想要变量。您需要子查询中的一列。在这种情况下，请使用 JOIN:

UPDATE uye JOIN
       (SELECT k.*
        FROM kupon k
        WHERE k.ID IN (select c.kupon
                       from kuponbahis c join
                            bahis b
                            on b.sonuc = c.secim and b.ID = c.bahis 
                       group by c.kupon 
                       having sum(c.bahis = 24) > 0 and
                              count(*) = (SELECT COUNT(*) FROM kuponbahis WHERE kupon = c.kupon)
                   )
     ) k
     ON k.eye = uye.id
    SET bakiye=bakiye + k.VARIABLE;