sql模版:select *,(select xx from tableName) from tableName
子查询使用要注意的:
- 只能返回一个值
- 一定要使用别名区别表,除非使用的表属性都不重名
再说个最近遇到的问题sql:
select abc.id, (select c.collect_supervisor_id from (select bx.id from t_bills bx where bx.bid_id=abc.bid_id) b join t_bills_assign c on (b.id=c.bill_id and c.isAssign=1) order by c.lastest_assign_time desc limit 0,1) 'sid' from t_bills abc where (TO_DAYS(abc.repayment_time) - TO_DAYS(now()))=0 and abc.`status`=-3 and abc.`overdue_mark`=-2
总是报[Err] 1054 - Unknown column 'abc.bid_id' in 'where clause',但abc.bid_id是存在的
最后经过一大番努力,修改成这样:
SELECT abc.id, ( SELECT c.collect_supervisor_id FROM t_bills b, t_bills_assign c WHERE b.id = c.bill_id AND c.isAssign = 1 AND abc.bid_id = b.bid_id ORDER BY c.lastest_assign_time DESC LIMIT 0, 1 ) 'sid' FROM t_bills abc WHERE ( TO_DAYS(abc.repayment_time) - TO_DAYS(now())) = 0 AND abc.`status` =- 3 AND abc.`overdue_mark` =- 2
原因是select这个嵌套的子查询不能超过两层,不然这个abc.bid_id就用不上了。感觉这个原因挺奇怪的,毕竟超过两层可以用在逻辑上是没有问题的!