此题目只可用一句SQL语句,不可用存储过程。在MS Sql Server里有两个表:T_Question(问题表),T_Vote(投票表),结构如下:T_Question:
id,int,自动编号
Title,varchar(50),标题
Content,varchar(250),内容T_Vote:
id,int,自动编号
Questionid,int,问题ID
Style,bit,0代表不赞成,1代表赞成请用一条SQL语句列出赞成率在60%与100%之间的问题。
id,int,自动编号
Title,varchar(50),标题
Content,varchar(250),内容T_Vote:
id,int,自动编号
Questionid,int,问题ID
Style,bit,0代表不赞成,1代表赞成请用一条SQL语句列出赞成率在60%与100%之间的问题。
select Q.id, Q.Title, RateOfAssent=A.Assent*1.0/B.Total
from T_Question Q
join (select Questionid,Assent=count(1) from T_Vote where Style=1 group by Questionid) A on Q.id=A.Questionid
join (select Questionid,Total=count(1) from T_Vote group by Questionid) B on Q.id=B.Questionid
where A.Assent*1.0/B.Total>=0.6
from t_question a
inner join
(select questionid,
count(*) allNum,
count(case when style=1 then 1 else null end) agreeNum
--或者
--sum( cast (style as int)) agreenum
from t_vote grouop by questionid
) b
on a.id=questionid
and (cast agreenum as decimal(10,2))/isnull(nullif(agreenum,0),1)*100 between 60 and 100
--或者. 若allnum为0则表示此id无记录,则不会出现此行,而不用做如上处理。或先连表再group则需做对除数排0处理。
--and (cast agreenum as decimal(10,2))/agreenum * 100 between 60 and 100随手敲的,难免手误
(select questionid from t_vote
group by questionid
having sum(convert(int,style))/count(*)>=0.6 and sum(convert(int,style))/count(*)<=1) a,
t_tuestion b
where a.questionid=b.id
(select questionid from t_vote
group by questionid
having sum(convert(numeric(10,2),style))/count(*)>=0.6 and sum(convert(numeric(10,2),style))/count(*)<=1) a,
t_tuestion b
where a.questionid=b.id用小数,才能得出0.6与1.0
from
(
select q.Title, sum(cast(q.style as float))/(count(q.style) over()) as ratio
from T_Qestion q
JOIN T_Vote t ON q.id = t.Questionid
)
where ratio > 0.6
from
(
select q.Title, sum(cast(q.style as float))/(count(q.style) over()) as ratio
from T_Qestion q
JOIN T_Vote t ON q.id = t.Questionid
)x
where ratio >= 0.6