php - 使用带计数的选择子查询进行mysql查询时出错

Question

我正在尝试从另一个表中选择并计算记录，并检查计数是否大于 0

表 1 = 获取记录的表

STRUCTURE:
id | username | sponsorid

表 2 = 我试图获取计数并检查计数是否大于 0 的表

STRUCTURE: 
id | receiver_id | amount

查询:

SELECT t1.id AS lev1, t2.id AS lev2, t3.id AS lev3, t4.id AS lev4, t5.id AS lev5, 
  t6.id AS lev6, t7.id AS lev7, t8.id AS lev8, t9.id AS lev9, t10.id AS lev10 
  (SELECT COUNT(*) FROM tbl_master_fund where receiver_id = 1) as entry_count 
FROM tbl_users AS t1 
  LEFT JOIN tbl_users AS t2 
    ON t2.sponsorid = t1.id 
  LEFT JOIN tbl_users AS t3 
    ON t3.sponsorid = t2.id 
  LEFT JOIN tbl_users AS t4 
    ON t4.sponsorid = t3.id 
  LEFT JOIN tbl_users AS t5 
    ON t5.sponsorid = t4.id 
  LEFT JOIN tbl_users AS t6 
    ON t6.sponsorid = t5.id 
  LEFT JOIN tbl_users AS t7 
    ON t7.sponsorid = t6.id 
  LEFT JOIN tbl_users AS t8 
    ON t8.sponsorid = t7.id 
  LEFT JOIN tbl_users AS t9 
    ON t9.sponsorid = t8.id 
  LEFT JOIN tbl_users AS t10 
    ON t10.sponsorid = t9.id 
  LEFT JOIN tbl_users AS t11 
    ON t11.sponsorid = t10.id 
WHERE t1.sponsorid = 1 
HAVING entry_count > 0 

错误:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT COUNT(*) FROM tbl_master_fund where receiver_id = 1) as entry_count FROM' at line 1 (SQL: SELECT t1.id AS lev1, t2.id AS lev2, t3.id AS lev3, t4.id AS lev4, t5.id AS lev5, t6.id AS lev6, t7.id AS lev7, t8.id AS lev8, t9.id AS lev9, t10.id AS lev10 (SELECT COUNT(*) FROM tbl_master_fund where receiver_id = 1) as entry_count FROM tbl_atest_unilevel AS t1 LEFT JOIN tbl_atest_unilevel AS t2 ON t2.sponsorid = t1.id LEFT JOIN tbl_atest_unilevel AS t3 ON t3.sponsorid = t2.id LEFT JOIN tbl_atest_unilevel AS t4 ON t4.sponsorid = t3.id LEFT JOIN tbl_atest_unilevel AS t5 ON t5.sponsorid = t4.id LEFT JOIN tbl_atest_unilevel AS t6 ON t6.sponsorid = t5.id LEFT JOIN tbl_atest_unilevel AS t7 ON t7.sponsorid = t6.id LEFT JOIN tbl_atest_unilevel AS t8 ON t8.sponsorid = t7.id LEFT JOIN tbl_atest_unilevel AS t9 ON t9.sponsorid = t8.id LEFT JOIN tbl_atest_unilevel AS t10 ON t10.sponsorid = t9.id LEFT JOIN tbl_atest_unilevel AS t11 ON t11.sponsorid = t10.id WHERE t1.sponsorid = 1 HAVING entry_count > 0 )

编辑:现在显示是这样的。但我想检查每个级别的每个用户的计数

我想要完成的只是获取用户在表2中有记录

Answer 1

在 lev10 之后漏掉了一个逗号

   SELECT t1.id AS lev1, t2.id AS lev2, t3.id AS lev3, t4.id AS lev4, t5.id AS lev5, t6.id AS lev6, t7.id AS lev7, t8.id AS lev8, t9.id AS lev9, t10.id AS lev10, (SELECT COUNT(*) FROM tbl_master_fund where receiver_id = 1) as entry_count FROM tbl_users AS t1 LEFT JOIN tbl_users AS t2 ON t2.sponsorid = t1.id LEFT JOIN tbl_users AS t3 ON t3.sponsorid = t2.id LEFT JOIN tbl_users AS t4 ON t4.sponsorid = t3.id LEFT JOIN tbl_users AS t5 ON t5.sponsorid = t4.id LEFT JOIN tbl_users AS t6 ON t6.sponsorid = t5.id LEFT JOIN tbl_users AS t7 ON t7.sponsorid = t6.id LEFT JOIN tbl_users AS t8 ON t8.sponsorid = t7.id LEFT JOIN tbl_users AS t9 ON t9.sponsorid = t8.id LEFT JOIN tbl_users AS t10 ON t10.sponsorid = t9.id LEFT JOIN tbl_users AS t11 ON t11.sponsorid = t10.id WHERE t1.sponsorid = 1 HAVING entry_count > 0