SELECT
COUNT(CCT.ID) AS 记录数C,
COUNT(ECT.ID) AS 记录数E,
COUNT(SYT.ID) AS 记录数S,
SchoolName AS 学院名称
FROM
ShiYanTable SYT,
ExamCreditTable ECT,
CourseCreditTable CCT,
School S
WHERE
CCT.SchoolID=ECT.SchoolID AND ECT.SchoolID=SYT.SchoolID
AND SYT.SchoolID=S.SchoolID
GROUP BY
S.SchoolName
为什么这样扫描不出正确的三个记录数呢?
COUNT(CCT.ID) AS 记录数C,
COUNT(ECT.ID) AS 记录数E,
COUNT(SYT.ID) AS 记录数S,
SchoolName AS 学院名称
FROM
ShiYanTable SYT,
ExamCreditTable ECT,
CourseCreditTable CCT,
School S
WHERE
CCT.SchoolID=ECT.SchoolID AND ECT.SchoolID=SYT.SchoolID
AND SYT.SchoolID=S.SchoolID
GROUP BY
S.SchoolName
为什么这样扫描不出正确的三个记录数呢?
COUNT(ECT.ID) AS 记录数E,
COUNT(SYT.ID) AS 记录数S,--
sum(case when cct.id is not null then 1 else 0 end) as c,
sum(case when ect.id is not null then 1 else 0 end) as e,
sum(case when syt.id is not null then 1 else 0 end) as s
SELECT
sum(case when cct.id is not null then 1 else 0 end) as c,
sum(case when ect.id is not null then 1 else 0 end) as e,
sum(case when syt.id is not null then 1 else 0 end) as s
,
SchoolName AS 学院名称
FROM School S left join
ShiYanTable SYT on SYT.SchoolID=S.SchoolID
left join ExamCreditTable ECT on ECT.SchoolID=SYT.SchoolID
left join CourseCreditTable CCT on CCT.SchoolID=ECT.SchoolID
GROUP BY
S.SchoolName
还有个问题,如果我想对count出来的结果进行排序,怎么样放置order by 呢?
SELECT
COUNT(ID) AS 记录数,
SchoolName AS 学院名称
FROM
School S left join
CourseCreditTable CCT on CCT.SchoolID=S.SchoolID
GROUP BY
S.SchoolName
我想对COUNT(ID) AS 记录数进行排序,请问怎么写这个语句呢?
COUNT(ID) AS 记录数,
SchoolName AS 学院名称
FROM
School S left join
CourseCreditTable CCT on CCT.SchoolID=S.SchoolID
GROUP BY
S.SchoolName
ORDER BY COUNT(ID)---ORDER BY 1
得好好补补了