错误SQL:
SELECT COUNT(*) AS tp_count FROM `user` LEFT JOIN user randTable921 on user.belong_user_id=randTable921.user_id LEFT JOIN user randTable239 on user.invit_user_id=randTable239.user_id WHERE ( user.is_deleted=0 ) HAVING invit_name='陈小宝' LIMIT 1错误原因:invit_name 为字段别名,实际上不存在的。运行错误:invit_name 不存在。下面的查询语句,运行正确:上面的count语句就是统计下面的条数的。请问如何实现。上面功能才不出错?SELECT user.*,randTable5.user_name as belong_name,randTable121.user_name as invit_name FROM `user` LEFT JOIN user randTable5 on user.belong_user_id=randTable5.user_id LEFT JOIN user randTable121 on user.invit_user_id=randTable121.user_id WHERE ( user.is_deleted = '0' ) HAVING invit_name='陈小宝'
SELECT COUNT(*) AS tp_count FROM `user` LEFT JOIN user randTable921 on user.belong_user_id=randTable921.user_id LEFT JOIN user randTable239 on user.invit_user_id=randTable239.user_id WHERE ( user.is_deleted=0 ) HAVING invit_name='陈小宝' LIMIT 1错误原因:invit_name 为字段别名,实际上不存在的。运行错误:invit_name 不存在。下面的查询语句,运行正确:上面的count语句就是统计下面的条数的。请问如何实现。上面功能才不出错?SELECT user.*,randTable5.user_name as belong_name,randTable121.user_name as invit_name FROM `user` LEFT JOIN user randTable5 on user.belong_user_id=randTable5.user_id LEFT JOIN user randTable121 on user.invit_user_id=randTable121.user_id WHERE ( user.is_deleted = '0' ) HAVING invit_name='陈小宝'
invit_name='陈小宝' LIMIT 1invit_name:是哪个表的?SELECT user.*,randTable5.user_name as belong_name,randTable121.user_name as invit_name FROM `user` LEFT JOIN user randTable5 on user.belong_user_id=randTable5.user_id LEFT JOIN user randTable121 on user.invit_user_id=randTable121.user_id WHERE ( user.is_deleted = '0' ) HAVING invit_name='陈小宝'不是标准的SQL语句
FROM `user` LEFT JOIN user randTable921 on user.belong_user_id=randTable921.user_id
LEFT JOIN user randTable239 on user.invit_user_id=randTable239.user_id
WHERE ( user.is_deleted=0 )
and invit_name='陈小宝'
LIMIT 1
invit_name:是哪个表的?不是任何表的,是user表字段user_name的别名。
LEFT JOIN user randTable239 on user.invit_user_id=randTable239.user_id WHERE ( user.is_deleted=0 ) HAVING invit_name='陈小宝'
仅这个语句,看不出在哪定义了 invit_name 为别名。
user.user_name='陈小宝' LIMIT 1orSELECT COUNT(*) AS tp_count FROM(
SELECT COUNT(*) AS tp_count FROM `user` LEFT JOIN user randTable921 on user.belong_user_id=randTable921.user_id LEFT JOIN user randTable239 on user.invit_user_id=randTable239.user_id
) a
WHERE ( a.is_deleted=0 ) HAVING
a.user_name='陈小宝' LIMIT 1
SELECT COUNT(*) AS tp_count
FROM(
SELECT user.*,randTable5.user_name as belong_name,randTable121.user_name as invit_name
FROM `user`
LEFT JOIN user randTable5 on user.belong_user_id=randTable5.user_id
LEFT JOIN user randTable121 on user.invit_user_id=randTable121.user_id
)
WHERE ( is_deleted = '0' )
HAVING invit_name='陈小宝'试试
SELECT COUNT(*) AS tp_count
FROM(
SELECT user.*,randTable5.user_name as belong_name,randTable121.user_name as invit_name
FROM `user`
LEFT JOIN user randTable5 on user.belong_user_id=randTable5.user_id
LEFT JOIN user randTable121 on user.invit_user_id=randTable121.user_id
WHERE ( is_deleted = '0' )
HAVING invit_name='陈小宝'
)
to biandongfeng aliuguangjin 楼上2位的 sql语句执行错误: Every derived table must have its own alias
多谢回复。我这是sql是thinkphp 自动生成的 。得到sql后,我还要想thinkphp代码如何写。