select substrb(room_no,1,2) , nvl(t.b,0) from tbl_xstj x , (select substrb(room_no),1,2) a ,count(p_key) b from tbl_xstj where endtime is null and begintime is not null group by a) t where x.substrb(room_no,1,2) = t.substrb(room_no,1,2) group by x.substrb(room_no,1,2); 这样试试呢,
表别名都放在字段前面,而不能放在函数前边
from tbl_xstj x ,
(select substrb(room_no),1,2) a ,count(p_key) b
from tbl_xstj
where endtime is null and begintime is not null
group by a) t
where x.substrb(room_no,1,2) = t.substrb(room_no,1,2)
group by x.substrb(room_no,1,2);
这样试试呢,