php - 这个查询也给了我空白结果! Mysqli 连接表

Question

该图像显示有些行具有 NULL 值，但我不需要它们。

而且这个查询需要很长时间才能加载..

基本上我想要的是

从数据库的两个表中获取值时遇到一些问题。我的数据库中有两个表，1是mem，2是付款mem存储用户的姓名和drawid付款表存储用户的提款和分期付款

用户每月向我们付款。因此，如果抽奖 ID 为 1 的用户在 2 月份向我们付款，则两个表中的值分别是 mem drawid=1 和 name = some payment Draw = 1 和 instalment=2

内存中的drawid与付款中的drawid相同

所以这些表具有多对多的关系。现在我需要找到所有在第 4 个月之前还没有支付过 1 期分期付款的成员(member)的列表。

 SELECT drawid,contact,dnd,mem.name,link,address, count(*) as numPayments,NULL numPaidPayments ,NULL PAID_CONTACT,NULL NAME_PAID FROM mem 
LEFT JOIN payment ON (mem.drawid = payment.draw) GROUP BY 
drawid HAVING numPayments < '4'
UNION
SELECT NULL drawid,NULL contact, NULL dnd, NULL name,NULL link, NULL address,NULL numPayments,COUNT(*) as numPaidPayments ,contact PAID_CONTACT,mem.name NAME_PAID  FROM mem 
INNER JOIN payment ON (mem.drawid = payment.draw) GROUP BY 
drawid HAVING numPaidPayments < '4' order by link desc 

我厌倦了解决这个问题，请给我一些建议，我一周以来一直在尝试解决这个问题..

我正在为两个表结构添加图像。

在此输入图像描述

Answer 1

看来您只想计算每个 mem 的付款:

select 
  m.*,
  (select count(*) from payment p where p.draw = m.drawid) as num_paid_payments
from mem m;

仅限付款次数少于四次的人:

select *
from
(
  select 
    m.*,
    (select count(*) from payment p where p.draw = m.drawid) as num_paid_payments
  from mem m
) paid
where num_paid_payments < 4;

或者不同的写法:

select 
  m.*,
  coalesce(cnt, 0) as num_paid_payments
from mem m
left join
(
  select draw, count(*) as cnt
  from payment
  group by draw
) p on p.draw = m.drawid
where coalesce(cnt, 0) < 4;

或者不同的写法:

select 
  m.*, 
  count(p.draw) as num_paid_payments
from mem m
left join payment p on p.draw = m.drawid
group by m.drawid
having count(p.draw) < 4;