表A:ExaminationBank 别名:EB 考试试卷管理表(例:MBA语文能力测试试卷)
表B:ExamCategory 别名:EC 试卷类型表(例:综合测试,课程练习...)
表C: SubjectCategory 别名:SC 学习科目表(例:语文,数学。。。)
我想根据试卷的类型 查询 试卷管理表里的 每个学习科目里 取一条数据出来
写了一条SQL查询语句
SELECT
EB.ExamId,
EB.ExamName,
EB.ExamCategoryId,
EB.SubjectId,
EC.CategoryName AS ExamCategoryName,
SC.CategoryName as SubjectCategoryName
FROM dbo.ExaminationBank AS EB INNER JOIN
dbo.ExamCategory AS EC ON EB.ExamCategoryId = EC.CategoryId INNER JOIN
dbo.SubjectCategory AS SC ON EB.SubjectId = SC.CategoryId
WHERE (EB.ExamId IN (SELECT TOP (1) ExamId FROM dbo.ExaminationBank WHERE (SubjectId = EB.SubjectId))) AND (EC.CategoryId = @ExamCategoryId )
查询后的语句,总有是一个学习科目的类型查不出来,不知道这条语句是不是正确的
请高手指点一下
5 个解决方案
#1
数据~~我要数据~~
#2
SELECT没看到数据只能帮猜
EB.ExamId,
EB.ExamName,
EB.ExamCategoryId,
EB.SubjectId,
EC.CategoryName AS ExamCategoryName,
SC.CategoryName as SubjectCategoryName
FROM dbo.ExaminationBank AS EB
LEFT JOIN dbo.ExamCategory AS EC ON EB.ExamCategoryId = EC.CategoryId
LEFT JOIN dbo.SubjectCategory AS SC ON EB.SubjectId = SC.CategoryId
WHERE EB.ExamId IN (SELECT TOP 1 ExamId
FROM dbo.ExaminationBank
WHERE (SubjectId = EB.SubjectId))
AND EC.CategoryId = @ExamCategoryId
#3
表数据不清。
#4
请给出测试数据,和所需要的结果,谢谢
#5
SELECT
EB.ExamId, 考卷表主键
EB.ExamName, 考卷名称
EB.ExamCategoryId, 考卷类型ID(考卷类型表主键)
EB.SubjectId, 科目类型ID(科目表主键)
EC.CategoryName AS ExamCategoryName, 考卷类型名称(考卷类型表)
SC.CategoryName as SubjectCategoryName 科目类型名称(科目类型表)
FROM dbo.ExaminationBank AS EB
LEFT JOIN dbo.ExamCategory AS EC ON EB.ExamCategoryId = EC.CategoryId
LEFT JOIN dbo.SubjectCategory AS SC ON EB.SubjectId = SC.CategoryId
WHERE EB.ExamId IN (SELECT TOP 1 ExamId
FROM dbo.ExaminationBank
WHERE (SubjectId = EB.SubjectId))
AND EC.CategoryId = @ExamCategoryId
EB.ExamId, 考卷表主键
EB.ExamName, 考卷名称
EB.ExamCategoryId, 考卷类型ID(考卷类型表主键)
EB.SubjectId, 科目类型ID(科目表主键)
EC.CategoryName AS ExamCategoryName, 考卷类型名称(考卷类型表)
SC.CategoryName as SubjectCategoryName 科目类型名称(科目类型表)
FROM dbo.ExaminationBank AS EB
LEFT JOIN dbo.ExamCategory AS EC ON EB.ExamCategoryId = EC.CategoryId
LEFT JOIN dbo.SubjectCategory AS SC ON EB.SubjectId = SC.CategoryId
WHERE EB.ExamId IN (SELECT TOP 1 ExamId
FROM dbo.ExaminationBank
WHERE (SubjectId = EB.SubjectId))
AND EC.CategoryId = @ExamCategoryId
#1
数据~~我要数据~~
#2
SELECT没看到数据只能帮猜
EB.ExamId,
EB.ExamName,
EB.ExamCategoryId,
EB.SubjectId,
EC.CategoryName AS ExamCategoryName,
SC.CategoryName as SubjectCategoryName
FROM dbo.ExaminationBank AS EB
LEFT JOIN dbo.ExamCategory AS EC ON EB.ExamCategoryId = EC.CategoryId
LEFT JOIN dbo.SubjectCategory AS SC ON EB.SubjectId = SC.CategoryId
WHERE EB.ExamId IN (SELECT TOP 1 ExamId
FROM dbo.ExaminationBank
WHERE (SubjectId = EB.SubjectId))
AND EC.CategoryId = @ExamCategoryId
#3
表数据不清。
#4
请给出测试数据,和所需要的结果,谢谢
#5
SELECT
EB.ExamId, 考卷表主键
EB.ExamName, 考卷名称
EB.ExamCategoryId, 考卷类型ID(考卷类型表主键)
EB.SubjectId, 科目类型ID(科目表主键)
EC.CategoryName AS ExamCategoryName, 考卷类型名称(考卷类型表)
SC.CategoryName as SubjectCategoryName 科目类型名称(科目类型表)
FROM dbo.ExaminationBank AS EB
LEFT JOIN dbo.ExamCategory AS EC ON EB.ExamCategoryId = EC.CategoryId
LEFT JOIN dbo.SubjectCategory AS SC ON EB.SubjectId = SC.CategoryId
WHERE EB.ExamId IN (SELECT TOP 1 ExamId
FROM dbo.ExaminationBank
WHERE (SubjectId = EB.SubjectId))
AND EC.CategoryId = @ExamCategoryId
EB.ExamId, 考卷表主键
EB.ExamName, 考卷名称
EB.ExamCategoryId, 考卷类型ID(考卷类型表主键)
EB.SubjectId, 科目类型ID(科目表主键)
EC.CategoryName AS ExamCategoryName, 考卷类型名称(考卷类型表)
SC.CategoryName as SubjectCategoryName 科目类型名称(科目类型表)
FROM dbo.ExaminationBank AS EB
LEFT JOIN dbo.ExamCategory AS EC ON EB.ExamCategoryId = EC.CategoryId
LEFT JOIN dbo.SubjectCategory AS SC ON EB.SubjectId = SC.CategoryId
WHERE EB.ExamId IN (SELECT TOP 1 ExamId
FROM dbo.ExaminationBank
WHERE (SubjectId = EB.SubjectId))
AND EC.CategoryId = @ExamCategoryId