SELECT A.DNAME,COUNT(*) AS COUNT
INTO #AA
FROM D A,P B,R C
WHERE C.RDATE='2002-11-30'
AND A.DNO=C.DNO
AND B.PNO=C.PNO
SELECT A.DNAME,MAX(COUNT) AS COUNT
FROM #AA
DROP TABLE
INTO #AA
FROM D A,P B,R C
WHERE C.RDATE='2002-11-30'
AND A.DNO=C.DNO
AND B.PNO=C.PNO
SELECT A.DNAME,MAX(COUNT) AS COUNT
FROM #AA
DROP TABLE
INTO #AA
FROM D A,P B,R C
WHERE C.RDATE='2002-11-30'
AND A.DNO=C.DNO
AND B.PNO=C.PNO
SELECT A.DNAME,MAX(COUNT) AS COUNT
FROM #AA
DROP TABLE #AA
A.Dname,D.Droom
from
(select
D.Dname,D.Droom,count(R.*) AS Num
from
医生表D D,
就诊记录表R R,
病人表P P
where
D.Dno=R.Dno and R.Pno=P.Pno and datediff(dd,R.Rdate,'2002-11-30')=0) A,
(select
V.Droom,MAX(V.Num) AS Num
from
(select
D.Dname,Droom,count(R.*) AS Num
from
医生表D D,
就诊记录表R R,
病人表P P
where
D.Dno=R.Dno and R.Pno=P.Pno and datediff(dd,R.Rdate,'2002-11-30')=0) V
group by
V.Droom) B
where
A.Num=B.Num