mysql - 将参数插入Where子句

Question

我有一个查询，我想从数据库中的数据中获取排名

set @urut:=0;
set @rankhrg:=0;
select @urut:=@urut+1 as urut, a.id_tender, b.nama_tender, b.nomor_tender, b.tgl_close1 as tgl_close,
(SELECT rankhrg 
from (select sum(tot_harga) as hrg_twr, id_rekanan, id_tender, @rankhrg:=@rankhrg+1 as rankhrg from tb_real_barang where id_tender = s.id_tender  group by id_rekanan) as rank_harga
left join tb_master_tender s on s.id_tender = b.id_tender
where rank_harga.id_rekanan = a.id_rekanan
order by rank_harga.hrg_twr asc) as ranking
                                        from tb_real_tender a
                                        left join tb_master_tender b on a.id_tender = b.id_tender
                                        where a.id_rekanan = 1
                                        order by convert(a.id_tender,unsigned) desc

当我想获取rankhrg时，我想将id_tender传递到select内的select中:

select sum(tot_harga) as hrg_twr, id_rekanan, id_tender, 
       @rankhrg:=@rankhrg+1 as rankhrg 
from tb_real_barang 
where id_tender = s.id_tender  
group by id_rekanan

但我总是收到错误，指出 s.id_tender 在 where 子句中未知。

有人可以指导我如何将参数传递到该插入中吗？谢谢你:)

Answer 1

您没有加入该表tb_master_tender，它也不存在于外部查询FROM子句中。因此，您需要为该内部查询单独执行JOIN，如下所示

select sum(trb.tot_harga) as hrg_twr, 
trb.id_rekanan, 
trb.id_tender, 
@rankhrg:=@rankhrg+1 as rankhrg 
from tb_real_barang trb
left join tb_master_tender s on trb.id_tender = s.id_tender  
group by trb.id_rekanan