《通过数据库存储过程动态表头制作》由会员分享，可在线阅读，更多相关《通过数据库存储过程动态表头制作（4页珍藏版）》请在金锄头文库上搜索。
1、这段代码在eTangent_AMS中.ALTER PROCEDURE dbo.ExpertGiveGraded PrizeGroupID int ,AchievementForm intASBEGINdeclare sql1 nvarchar(500)declare sql2 nvarchar(500)declare sql3 nvarchar(max)declare sql4 nvarchar(500)declare sql5 nvarchar(max)declare sql6 nvarchar(500)declare sql7 nvarchar(500)declare PartakeExp
2、ertNum int Declare GetprizeNum intset sql1=set sql2=set sql3=set sql4=set sql5=set sql6=set sql7=set PartakeExpertNum=0select GetprizeNum=count(Expert.Name) from Achievement join Participants on Participants.AchievementID=Achievement.AchievementIDjoin mark on mark.AchievementID=Achievement.Achieveme
3、ntIDjoin Expert on Expert.ExpertID=mark.ExpertIDwhere Mark.PrizeRankRecommended=-1 and PrizeGroupID=PrizeGroupIDset sql1=select Achievement.FirstEvaluationNo as 初评编号,Achievement.HostName as 申报人姓名,WorkUnitName as 工作单位,TitleName as 职称,DutyName as 职务,Achievement.AchievementName as 成果名称,(Achievement.Ser
4、ialNumber +Achievement.Publisher +Achievement.PublicationDate) as 成果发表期刊、出版单位名称以及成果发表、出版时间select PartakeExpertNum=PartakeExpertNum+1,sql2=sql2+,CONVERT(decimal(18,2), Score.+CAST(ExpertID AS nvarchar(256)+评分) AS + Name + 评分,sql4=sql4+,sum(case ExpertID when +cast(ExpertID as nvarchar(5)+ then TotalS
5、core end) as +CAST(ExpertID AS nvarchar(256)+评分from Expertwhere ExpertID in (select ExpertID from PrizeGroupExpert where PrizeGroupID=PrizeGroupID and IsPartake=1)SELECT sql7=sql7+,isnull(sum(case PrizeRankRecommended when +cast(PropertyValue as nvarchar(50)+ then countofprise end),0) as 推荐+cast (Pr
6、opertyMeaning as nvarchar(50) +个数,sql6=sql6+, newprisecount.推荐 +PropertyMeaning+个数 FROM (SELECT DISTINCT PropertyValue,PropertyMeaningfrom mark inner join PropertyMapping on mark.PrizeRankRecommended=PropertyMapping.PropertyValuewhere achievementid in (select AchievementID from Achievement where Pri
7、zeGroupID=PrizeGroupID)and PropertyMapping.PropertyName=PrizeRankRecommended and PropertyMapping.PropertyValue!=-1) AS Aset sql3=,Convert(decimal(18,2), Achievement.AverageScore) as 平均分,isnull(prisecount.countofall,0)-isnull(noprisecount.countofno,0) as 推荐获奖个数+sql6 +from (select achievement.achievem
8、entid,achievement.Name as AchievementName,achievement.CompleteUnit,Achievement.SerialNumber,Achievement.Publisher,Achievement.PublicationDate,achievement.AverageScore,achievement.AchievementForm,Achievement.FirstEvaluationNo,Host.Name as HostName,Ti.Name as TitleName,ExpertContacts.Duty as DutyName,
9、vw.Name as WorkUnitNamefrom achievement left join (select achievementid,Name as achievementName,ParticipantID,Participants.Name,Title,CardIDfrom Participants where ParticipantType=0) as Host on achievement.achievementID=Host.achievementid left join ViewTitles as Ti on Ti.ID = Host.Titlejoin ExpertCo
10、ntacts on ExpertContacts.CardID=Host.CardID left join vw_AgencyForSummary as vw on vw.ScienceAgencyID = ExpertContacts.WorkUnitwhere PrizeGroupID=+cast(PrizeGroupID as nvarchar(5)+) as Achievementleft join(select achievementidset sql5=,(case count(ExpertID) when +cast(PartakeExpertNum as nvarchar(5)
11、+ then ROUND(sum(TotalScore)/count(ExpertID),2)end) as 平均分from Markwhere achievementid in(select AchievementID from Achievement where PrizeGroupID=+cast(PrizeGroupID as nvarchar(5)+)group by achievementid)as Score on Achievement.AchievementId=Score.achievementid left join (select achievementid,count
12、(PrizeRankRecommended) as countofall from Mark where achievementid in(select AchievementID from Achievement where PrizeGroupID=+cast(PrizeGroupID as nvarchar(5)+)group by achievementid) as prisecounton Score.achievementid=prisecount.achievementidleft join (select achievementid,count(PrizeRankRecomme
13、nded) as countofno from Mark where achievementid in(select AchievementID from Achievement where PrizeGroupID=+cast(PrizeGroupID as nvarchar(5)+) and (PrizeRankRecommended =-1 or PrizeRankRecommended is null)group by achievementid) as noprisecounton noprisecount.achievementid=prisecount.achievementid
14、 left join (select achievementid +sql7+ from (select achievementid,count(PrizeRankRecommended) as countofprise,PrizeRankRecommendedfrom Markwhere achievementid in(select AchievementID from Achievement where PrizeGroupID=+cast(PrizeGroupID as nvarchar(5)+)group by achievementid,PrizeRankRecommended)
15、as prisrankgroup by achievementid) as newprisecount on newprisecount.achievementid=Score.achievementid where +cast(AchievementForm as nvarchar(5)+=-1 or Achievement.AchievementForm=+cast(AchievementForm as nvarchar(5)+order by achievement.AchievementForm,Achievement.FirstEvaluationNoexec(sql1+sql2+sql3+sql4+sql5)print (sql1+sql2+sql3+sql4+sql5)END
