select a.question_id,a.question_content,
sum(case when b.isagree='同意' then 1 else 0 end) [同意数],
sum(case when b.isagree='不同意' then 1 else 0 end) [不同意数]
from question_table a
join vote_table b
on a.question_id=b.question_id
group by a.question_id,a.question_content
sum(case when b.isagree='同意' then 1 else 0 end) [同意数],
sum(case when b.isagree='不同意' then 1 else 0 end) [不同意数]
from question_table a
join vote_table b
on a.question_id=b.question_id
group by a.question_id,a.question_content
group by q.question_content
(
question_id varchar(10) primary key not null,
question_content varchar(20)
)create table vote_table
(
number_id varchar(10) primary key not null,
question_id varchar(10),
isagree varchar(10)
)
select f.question_id,f.question_content,sum(case when h.isagree='true' then 1 else 0 end) as 同意人数,
sum(case when h.isagree='false' then 1 else 0 end)as 不同意人数 from question_table f right outer join vote_table h
on f.question_id=h.question_id group by f.question_id,f.question_content
from question_table a,
(select question_id,sum(case when isagree=1 then 1 else 0 end) agreecount
sum(case when isagree=0 then 0 else 1 end notagreecount
from vote_table group by question_id) b
where a.question_id=b.question_id
下面想进一步讨论一下:
现有两个表question_table(question_id,question_content)
vote_table(number_id,question_id,isagree)
isagree(true,false)表示同意否 number_id表示投票人的号码,number_id,question_id同时作为主键
我现在想用一条语句选择出question_content以及对应的 同意的人数和不同意的人数进一步讨论一下,
现有另外一个表:user_table(number_id,level),用来存储所有有权投票的人(level>=1),那怎样在一条语句中
选择出 question_content以及对应的 同意的人数,不同意的人数和有权投票而未投票的人呢?
from question_table a,
(select question_id,sum(case when isagree=1 then 1 else 0 end) agreecount
sum(case when isagree=0 then 0 else 1 end notagreecount
from vote_table group by question_id) b
where a.question_id=b.question_id