I am creating a stored procedure that views the results of polls answers by counting the votes for each choice, however when I run it each select statement is in single table, but I want the result in single table while each select statement is a column, anyone knows how to do it. thanks :) thats my code:
我正在创建一个存储过程,通过计算每个选项的投票来查看民意调查答案的结果,但是当我运行它时,每个select语句都在单个表中,但我希望结果在单个表中,而每个select语句都是一个列,谁都知道怎么做。谢谢:)多数民众赞成我的代码:
CREATE PROC view_pollresult
@myemail VARCHAR (30),
@course_ID INT,
@poll_ID INT
AS
IF @myemail = (SELECT page_creator FROM Course_pages WHERE course_ID = @course_ID OR @myemail IN (SELECT added_email
FROM Lecturers_Adds_Academics_Course_page WHERE course_ID = @course_ID))
BEGIN
SELECT COUNT(choice) FROM Students_Answers_Polls AS choice1_results
WHERE poll_ID = @poll_ID AND course_page = @course_ID
AND choice = (SELECT choice1 FROM Polls WHERE poll_ID = @poll_ID)
SELECT COUNT(choice) FROM Students_Answers_Polls AS choice2_results
WHERE poll_ID = @poll_ID AND course_page = @course_ID
AND choice = (SELECT choice2 FROM Polls WHERE poll_ID = @poll_ID)
SELECT COUNT(choice) FROM Students_Answers_Polls AS choice3_results
WHERE poll_ID = @poll_ID AND course_page = @course_ID
AND choice = (SELECT choice3 FROM Polls WHERE poll_ID = @poll_ID)
SELECT COUNT(choice) FROM Students_Answers_Polls AS choice4_results
WHERE poll_ID = @poll_ID AND course_page = @course_ID
AND choice = (SELECT choice4 FROM Polls WHERE poll_ID = @poll_ID)
SELECT COUNT(choice) FROM Students_Answers_Polls AS choice5_results
WHERE poll_ID = @poll_ID AND course_page = @course_ID
AND choice = (SELECT choice5 FROM Polls WHERE poll_ID = @poll_ID)
END
2 个解决方案
#1
2
Just make it a single statement:
只需将其作为一个声明:
SELECT
SUM(case when results.choice = p.choice1 then 1 else 0 end) as Choice1Count,
SUM(case when results.choice = p.choice2 then 1 else 0 end) as Choice2Count,
SUM(case when results.choice = p.choice3 then 1 else 0 end) as Choice3Count,
SUM(case when results.choice = p.choice4 then 1 else 0 end) as Choice4Count,
SUM(case when results.choice = p.choice5 then 1 else 0 end) as Choice5Count
FROM
Students_Answers_Polls AS results
inner join Polls p on
results.poll_id = p.poll_id
and results.choice in (p.choice1, p.choice2, p.choice3, p.choice4, p.choice5)
WHERE
results.poll_ID = @poll_ID
AND course_page = @course_ID
#2
0
What you need to do is to create a select statement that joins all the tables and returns only the columns you need into one table. Try something like this:
您需要做的是创建一个select语句,该语句连接所有表并仅将您需要的列返回到一个表中。尝试这样的事情:
select count(a.choice) as choice1_results, count(b.choice) as choice2_results
from students_answers_polls a
inner join students_ansers_polls b on a.couseId = b.courseId
and a.poll = (select choice1 from polls where poll_Id = @poll_ID)
and b.poll = (select choice2 from polls where poll_id = @poll_ID)
And so on...
等等...
#1
2
Just make it a single statement:
只需将其作为一个声明:
SELECT
SUM(case when results.choice = p.choice1 then 1 else 0 end) as Choice1Count,
SUM(case when results.choice = p.choice2 then 1 else 0 end) as Choice2Count,
SUM(case when results.choice = p.choice3 then 1 else 0 end) as Choice3Count,
SUM(case when results.choice = p.choice4 then 1 else 0 end) as Choice4Count,
SUM(case when results.choice = p.choice5 then 1 else 0 end) as Choice5Count
FROM
Students_Answers_Polls AS results
inner join Polls p on
results.poll_id = p.poll_id
and results.choice in (p.choice1, p.choice2, p.choice3, p.choice4, p.choice5)
WHERE
results.poll_ID = @poll_ID
AND course_page = @course_ID
#2
0
What you need to do is to create a select statement that joins all the tables and returns only the columns you need into one table. Try something like this:
您需要做的是创建一个select语句,该语句连接所有表并仅将您需要的列返回到一个表中。尝试这样的事情:
select count(a.choice) as choice1_results, count(b.choice) as choice2_results
from students_answers_polls a
inner join students_ansers_polls b on a.couseId = b.courseId
and a.poll = (select choice1 from polls where poll_Id = @poll_ID)
and b.poll = (select choice2 from polls where poll_id = @poll_ID)
And so on...
等等...