1、首先要把这个层的runat="server"
This.div1.Style.Add(“display”,”none);
2、要使用asp:DropDownList的事件时要设置
AutoPostBack="True"
3、查外键的值
业务层:
/// <summary>
/// 查询邮件
/// </summary>
/// <param name="curPage">当前页</param>
/// <param name="countOfPage">一页显示的数量</param>
/// <param name="condition">条件</param>
/// <returns>邮件</returns>
public static List<tbl_Email> GetEmailByPageAndCondition(int curPage, int countOfPage, string condition)
{
List<tbl_Email> email= EmailService.GetEmailByPageAndCondition(curPage, countOfPage, condition);
foreach (tbl_Email item in email)
{
item.tbl_EmailType = EmailTypeService.GetEmailTypeById(Convert.ToInt32( item.TypeId));
}
return email;
}
数据层:
/// <summary>
/// 根据类型ID查询邮件类型
/// 2009-10-14
/// </summary>
/// <param name="id">邮件ID</param>
/// <returns>邮件类型</returns>
public static tbl_EmailType GetEmailTypeById(int id) {
SchoolContext context = DbHelper.CreateContext();
tbl_EmailType type = (from emailType in context.tbl_EmailType
where emailType.Id == id
select emailType).SingleOrDefault();
return type;
}
4、 如果sql语句是子查询同时又要排序的话一定要在子查询语句里和子查询语句外都写ORDER BY AccessTime desc:
SELECT TOP (1) * FROM tbl_Access
WHERE Id NOT IN
(SELECT TOP (1) Id FROM tbl_Access
WHERE 1=1 And AccessTime BETWEEN '2009-10-04' AND '2009-10-31' ORDER BY AccessTime desc)
AND 1=1 And AccessTime BETWEEN '2009-10-04' AND '2009-10-31'
ORDER BY AccessTime desc
/// <summary>
/// 查询来访记录
/// </summary>
/// <param name="curPage">当前页</param>
/// <param name="countOfPage">一页显示的数量</param>
/// <param name="condition">条件</param>
/// <returns>来访记录</returns>
public static List<tbl_Access> GetAccessByPageAndCondition(int curPage, int countOfPage, string condition)
{
SchoolContext context = DbHelper.CreateContext();
string sql =
@"SELECT TOP ({1}) * FROM tbl_Access WHERE Id NOT IN
(SELECT TOP ({2}) Id FROM tbl_Access WHERE 1=1 {0} ORDER BY AccessTime desc)
AND 1=1 {0} ORDER BY AccessTime desc";
sql = string.Format(sql, condition, countOfPage, (curPage - 1) * countOfPage);
return context.ExecuteQuery<tbl_Access>(sql, new object[] { curPage, countOfPage, condition }).ToList();
}
在GridView中给数据添加序号
<%var count = aspClass.PageSize; iiindex = (aspClass.CurrentPageIndex - 1) * count; %>
<asp:GridView ID="gvClassInfo" runat="server" AutoGenerateColumns="False" DataKeyNames="Id"
Style="margin-top: 0px" Width="590px" OnRowDataBound="gvClassInfo_RowDataBound">
<Columns>
<asp:TemplateField Visible="True">
<HeaderTemplate>
序号
</HeaderTemplate>
<ItemTemplate>
<script type="text/C#" runat="server">
protected int iiindex = 0;
</script>
<%=++iiindex %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField Visible="False">
<HeaderTemplate>
<asp:CheckBox ID="chbAll" runat="server" Text="全选" onclick="GetAllCheckBox(this)" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chbSelect" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="班级" DataField="Name" />
<asp:TemplateField HeaderText="年级">
<ItemTemplate>
<asp:Label ID="lblGrade" runat="server" Text='<%# Eval("tbl_Grade.Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="入学年份" DataField="GradeYear" />
<asp:BoundField HeaderText="毕业时间" DataField="GraduateYear" />
</Columns>
</asp:GridView>
ClientScript.RegisterStartupScript(GetType(), "提示", "alert('批量删除失败!')", true);
btnDelAll.OnClientClick = "return window.confirm('警告!您确定要删除选中项吗?//r//n如果您将班级信息删除可能会把所有属于这个班级的学生信息删除,请问您确定要执行删除选中项吗?');";
这是获取按钮的ASCII字符码
<div id="div" style="width: 800px; overflow: auto;"> | |
| </div> |
| <input type="text" id="txt" /> |
|
|
| <script> |
| txt.onkeydown = function(e){ |
| e = window.event || e; |
| var div = document.getElementById("div"); |
|
|
| div.innerHTML += e.keyCode + ","; |
| } |
| </script> |
|
|
|
|
select distinct(
select count(Id) from dbo.tbl_EnrollClue where ContactTime like '%2008%') 二零零八,(
select count(Id) from dbo.tbl_EnrollClue where ContactTime like '%2009%') 二零零九
from dbo.tbl_EnrollClue
create proc usp_EnrollClueCount
@begincontactTime datetime
@endcontactTime
as
substring(@begincontactTime,1,4)
SELECT year(convert(datetime, '2006-1-1'))
for(){}
select count(Id) from dbo.tbl_EnrollClue where enrollpersonId=2 and ContactTime like '%'+substring(@contactTime,1,4)+'%'
select distinct(
select count(Id) from dbo.tbl_EnrollClue where ContactTime like '%2008%') 二零零八,(
select count(Id) from dbo.tbl_EnrollClue where ContactTime like '%2009%') 二零零九
from dbo.tbl_EnrollClue
create proc usp_GetEnrollCountByEnrollYear
as
select distinct year(convert(datetime,ContactTime)) EnrollYear,
(select count(*) from dbo.tbl_EnrollClue where IntentionState=3) EnrollCount
from dbo.tbl_EnrollClue
go
验证多个控件不能为空!
<script type="text/javascript">
function check(){
var elArr = [
{el:'<%=txtBeginTime.ClientID %>', validRegex:null, regexMsg:null, requredMsg:"时间不能为空!"},
{el:'<%=txtEndTime.ClientID %>', validRegex:null, regexMsg:null, requredMsg:"时间不能为空!"}
];
// return checkForm(elArr);
return true;
}
function validateFormElByTwo(elOrId1, requredMsg1,elOrId2, requredMsg2){
if(!validateFormEl(elOrId1,null,null,requredMsg1)){return false;}
if(!validateFormEl(elOrId2,null,null,requredMsg2)){
return false;
}
return true;
}
</script>
URL传多个值
PostBackUrl='<%# "~/EnrollManager/AddEnrollClue.aspx?Id="+Eval("Id") + "&EnrollPersonId=" + Eval("EnrollPersonId")%>'
返回上一页
OnClientClick="window.history.go(-1);return false;"
//去除集合中的重复项
for (int i = 0; i < subjectList.Count; i++)
{
//是否为真
bool isTrue = true;
for (int j = 0; j < subjectList.Count; j++)
{
if (subjectList[i].Name == subjectList[j].Name)
{
if (isTrue)
{
isTrue = false;
}
else
{
subjectList.Remove(subjectList[j]);
}
}
}
}
Sql中拆分字符串,得到字符的方法
1
create function [dbo].[split]
(
@SourceSql varchar(8000),
@StrSeprate varchar(10)
)
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql = rtrim(ltrim(@SourceSql))
set @i = charindex(@StrSeprate,@SourceSql)
while @i >= 1
begin
if len(left(@SourceSql,@i-1))>0
begin
insert @temp values(left(@SourceSql,@i-1))
end
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql <> ''
insert @temp values(@SourceSql)
return
end
2
ALTER PROCEDURE dbo.usp_GetCharByString
@TestInfoId int,
@TestClassId int,
@str varchar(4000)
AS
DECLARE @sql1 nvarchar(4000) --拼接后的第一个Sql语句
declare @location int
declare @start int
declare @length int
--去掉前后空格
set @str=ltrim(rtrim(@str))
--分隔符第一次出现的位置
set @location=charindex(',',@str)
--设置分隔后的字符串长度为
set @length=1
while @location<>0
begin
print @start
print substring(@str,@location,0+@start)
set @start=@location+1
set @location=charindex(',',@str,@start)
set @length=@length+1
end
return @length
动态显示某班的考试科目成绩的存储过程(科目为动态)--考试三科以上用此方法
ALTER PROCEDURE [dbo].[usp_ShowTestScore]
@TestInfoId int,
@TestClassId int,
@subjectStr varchar(4000)
as
DECLARE @sql1 nvarchar(4000), --拼接后的第一个Sql语句
@sql2 nvarchar(4000), --拼接后的第二个Sql语句
@sql3 nvarchar(4000), --拼接后的第三个Sql语句
@fieldSql nvarchar(4000), --查询列的Sql语句
@subFieldSql nvarchar(4000), --每一个子虚拟表的Select字段
@joinOnSql nvarchar(4000) --fullJoin on后面的条件
SET @fieldSql = ''
SET @subFieldSql = ''
SET @sql1=''
SET @sql2=''
SET @sql3=''
declare @i int
declare @isFalg int
declare @SubName varchar(30)
declare @SubName1 varchar(30)
set @isFalg=0
set @subjectStr = rtrim(ltrim(@subjectStr))
set @i = charindex(',',@subjectStr)
while @i >= 1
begin
if len(left(@subjectStr,@i-1))>0
begin
SET @fieldSql=@fieldSql+ ','+left(@subjectStr,@i-1)
if(@isFalg<1)
begin
set @isFalg=1
SET @sql1=@sql1+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + left(@subjectStr,@i-1)+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ left(@subjectStr,@i-1)+''' and TestClassId='+convert(nvarchar(20),@TestClassId)+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+' ) as '+ left(@subjectStr,@i-1)+' INNER JOIN'
SET @SubName1=left(@subjectStr,@i-1)
end
ELSE
begin
SET @sql2=@sql2+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + left(@subjectStr,@i-1)+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ left(@subjectStr,@i-1)+''' and TestClassId='+convert(nvarchar(20),@TestClassId)+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+' ) as '+ left(@subjectStr,@i-1)+' on '+@SubName1+'.StudentId='+left(@subjectStr,@i-1)+'.StudentId inner join'
SET @SubName=left(@subjectStr,@i-1)
--print left(@subjectStr,@i-1)
end
end
set @subjectStr=substring(@subjectStr,@i+1,len(@subjectStr)-@i)
set @i=charindex(',',@subjectStr)
end
if @subjectStr <> ''
begin
SET @fieldSql=@fieldSql+ ','+@subjectStr
SET @fieldSql = RIGHT(@fieldSql, Len(@fieldSql) - 1)
SET @sql3=@sql3+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + @subjectStr+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ @subjectStr+''' and TestClassId='+convert(nvarchar(20),@TestClassId)+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+'
) as '+ @subjectStr+' on '+@SubName+'.StudentId='+@subjectStr+'.StudentId'
end
print 'select '+@subjectStr+'.StudentName as 学生名称,'+@subjectStr+'.StudentId as 学号,'+@fieldSql+' from '+@sql1+@sql2+@sql3+' where '+@subjectStr+'.TestClassId='+convert(nvarchar(20),@TestClassId)+' and '+@subjectStr+'.TestInfoId='+convert(nvarchar(20),@TestInfoId)
// //
存储过程生成的T-SQL语句
select 政治.StudentName as 学生名称,政治.StudentId as 学号,语文,数学,英语,历史,政治from (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore 语文from dbo.v_TestScore_Student_Class_Subject
where subjectName='语文' and TestClassId=81 and TestInfoId=10033 ) as 语文INNER JOIN (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore 数学from dbo.v_TestScore_Student_Class_Subject
where subjectName='数学' and TestClassId=81 and TestInfoId=10033 ) as 数学on 语文.StudentId=数学.StudentId inner join (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore 英语from dbo.v_TestScore_Student_Class_Subject
where subjectName='英语' and TestClassId=81 and TestInfoId=10033 ) as 英语on 语文.StudentId=英语.StudentId inner join (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore 历史from dbo.v_TestScore_Student_Class_Subject
where subjectName='历史' and TestClassId=81 and TestInfoId=10033 ) as 历史on 语文.StudentId=历史.StudentId inner join (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore 政治from dbo.v_TestScore_Student_Class_Subject
where subjectName='政治' and TestClassId=81 and TestInfoId=10033
) as 政治on 历史.StudentId=政治.StudentId where 政治.TestClassId=81 and 政治.TestInfoId=10033
动态显示某班的考试科目成绩的存储过程(科目为动态)--考试二科用此方法
ALTER PROCEDURE dbo.usp_ShowTestScore2
@TestInfoId int,
@TestClassId int,
@subjectStr varchar(4000)
as
DECLARE @sql1 nvarchar(4000), --拼接后的第一个Sql语句
@sql2 nvarchar(4000), --拼接后的第二个Sql语句
@fieldSql nvarchar(4000) --查询列的Sql语句
SET @fieldSql = ''
SET @sql1=''
SET @sql2=''
declare @i int
declare @isFalg int
declare @SubName varchar(30)
declare @SubName1 varchar(30)
set @isFalg=0
set @subjectStr = rtrim(ltrim(@subjectStr))
set @i = charindex(',',@subjectStr)
while @i >= 1
begin
if len(left(@subjectStr,@i-1))>0
begin
SET @fieldSql=@fieldSql+ ','+left(@subjectStr,@i-1)
if(@isFalg<1)
begin
set @isFalg=1
SET @sql1=@sql1+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + left(@subjectStr,@i-1)+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ left(@subjectStr,@i-1)+''' and TestClassId='+isNull(convert(nvarchar(20),@TestClassId),+'TestClassId')+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+' ) as '+ left(@subjectStr,@i-1)+' INNER JOIN'
SET @SubName=left(@subjectStr,@i-1)
end
end
set @subjectStr=substring(@subjectStr,@i+1,len(@subjectStr)-@i)
set @i=charindex(',',@subjectStr)
end
if @subjectStr <> ''
begin
SET @fieldSql=@fieldSql+ ','+@subjectStr
SET @fieldSql = RIGHT(@fieldSql, Len(@fieldSql) - 1)
SET @sql2=@sql2+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + @subjectStr+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ @subjectStr+''' and TestClassId='+isNull(convert(nvarchar(20),@TestClassId),+'TestClassId')+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+'
) as '+ @subjectStr+' on '+@SubName+'.StudentId='+@subjectStr+'.StudentId'
end
print 'select '+@subjectStr+'.StudentName as 学生名称,'+@subjectStr+'.StudentId as 学号,'+@fieldSql+' from '+@sql1+@sql2+' where '+@subjectStr+'.TestClassId='+convert(nvarchar(20),@TestClassId)+' and '+@subjectStr+'.TestInfoId='+convert(nvarchar(20),@TestInfoId)
动态显示某班的考试科目成绩的存储过程(科目为动态)--考试一科用此方法
ALTER PROCEDURE dbo.usp_ShowTestScore1
@TestInfoId int,
@TestClassId int,
@subjectStr varchar(4000)
as
DECLARE @sql1 nvarchar(4000) --拼接后的第一个Sql语句
SET @sql1=''
SET @sql1=@sql1+ 'select StudentName as 学生名称,StudentId as 学号,TestScore as '+@subjectStr+' from dbo.v_TestScore_Student_Class_Subject where TestClassId='+convert(nvarchar(20),@TestClassId)+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+' and subjectname='''+@subjectStr+''''
print @sql1
动态显示某班的考试科目成绩的存储过程(科目为动态)
班级为NULL时,查询这次考试的所有人的成绩
反之,则查询该班的学生的考试成绩
--可查询考试三科以上的全部学生成绩和班级学生成绩
ALTER proc [dbo].[usp_ShowTestScoreByTestClassId]
@TestInfoId int,
@TestClassId int,
@subjectStr varchar(4000)
as
DECLARE @sql1 nvarchar(4000), --拼接后的第一个Sql语句
@sql2 nvarchar(4000), --拼接后的第二个Sql语句
@sql3 nvarchar(4000), --拼接后的第三个Sql语句
@fieldSql nvarchar(4000), --查询列的Sql语句
@subFieldSql nvarchar(4000), --每一个子虚拟表的Select字段
@joinOnSql nvarchar(4000) --fullJoin on后面的条件
SET @fieldSql = ''
SET @subFieldSql = ''
SET @sql1=''
SET @sql2=''
SET @sql3=''
declare @i int
declare @isFalg int
declare @SubName varchar(30)
declare @SubName1 varchar(30)
set @isFalg=0
set @subjectStr = rtrim(ltrim(@subjectStr))
set @i = charindex(',',@subjectStr)
while @i >= 1
begin
if len(left(@subjectStr,@i-1))>0
begin
SET @fieldSql=@fieldSql+ ','+left(@subjectStr,@i-1)
if(@isFalg<1)
begin
set @isFalg=1
SET @sql1=@sql1+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + left(@subjectStr,@i-1)+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ left(@subjectStr,@i-1)+''' and TestClassId='+isNull(convert(nvarchar(20),@TestClassId),+'TestClassId')+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+' ) as '+ left(@subjectStr,@i-1)+' INNER JOIN'
SET @SubName1=left(@subjectStr,@i-1)
end
ELSE
begin
SET @sql2=@sql2+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + left(@subjectStr,@i-1)+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ left(@subjectStr,@i-1)+''' and TestClassId='+isNull(convert(nvarchar(20),@TestClassId),+'TestClassId')+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+' ) as '+ left(@subjectStr,@i-1)+' on '+@SubName1+'.StudentId='+left(@subjectStr,@i-1)+'.StudentId inner join'
SET @SubName=left(@subjectStr,@i-1)
--print left(@subjectStr,@i-1)
end
end
set @subjectStr=substring(@subjectStr,@i+1,len(@subjectStr)-@i)
set @i=charindex(',',@subjectStr)
end
if @subjectStr <> ''
begin
SET @fieldSql=@fieldSql+ ','+@subjectStr
SET @fieldSql = RIGHT(@fieldSql, Len(@fieldSql) - 1)
SET @sql3=@sql3+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + @subjectStr+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ @subjectStr+''' and TestClassId='+isNull(convert(nvarchar(20),@TestClassId),+'TestClassId')+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+'
) as '+ @subjectStr+' on '+@SubName+'.StudentId='+@subjectStr+'.StudentId'
end
print 'select '+@subjectStr+'.StudentName as 学生名称,'+@subjectStr+'.StudentId as 学号,'+@fieldSql+' from '+@sql1+@sql2+@sql3+' where '+@subjectStr+'.TestClassId='+isNull(convert(nvarchar(20),@TestClassId),@subjectStr+'.TestClassId')+' and '+@subjectStr+'.TestInfoId='+convert(nvarchar(20),@TestInfoId)
求行的总分
SELECT 学生名称,学号,语文,数学,品德,(语文+数学+品德) 总分FROM
(SELECT * FROM @temp) as t
/
统计科目总分和平均分的存储过程
/
ALTER proc [dbo].[usp_ShowTestScoreByTestClassId]
@TestInfoId int,
@TestClassId int,
@subjectStr varchar(4000)
as
DECLARE @sql1 nvarchar(4000), --拼接后的第一个Sql语句
@sql2 nvarchar(4000), --拼接后的第二个Sql语句
@sql3 nvarchar(4000), --拼接后的第三个Sql语句
@fieldSql nvarchar(4000), --查询列的Sql语句
@fieldSql1 nvarchar(4000),--虚拟表列的Sql语句
@fieldSql2 nvarchar(4000),--虚拟表统计的Sql语句
@fieldSql3 nvarchar(4000),--虚拟表统计和的Sql语句
@subFieldSql nvarchar(4000), --每一个子虚拟表的Select字段
@joinOnSql nvarchar(4000) --fullJoin on后面的条件
SET @fieldSql = ''
SET @subFieldSql = ''
SET @sql1=''
SET @sql2=''
SET @sql3=''
SET @fieldSql1=''
SET @fieldSql2=''
SET @fieldSql3=''
declare @i int
declare @isFalg int
declare @SubName varchar(30)
declare @SubName1 varchar(30)
set @isFalg=0
set @subjectStr = rtrim(ltrim(@subjectStr))
set @i = charindex(',',@subjectStr)
while @i >= 1
begin
if len(left(@subjectStr,@i-1))>0
begin
SET @fieldSql=@fieldSql+ ','+left(@subjectStr,@i-1)
SET @fieldSql1=@fieldSql1+','+left(@subjectStr,@i-1)+' int'
SET @fieldSql2=@fieldSql2+','+'Sum('+left(@subjectStr,@i-1)+') as '+left(@subjectStr,@i-1)+'总分'
SET @fieldSql3=@fieldSql3+left(@subjectStr,@i-1)+'+'
if(@isFalg<1)
begin
set @isFalg=1
SET @sql1=@sql1+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + left(@subjectStr,@i-1)+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ left(@subjectStr,@i-1)+''' and TestClassId='+isNull(convert(nvarchar(20),@TestClassId),+'TestClassId')+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+' ) as '+ left(@subjectStr,@i-1)+' INNER JOIN'
SET @SubName1=left(@subjectStr,@i-1)
end
ELSE
begin
SET @sql2=@sql2+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + left(@subjectStr,@i-1)+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ left(@subjectStr,@i-1)+''' and TestClassId='+isNull(convert(nvarchar(20),@TestClassId),+'TestClassId')+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+' ) as '+ left(@subjectStr,@i-1)+' on '+@SubName1+'.StudentId='+left(@subjectStr,@i-1)+'.StudentId inner join'
SET @SubName=left(@subjectStr,@i-1)
--print left(@subjectStr,@i-1)
end
end
set @subjectStr=substring(@subjectStr,@i+1,len(@subjectStr)-@i)
set @i=charindex(',',@subjectStr)
end
if @subjectStr <> ''
begin
SET @fieldSql=@fieldSql+ ','+@subjectStr
SET @fieldSql1=@fieldSql1+','+@subjectStr+' int'
SET @fieldSql2=@fieldSql2+','+'Sum('+@subjectStr+') as '+@subjectStr+'总分'
SET @fieldSql3=@fieldSql3+@subjectStr
SET @fieldSql2 = RIGHT(@fieldSql2, Len(@fieldSql2) - 1)
SET @fieldSql1 = RIGHT(@fieldSql1, Len(@fieldSql1) - 1)
SET @fieldSql = RIGHT(@fieldSql, Len(@fieldSql) - 1)
SET @sql3=@sql3+' (
select TestSubjectId,TestClassId,TestInfoId,StudentName,StudentId,TestScore ' + @subjectStr+ ' from dbo.v_TestScore_Student_Class_Subject
where subjectName='''+ @subjectStr+''' and TestClassId='+isNull(convert(nvarchar(20),@TestClassId),+'TestClassId')+' and TestInfoId='+convert(nvarchar(20),@TestInfoId)+'
) as '+ @subjectStr+' on '+@SubName+'.StudentId='+@subjectStr+'.StudentId'
end
print @fieldSql
print @fieldSql1
print @fieldSql2
print @fieldSql3
print 'DECLARE @temp table(学生名称nvarchar(50),学号nvarchar(50),'+@fieldSql1+' ) insert into @temp'
print 'select '+@subjectStr+'.StudentName as 学生名称,'+@subjectStr+'.StudentId as 学号,'+@fieldSql+' from '+@sql1+@sql2+@sql3+' where '+@subjectStr+'.TestClassId='+isNull(convert(nvarchar(20),@TestClassId),@subjectStr+'.TestClassId')+' and '+@subjectStr+'.TestInfoId='+convert(nvarchar(20),@TestInfoId)+' select '+@fieldSql2+',avg('+@fieldSql3+') as 平均分from @temp'