|
|
The following simple design:
1. Display: There is a table template in gridview, with three columns (units), each column has three columns (each section). The data is used
Just bind directly.
2. The structure of the data table (class hours, teachers, time, content, student number, name, grades) where the class value should be limited (A1-C3), because it is decomposed according to its value
3. The data result query view should be (student number, A1, A2, A3, B1, B2, B3, C1, C2, C3) where A1 content is the current student's score, class teacher and time and other details. The field content is merged into a field A1, which is convenient for binding and displaying
Here is part of the code (debugged)
<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">Class hour query</td></tr>
<tr>
<td colspan="4">Unit A</td>
<td colspan="3">Unit B</td>
<td colspan="3">Unit C</td>
</tr>
<tr>
<td>Student</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>
data sheet:
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','Zhang San','Teacher Li',getdate(),'','Good')
Insert study values('A2','001','Zhang San','Teacher Zhang',getdate(),'','Good')
Insert study values('A3','001','Zhang San','Teacher Ji',getdate(),'','liang')
Insert study values('B1','001','Zhang San','Teacher Li',getdate(),'','Excellent')
Insert study values('B2','001','Zhang San','Teacher Zhang',getdate(),'','Good')
Insert study values('B3','001','Zhang San','Teacher Li',getdate(),'','Good')
Insert study values('C1','001','Zhang San','Teacher Li',getdate(),'','Poor')
Insert study values('C2','001','Zhang San','Teacher Zhang',getdate(),'','Good')
Insert study values('C3','001','Zhang San','Teacher Li',getdate(),'','Excellent')
Insert study values('A1','002','Li Si','Teacher Li',GetDATE(),'','Excellent')
--Select * From study
/* The following is a case course to query, but to be grouped. This question is a string, unsuccessful. If the number is OK
Select
stu_no,
Case course when'A1' then'Achievement:'+source+' Teacher:'+tehname +'Time:'+ cast(rec_cr as varchar(16)) End as A1,
Case course when'A2' then'Achievement:'+source+' Teacher:'+tehname +'Time:'+ cast(rec_cr as varchar(16)) End as A2,
Case course when'A3' then'Achievement:'+source+' Teacher:'+tehname +'Time:'+ cast(rec_cr as varchar(16)) End as A3,
Case course when'B1' then'Achievement:'+source+' Teacher:'+tehname +'Time:'+ cast(rec_cr as varchar(16)) End as B1,
Case course when'B2' then'Achievement:'+source+' Teacher:'+tehname +'Time:'+ cast(rec_cr as varchar(16)) End as B2,
Case course when'B3' then'Achievement:'+source+' Teacher:'+tehname +'Time:'+ cast(rec_cr as varchar(16)) End as B3,
Case course when'C1' then'Achievement:'+source+' Teacher:'+tehname +'Time:'+ cast(rec_cr as varchar(16)) End as C1,
Case course when'C2' then'Achievement:'+source+' Teacher:'+tehname +'Time:'+ cast(rec_cr as varchar(16)) End as C2,
Case course when'C3' then'Achievement:'+source+' Teacher:'+tehname +'Time:'+ cast(rec_cr as varchar(16)) End as C3
From study
*/
/* The following is a total query by decomposing the sub-table (a total of 9 times) and the left join mode
Select AA1.stu_no,A1, A2,A3,B1,B2,B3,C1,C2,C3
From
(
Select
stu_no,' grade:'+source+' teacher:'+tehname +'time:'+ cast(rec_cr as varchar(16)) AS A1
From study
Where course='A1'
)as AA1
Left join
(
Select
stu_no,' grade:'+source+' teacher:'+tehname +'time:'+ 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,' grade:'+source+' teacher:'+tehname +'time:'+ 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,' grade:'+source+' teacher:'+tehname +'time:'+ 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,' grade:'+source+' teacher:'+tehname +'time:'+ 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,' grade:'+source+' teacher:'+tehname +'time:'+ 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,' grade:'+source+' teacher:'+tehname +'time:'+ 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,' grade:'+source+' teacher:'+tehname +'time:'+ 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,' grade:'+source+' teacher:'+tehname +'time:'+ cast(rec_cr as varchar(16)) AS C3
From study
Where course='C3'
)as AC3
ON (AA1.stu_no=AC3.stu_no) |
|
|
|
China
Post time: 2020-7-10 23:00:01
United States
Invalid IP Address