错误 SQL 查询:使用 SUM 方法,当 #1054 - 'where 子句'中的未知列'tbl_customers.id' [英] Error SQL query: Use SUM method, When #1054 - Unknown column 'tbl_customers.id' in 'where clause'
本文介绍了错误 SQL 查询:使用 SUM 方法,当 #1054 - 'where 子句'中的未知列'tbl_customers.id'的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当我们表客户 id 时出现错误#1054 - 'where 子句'中的未知列'tbl_customers.id'其实问题是每个派生表都必须有自己的别名.
When us table customer id than getting error #1054 - Unknown column 'tbl_customers.id' in 'where clause' Actually problems is Every derived table must have its own alias.
喜欢下面的这个查询.
SELECT tbl_customers.*,(SELECT SUM(amount) As Amount
FROM
(
SELECT tcc.entry_fees*COUNT(tccc.match_contest_id) as amount
FROM `tbl_cricket_customer_contests` tccc
LEFT JOIN tbl_cricket_contest_matches tccm on(tccm.id=tccc.match_contest_id)
LEFT JOIN tbl_cricket_contests tcc ON (tcc.id=tccm.contest_id)
WHERE tccc.customer_id = tbl_customers.id GROUP BY tccc.match_contest_id
) As DT) as spendamount
FROM (`tbl_customers`)
WHERE `tbl_customers`.`is_deleted` = 'N'
GROUP BY `tbl_customers`.`id`
ORDER BY `spendamount` DESC
在查询中遵循此表关系结构.
Below table relationship structure following this in query.
推荐答案
如果我想要总量,不要嵌套聚合函数并删除分组:
If I want the total amount, don't nest the aggregation functions and remove the group by:
SELECT tbl_customers.*,(SELECT SUM(tcc.entry_fees) as amount
FROM tbl_cricket_customer_contests tccc
JOIN tbl_cricket_contest_matches tccm ON tccm.id = tccc.match_contest_id
JOIN tbl_cricket_contests tcc ON tcc.id = tccm.contest_id
WHERE tccc.customer_id = tbl_customers.id ) as spendamount
FROM (`tbl_customers`)
WHERE `tbl_customers`.`is_deleted` = 'N'
GROUP BY `tbl_customers`.`id`
ORDER BY `spendamount` DESC
我还将 LEFT JOIN 更改为 JOIN.您正在对最后一个表中的值求和,因此只有匹配的行才对总和有贡献.
I also changed the LEFT JOIN to JOIN. You are summing values from the last table, so only matching rows contribute to the sum.
这篇关于错误 SQL 查询:使用 SUM 方法,当 #1054 - 'where 子句'中的未知列'tbl_customers.id'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文