在asp.net编程中用sql写一个分组统计程序

来源：百度知道 编辑：UC知道 时间：2024/06/07 05:41:45
有三个表
Chapter[ChapterID,ChapterName,CourseID]:章节id，章节名称，课程id
Course[CourseID,CoirseName]：课程id，课程名称
Question[QuestionID,QuestionContent,QuestionRightAnser,ChapterID,Type,DifficultyLevel]：试题id，试题内容，试题答案，章节id，题型，难度
根据课程、章节、题型统计有多少道题，怎么编呢？

3表相连,用group by

select Chapter.CourseID,Chapter.ChapterID,Type,count(1)
from Question
join Chapter
on Question.ChapterID=Chapter.ChapterID

select a.CourseID,a.ChapterID,c.Type,sum(1)
from Question a,Chapter b,Chapter c
where a.ChapterID = b.ChapterID
and b.CourseID = c.CourseID
group by a.CourseID,a.ChapterID,c.Type

SELECT COUNT(*)
FROM Chapter
LEFT JOIN Course ON Chapter.ChapterID = Course.CourseID
LEFT JOIN Question ON Chapter.ChapterID = Question.CourseID
WHERE Chapter.CourseID = 'XX'
AND Chapter.ChapterName = 'XX'
AND Question.题型 = 'XX'