reporting service做报表中写的sql语句如下:
="Select a.studentname As 姓名,
k.CodeName As 性别,
b.signupcode As 准考证号,
c.matriculatedatetime As 录取时间,
Case b.status When 1 Then '初始' when 10 then '已报志愿' when 20 then '预录取' when 30 then '录取' End As 录取状态,
Case b.isareastudent When 0 Then '否' Else '是' End As 是否地段生,
g.sumscore As 总分,
g.combinedscore As 学科组合分,
[语文]=Max(Case When f.subjectID=1 Then f.examresult End),
[英语]=Max(Case When f.subjectID=4 Then f.examresult End),
[数学]=Max(Case When f.subjectID=10 Then f.examresult End),
[政史]=Max(Case When f.subjectID=20 Then f.examresult End),
[体育]=Max(Case When f.subjectID=23 Then f.examresult End),
[理化]=Max(Case When f.subjectID=24 Then f.examresult End),
[地生]=Max(Case When f.subjectID=25 Then f.examresult End),
e.filltime As 报名时间,
h.PhaseName As 报名时段,
n.WinName As 报名地点,
d.OrgId as 录取学校编号,
d.SchoolName As 录取学校,
case e.signuptypecode when 1 then '统招' when 2 then '指导' when 3 then '统招且愿择校' end as 报考类别,
p.TypeName As 录取方式,
r.typename as 录取类型 ,
s.schoolname as 毕业学校 "&"From student a
inner join highschoolstudent b on a.studentid=b.studentid
left join StudentMatriculate c on a.studentid=c.studentid
left join highschool d on c.orgid=d.orgid
left join highschool s on b.orgid=s.orgid
left join studentwill e on e.studentid=a.studentid
inner Join StudentExamResult f On f.studentid=a.studentid
inner Join Studentscore g On a.studentid=g.studentid
inner Join C_Phase h On h.PhaseId=e.PhaseId
left Join C_SignUpType i On i.SignUpTypeCode=e.SignUpTypeCode
left Join C_SexType k On a.sextypecode=k.sextypecode
left Join Account m On m.AccountId=e.AccountId
left Join SignupWindows n On n.WinNo=m.WinNo
left Join C_RecruitType p On c.RecruitTypeCode=p.RecruitTypeCode
left Join C_StudentType q On b.StudentTypeCode=q.StudentTypeCode
left Join C_Recruitclass r On p.RecruitClassCode=r.RecruitClassCode
left Join StudentGeneralEvaluate o On a.studentid=o.studentid "&" WHERE a.studentid <> '1' "& Iif(Parameters!OrgId.Value = 0, "", "AND e.orgid = @OrgId ") &
Iif(Parameters!MOrgId.Value = 0, "", "AND c.orgid = @MOrgId ") &
Iif(Parameters!WillStatus.Value = 0, "", "AND e.status = @WillStatus ") &
Iif(Parameters!MStatus.Value = 0, "", "AND b.status = @MStatus ") &
Iif(Parameters!PhaseId.Value = 0, "", "AND e.PhaseId = @PhaseId ") &
Iif(Parameters!SignUpTypeCode.Value = 0, "", "AND e.SignUpTypeCode = @SignUpTypeCode ") &
Iif(Parameters!WinNo.Value = 0, "", "AND m.WinNo = @WinNo ") &
Iif(Parameters!RecruitTypeCode.Value = 0, "", "AND c.RecruitTypeCode = @RecruitTypeCode ") &
Iif(Parameters!Signupcode.Value = 0, "", "AND b.Signupcode = @Signupcode ") &
Iif(Parameters!StudentType.Value = 0, "", "AND b.StudentTypeCode = @StudentType ") &
Iif(Parameters!GraduateSchoolId.Value = 0, "", "AND b.OrgId = @GraduateSchoolId ") &
Iif(Parameters!SexTypeCode.Value = 0, "", "AND a.SexTypeCode = @SexTypeCode ") &
Iif(Parameters!IsAreaStudent.Value = 0, "", "AND b.IsAreaStudent = @IsAreaStudent ") &
Iif(Parameters!MaxSumScore.Value = 0, "", "AND g.SumScore >= @MaxSumScore ") &
Iif(Parameters!MinSumScore.Value = 0, "", "AND g.SumScore <= @MinSumScore ") &
Iif(Parameters!IsLodging.Value = 0, "", "AND f.IsLodging = @IsLodging ") &
" group by "&
" a.studentname,
k.CodeName,
b.signupcode,
b.status,
c.matriculatedatetime,
c.status,
b.isareastudent,
g.sumscore ,
g.combinedscore ,
e.filltime,
e.signuptypecode,
h.PhaseName,
i.CodeName ,
n.WinName ,
d.OrgId,
d.SchoolName ,
i.CodeName ,
p.TypeName ,
r.typename ,
s.schoolname,
o.sumscore,
o.combinedscore "&
"Order By o.SumScore desc,o.CombinedScore desc"
为何在配置好参数后保存时,vs为什么会变得很慢?要几个钟头才能保存成功?
="Select a.studentname As 姓名,
k.CodeName As 性别,
b.signupcode As 准考证号,
c.matriculatedatetime As 录取时间,
Case b.status When 1 Then '初始' when 10 then '已报志愿' when 20 then '预录取' when 30 then '录取' End As 录取状态,
Case b.isareastudent When 0 Then '否' Else '是' End As 是否地段生,
g.sumscore As 总分,
g.combinedscore As 学科组合分,
[语文]=Max(Case When f.subjectID=1 Then f.examresult End),
[英语]=Max(Case When f.subjectID=4 Then f.examresult End),
[数学]=Max(Case When f.subjectID=10 Then f.examresult End),
[政史]=Max(Case When f.subjectID=20 Then f.examresult End),
[体育]=Max(Case When f.subjectID=23 Then f.examresult End),
[理化]=Max(Case When f.subjectID=24 Then f.examresult End),
[地生]=Max(Case When f.subjectID=25 Then f.examresult End),
e.filltime As 报名时间,
h.PhaseName As 报名时段,
n.WinName As 报名地点,
d.OrgId as 录取学校编号,
d.SchoolName As 录取学校,
case e.signuptypecode when 1 then '统招' when 2 then '指导' when 3 then '统招且愿择校' end as 报考类别,
p.TypeName As 录取方式,
r.typename as 录取类型 ,
s.schoolname as 毕业学校 "&"From student a
inner join highschoolstudent b on a.studentid=b.studentid
left join StudentMatriculate c on a.studentid=c.studentid
left join highschool d on c.orgid=d.orgid
left join highschool s on b.orgid=s.orgid
left join studentwill e on e.studentid=a.studentid
inner Join StudentExamResult f On f.studentid=a.studentid
inner Join Studentscore g On a.studentid=g.studentid
inner Join C_Phase h On h.PhaseId=e.PhaseId
left Join C_SignUpType i On i.SignUpTypeCode=e.SignUpTypeCode
left Join C_SexType k On a.sextypecode=k.sextypecode
left Join Account m On m.AccountId=e.AccountId
left Join SignupWindows n On n.WinNo=m.WinNo
left Join C_RecruitType p On c.RecruitTypeCode=p.RecruitTypeCode
left Join C_StudentType q On b.StudentTypeCode=q.StudentTypeCode
left Join C_Recruitclass r On p.RecruitClassCode=r.RecruitClassCode
left Join StudentGeneralEvaluate o On a.studentid=o.studentid "&" WHERE a.studentid <> '1' "& Iif(Parameters!OrgId.Value = 0, "", "AND e.orgid = @OrgId ") &
Iif(Parameters!MOrgId.Value = 0, "", "AND c.orgid = @MOrgId ") &
Iif(Parameters!WillStatus.Value = 0, "", "AND e.status = @WillStatus ") &
Iif(Parameters!MStatus.Value = 0, "", "AND b.status = @MStatus ") &
Iif(Parameters!PhaseId.Value = 0, "", "AND e.PhaseId = @PhaseId ") &
Iif(Parameters!SignUpTypeCode.Value = 0, "", "AND e.SignUpTypeCode = @SignUpTypeCode ") &
Iif(Parameters!WinNo.Value = 0, "", "AND m.WinNo = @WinNo ") &
Iif(Parameters!RecruitTypeCode.Value = 0, "", "AND c.RecruitTypeCode = @RecruitTypeCode ") &
Iif(Parameters!Signupcode.Value = 0, "", "AND b.Signupcode = @Signupcode ") &
Iif(Parameters!StudentType.Value = 0, "", "AND b.StudentTypeCode = @StudentType ") &
Iif(Parameters!GraduateSchoolId.Value = 0, "", "AND b.OrgId = @GraduateSchoolId ") &
Iif(Parameters!SexTypeCode.Value = 0, "", "AND a.SexTypeCode = @SexTypeCode ") &
Iif(Parameters!IsAreaStudent.Value = 0, "", "AND b.IsAreaStudent = @IsAreaStudent ") &
Iif(Parameters!MaxSumScore.Value = 0, "", "AND g.SumScore >= @MaxSumScore ") &
Iif(Parameters!MinSumScore.Value = 0, "", "AND g.SumScore <= @MinSumScore ") &
Iif(Parameters!IsLodging.Value = 0, "", "AND f.IsLodging = @IsLodging ") &
" group by "&
" a.studentname,
k.CodeName,
b.signupcode,
b.status,
c.matriculatedatetime,
c.status,
b.isareastudent,
g.sumscore ,
g.combinedscore ,
e.filltime,
e.signuptypecode,
h.PhaseName,
i.CodeName ,
n.WinName ,
d.OrgId,
d.SchoolName ,
i.CodeName ,
p.TypeName ,
r.typename ,
s.schoolname,
o.sumscore,
o.combinedscore "&
"Order By o.SumScore desc,o.CombinedScore desc"
为何在配置好参数后保存时,vs为什么会变得很慢?要几个钟头才能保存成功?
用reporting services制作报表的时候,这个做为"数据集"的"查询字符串",
我要保存这个报表才能预览,但是一点下"确定"按钮,整个vs2005都动不了,大概5、6个小时后才保存成功。
我是在4CPU,4G内存的服务器上做的报表