我想查出各位老师所上课程的次数,及提成计算(次数*价格)
之前的查出每位老师的上课次数我倒是成功了,现在就是对于怎么从另一个表得到相应课程的价格再乘以次数得出提成有问题!
之前的语句:
SELECT EACHER as 老师,LESSONID as 课程,count(*) as 次数 FROM TABLELESSON group by TEACHER,LESSONID
SELECT a.TEACHER as 老师,a.LESSONID as 课程,count(*)*b.price as 价格
FROM TABLELESSON a,tableinfo b
where a.lessonid=b.lessonid
group by a.TEACHER,a.LESSONID,b.price
select b.老师,b.LESSONID as 课程,b.次数,b.次数*a.price 提成计算
from tableinfo a,(
SELECT TEACHER as 老师,LESSONID ,count(*) as 次数 FROM TABLELESSON group by TEACHER,LESSONID) b
where a.LESSONID=b.LESSONID