Code
?--?年級排名和班級排名
declare?@sql?varchar(4000),@nclassid?int,@nexamid?int??select?@sql=''?select?@nclassid=38?select?@nexamid=19
select?@sql=@sql+'max(case?scoursename?when?'''+scoursename+'''?then?descore?else?0?end)?'+scoursename+','
from?(select?distinct?scoursename?from?t_card_course?where?id?in?(select?ncourseid?from?t_card_examcou?where?nclassid?=+@nclassid?and?nexamid=+@nexamid?))?a??
exec('
select?nclassid,?name??姓名,'+?@sql+?'sum(descore)?總分
into?#temp?
from?(?
?select?b.nclassid,b.name,a.descore,c.scoursename?from?t_card_score?a,t_stu?b,t_card_course?c,t_card_examcou?d?
?where?a.nstuid=b.id?and?a.ncouexamid?in
?(select?id?from?t_card_examcou?where?nclassid?in?(select?id?from?t_card_class?where?ngradeid?=(select?ngradeid?from?t_card_class?where?id='+@nclassid+'?and?nschoolid=(select?nschoolid?from?t_card_class?where?id='+@nclassid+')))??and?nexamid='+@nexamid+'?)?
and?a.ncouexamid=d.id?and?d.ncourseid=c.id?group?by?b.nclassid,b.name,a.descore,c.scoursename?)as?a
?group?by?name,nclassid?

select?*,班級名次=(select?count(總分)?from?#temp?where?總分>y.總分?and?nclassid=y.nclassid)+1?into?#tempclass?from?#temp?y???
select?*,年級名次?=(select?count(總分)?from?#tempclass?where?總分>x.總分)+1?into?#tempok?from?#tempclass?x?where?nclassid?='+@nclassid+'?order?by?班級名次

alter?table?#tempok?drop?column?nclassid
select?*?from?#tempok
')

? ? 本文轉自wengyuli 51CTO博客，原文鏈接：http://blog.51cto.com/wengyuli/588648，如需轉載請自行聯系原作者