select a.tid,c.cb from teacher a,tc b,course c where a.tid = b.tid and b.cid = c.cid and a.tid = .. and c.cid = ..
SELECT T.TID,C.CID,TC.CID FROM TC INNER JOIN COURSE C ON TC.CID=C.CID INNER JOIN TEACHER T ON TC.TID=T.TID WHERE TC.TID=? AND C.CID=?
不好意思,看错了,应该这样;WITH TEMP AS SELECT TID,CID,COUNT(TID) AS COUNT FROM TC GROUP BY TID,CID SELECT T.TID,C.CID,TC.COUNT FROM TEMP TC INNER JOIN COURSE C ON TC.CID=C.CID INNER JOIN TEACHER T ON TC.TID=T.TID WHERE TC.TID=? AND C.CID=?
CREATE TABLE TEACHER ( TID VARCHAR(50), NAME VARCHAR(50) ) CREATE TABLE COURSE ( CID VARCHAR(50), CB INT ) CREATE TABLE TC ( TID VARCHAR(50), CID VARCHAR(50) )INSERT INTO TEACHER VALUES('A','ZHAWNG') INSERT INTO TEACHER VALUES('B','LISI')INSERT INTO COURSE VALUES('1',3) INSERT INTO COURSE VALUES('2',30)INSERT INTO TC VALUES('A','2')SELECT T1.*,T2.CB,T3.* FROM TEACHER T1 INNER JOIN (SELECT * FROM TC ) T3 ON T1.TID = T3.TID INNER JOIN (SELECT * FROM COURSE) T2 ON T3.CID=T2.CID /* RESULT TID NAME CB TID CID A ZHAWNG 30 A 2 */
表结构 teacher(tid,....) ,course(cid,....),tc(tid,cid,cb)-- 现在要选某个老师的就直接 select cb from tc where tid=@tid -- 要查看课程的 select sum(cb) as cb from tc where cid=@id-- 要查某个老师的某一门课的 select cb from tc where tid=@tid and cid=@cid
这样才能统计一个老师点了各种课程的次数。也可以求出一个程课的总点的数。
select a.tid,c.cb
from teacher a,tc b,course c
where a.tid = b.tid and b.cid = c.cid
and a.tid = .. and c.cid = ..
SELECT T.TID,C.CID,TC.CID
FROM TC
INNER JOIN COURSE C
ON TC.CID=C.CID
INNER JOIN TEACHER T
ON TC.TID=T.TID
WHERE TC.TID=? AND C.CID=?
AS
SELECT TID,CID,COUNT(TID) AS COUNT
FROM TC
GROUP BY TID,CID
SELECT T.TID,C.CID,TC.COUNT
FROM TEMP TC
INNER JOIN COURSE C
ON TC.CID=C.CID
INNER JOIN TEACHER T
ON TC.TID=T.TID
WHERE TC.TID=? AND C.CID=?
还有,请问楼上的temp是什么意思,有点不太明白
CREATE TABLE TEACHER
(
TID VARCHAR(50),
NAME VARCHAR(50)
)
CREATE TABLE COURSE
(
CID VARCHAR(50),
CB INT
)
CREATE TABLE TC
(
TID VARCHAR(50),
CID VARCHAR(50)
)INSERT INTO TEACHER VALUES('A','ZHAWNG')
INSERT INTO TEACHER VALUES('B','LISI')INSERT INTO COURSE VALUES('1',3)
INSERT INTO COURSE VALUES('2',30)INSERT INTO TC VALUES('A','2')SELECT T1.*,T2.CB,T3.* FROM TEACHER T1 INNER JOIN (SELECT * FROM TC )
T3 ON T1.TID = T3.TID INNER JOIN (SELECT * FROM COURSE) T2 ON T3.CID=T2.CID
/* RESULT
TID NAME CB TID CID
A ZHAWNG 30 A 2
*/
表结构 teacher(tid,....) ,course(cid,....),tc(tid,cid,cb)-- 现在要选某个老师的就直接 select cb from tc where tid=@tid
-- 要查看课程的 select sum(cb) as cb from tc where cid=@id-- 要查某个老师的某一门课的 select cb from tc where tid=@tid and cid=@cid