|
|
发表于 2020-7-10 23:00:01
|
显示全部楼层
下面简单设计:
1.显示: 采用gridview内有table模板，有三列（单元），每列有三小列(每小节).数据采用
直接 绑定便可.
2. 数据表结构(课时，教师，时间，内容，学号，姓名,成绩)其中课时值要限定(A1-C3)，因根据其值进行分解
3. 数据结果查询视图应该为(学号,A1,A2,A3,B1,B2,B3,C1,C2,C3)其中 A1内容为当前学生的成绩，上课教师及时间等详细信息.即将几个字段内容合并成一个字段A1中，便于绑定好显示
下面是部分代码(已调试)
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Search.." OnClick="Button1_Click" /><br />
<asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server" Height="195px" Width="700px" CellPadding="4" ForeColor="#333333" GridLines="None">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<table width="700px"><tr><td colspan="9">课时查询</td></tr>
<tr>
<td colspan="4">A单元</td>
<td colspan="3">B单元</td>
<td colspan="3">C单元</td>
</tr>
<tr>
<td>学生</td>
<td>A1</td>
<td>A2</td>
<td>A3</td>
<td>B1</td>
<td>B2</td>
<td>B3</td>
<td>C1</td>
<td>C2</td>
<td>C3</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%# DataBinder.Eval(Container.DataItem,"Stu_no") %> </td>
<td><%# DataBinder.Eval(Container.DataItem,"A1") %></td>
<td><%# DataBinder.Eval(Container.DataItem,"A2") %></td>
<td><%# DataBinder.Eval(Container.DataItem,"A3") %></td>
<td><%# DataBinder.Eval(Container.DataItem,"B1") %></td>
<td><%# DataBinder.Eval(Container.DataItem,"B2") %></td>
<td><%# DataBinder.Eval(Container.DataItem,"B3") %></td>
<td><%# DataBinder.Eval(Container.DataItem,"C1") %></td>
<td><%# DataBinder.Eval(Container.DataItem,"C2") %></td>
<td><%# DataBinder.Eval(Container.DataItem,"C3") %></td>
</tr>
</ItemTemplate>
<FooterTemplate></table></FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</div>
</form>
数据表:
if exists(
Select name from sysobjects where name=N'Study' and type='U'
)
drop table Study
Create Table Study(course nvarchar(2), stu_no nvarchar(10),stu_Name nvarchar(10),tehname nvarchar(10),rec_cr Datetime,Content nvarchar(100),source nvarchar(1))
Insert study values('A1','001','张三','李老师 ',getdate(),'','良')
Insert study values('A2','001','张三','张老师 ',getdate(),'','良')
Insert study values('A3','001','张三','济老师 ',getdate(),'','良')
Insert study values('B1','001','张三','李老师 ',getdate(),'','优')
Insert study values('B2','001','张三','张老师 ',getdate(),'','良')
Insert study values('B3','001','张三','李老师 ',getdate(),'','良')
Insert study values('C1','001','张三','李老师 ',getdate(),'','差')
Insert study values('C2','001','张三','张老师 ',getdate(),'','良')
Insert study values('C3','001','张三','李老师 ',getdate(),'','优')
Insert study values('A1','002','李四','李老师 ',GetDATE(),'','优')
--Select * From study
/* 以下是通过 case course 来进行查询，但是要进行分组. 本题是字符串，未成功。若数字则可
Select
stu_no,
Case course when 'A1' then '成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) End as A1,
Case course when 'A2' then '成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) End as A2,
Case course when 'A3' then '成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) End as A3,
Case course when 'B1' then '成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) End as B1,
Case course when 'B2' then '成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) End as B2,
Case course when 'B3' then '成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) End as B3,
Case course when 'C1' then '成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) End as C1,
Case course when 'C2' then '成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) End as C2,
Case course when 'C3' then '成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) End as C3
From study
*/
/* 以下是通过分解子表(共9次)，左连接方式来连接成一个总查询
Select AA1.stu_no,A1, A2,A3,B1,B2,B3,C1,C2,C3
From
(
Select
stu_no,'成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) AS A1
From study
Where course='A1'
)as AA1
Left join
(
Select
stu_no,'成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) AS A2
From study
Where course='A2'
)as AA2
ON (AA1.stu_no=AA2.stu_no)
LEFT JOIN
(
Select
stu_no,'成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) AS A3
From study
Where course='A3'
)as AA3
ON (AA1.stu_no=AA3.stu_no)
LEFT JOIN
(
Select
stu_no,'成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) AS B1
From study
Where course='B1'
)as AB1
ON (AA1.stu_no=AB1.stu_no)
LEFT JOin
(
Select
stu_no,'成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) AS B2
From study
Where course='B2'
)as AB2
ON (AA1.stu_no=AB2.stu_no)
left join
(
Select
stu_no,'成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) AS B3
From study
Where course='B3'
)as AB3
ON (AA1.stu_no=AB3.stu_no)
left join
(
Select
stu_no,'成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) AS C1
From study
Where course='C1'
)as AC1
ON (AA1.stu_no=AC1.stu_no)
LEFT JOIN
(
Select
stu_no,'成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) AS C2
From study
Where course='C2'
)as AC2
ON (AA1.stu_no=AC2.stu_no)
LEFT JOIN
(
Select
stu_no,'成绩:'+source+' 教师:'+tehname +'时间: '+ cast(rec_cr as varchar(16)) AS C3
From study
Where course='C3'
)as AC3
ON (AA1.stu_no=AC3.stu_no)
|
|
