Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 объединить 2 запроса в один  [new]
Sv219
Member

Откуда: Омск
Сообщений: 976
как можно объединить 2 запроса в один?
select s3.Value , s2.Value, s1.Value, CAST(q.QuestionID AS VARCHAR), COUNT(sah.AnsweredCorrectly)
from Question q
inner join [Subject] s1 ON s1.SubjectID = q.SubjectID
inner join [Subject] s2 ON s2.SubjectID = s1.ParentID
inner join [Subject] s3 ON s3.SubjectID = s2.ParentID
left join StudentAnswerHistory sah ON q.QuestionID = sah.QuestionID AND sah.AnsweredCorrectly = 0
GROUP BY s3.Value, s2.Value, s1.Value, CAST(q.QuestionID AS VARCHAR)

select s3.Value , s2.Value, s1.Value, CAST(q.QuestionID AS VARCHAR), COUNT(sah.AnsweredCorrectly)
from Question q
inner join [Subject] s1 ON s1.SubjectID = q.SubjectID
inner join [Subject] s2 ON s2.SubjectID = s1.ParentID
inner join [Subject] s3 ON s3.SubjectID = s2.ParentID
left join StudentAnswerHistory sah ON q.QuestionID = sah.QuestionID AND sah.AnsweredCorrectly = 1
GROUP BY s3.Value, s2.Value, s1.Value, CAST(q.QuestionID AS VARCHAR)
10 июн 09, 12:55    [7285284]     Ответить | Цитировать Сообщить модератору
 Re: объединить 2 запроса в один  [new]
Glory
Member

Откуда:
Сообщений: 104760
select s3.Value , s2.Value, s1.Value, CAST(q.QuestionID AS VARCHAR), COUNT(sah.AnsweredCorrectly)
from Question q
inner join [Subject] s1 ON s1.SubjectID = q.SubjectID
inner join [Subject] s2 ON s2.SubjectID = s1.ParentID
inner join [Subject] s3 ON s3.SubjectID = s2.ParentID
left join StudentAnswerHistory sah ON q.QuestionID = sah.QuestionID AND sah.AnsweredCorrectly IN(0,1)
GROUP BY s3.Value, s2.Value, s1.Value, CAST(q.QuestionID AS VARCHAR)
10 июн 09, 12:57    [7285295]     Ответить | Цитировать Сообщить модератору
 Re: объединить 2 запроса в один  [new]
alx111
Member

Откуда:
Сообщений: 92
select s3.Value , s2.Value, s1.Value, CAST(q.QuestionID AS VARCHAR), SUM(case when sah.AnsweredCorrectly = 0 then 1 else 0 end) UnCorrectly, SUM(case when sah.AnsweredCorrectly = 1 then 1 else 0 end) Correctly
from Question q
inner join [Subject] s1 ON s1.SubjectID = q.SubjectID
inner join [Subject] s2 ON s2.SubjectID = s1.ParentID
inner join [Subject] s3 ON s3.SubjectID = s2.ParentID
left join StudentAnswerHistory sah ON q.QuestionID = sah.QuestionID AND sah.AnsweredCorrectly IN(0,1)
GROUP BY s3.Value, s2.Value, s1.Value, CAST(q.QuestionID AS VARCHAR)
10 июн 09, 13:04    [7285341]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить