数据查询和table显示问题

Question

数据表tb
id     id
class  课程
begin_time  开始时间
end_time    结束时间
date_time  日期
begin_time和end_time构成每一行的第一列的数据,比如08:00-09:00
date_time为年月日,根据上面日期下拉框的选择,切换不同星期的课程表,
比如我选择了2013/07/02的话,就显示07/01-07/07(星期一到星期日)这个星期的课程表怎样写sql语句并显示在table上面?效果要求和图片一样

Answer 1

非常简单，行转列的问题。自己研究下sql怎么写，如果不会，请上网搜下行转列、列转行。百度文库不错，可以搜索看看。

Answer 2

这个是自己做的，另外这个比较复杂。有以下几种思路
1、做好html表格，然后内容用后台填
2、后台直接生成表格第一种：如下代码
前台<h3>星期一课表</h3>
                        <table border="1" class="table">
                            <thead>
                                <tr>
                                    <th style="width: 30%">节&nbsp;&nbsp;次</th>
                                    <th style="width: 70%">课&nbsp;&nbsp;&nbsp;&nbsp;程</th>
                                </tr>
                            </thead>
                            <tr>
                                <td>1-2节</td>
                                <td>
                                    <asp:Label ID="lb11" CssClass="spanContent" runat="server"></asp:Label></td>
                            </tr>
                            <tr>
                                <td>3-4节</td>
                                <td>
                                    <asp:Label ID="lb12" runat="server"></asp:Label></td>
                            </tr>
                            <tr>
                                <td>5-6节</td>
                                <td>
                                    <asp:Label ID="lb13" runat="server"></asp:Label></td>
                            </tr>
                            <tr>
                                <td>7-8节</td>
                                <td>
                                    <asp:Label ID="lb14" runat="server"></asp:Label></td>
                            </tr>
                            <tr>
                                <td>9-11节</td>
                                <td>
                                    <asp:Label ID="lb15" runat="server"></asp:Label></td>
                            </tr>
                        </table>后台   private void BindCourse()
    {
        s_SelectResult selectResult = new s_SelectResult();
        DataTable dt = selectResult.GetCourseTable(ddlTerm.SelectedValue.ToString(), Session["UserID"].ToString());
        if (dt.Rows.Count > 0)
        {
            #region 清空现有的课表
            for (int i = 1;i <= 7;i++)
            {
                for(int j=1;j <= 5;j++)
                {
                    string lbName = "lb" + i.ToString() + j.ToString() ;
                    Label label = (Label)Master.FindControl("ContentPlaceHolder1").FindControl(lbName);
                    label.Text = string.Empty;
                }
            }
            #endregion
            foreach (DataRow dr in dt.Rows)
            {
                if (dr["WeekSection"].ToString() != "")
                {
                    string lbName = "lb" + dr["WeekSection"].ToString();
                    Label label = (Label)Master.FindControl("ContentPlaceHolder1").FindControl(lbName);
                    if (label == null)
                        continue;
                    else
                    {
                        string courinfor = dr["CourName"].ToString() + "(" + dr["CourOrder"].ToString() + ")" + dr["WeekTime"].ToString() + "H<br />" + dr["RoomID"].ToString() + "&nbsp;" + dr["WeekBegin"].ToString() + "-" + dr["WeekEnd"].ToString() + "周&nbsp;" + dr["LessType"].ToString();
                        if (label.Text.Trim() != string.Empty)
                            label.Text = label.Text + @"<br />" + courinfor;
                        else
                            label.Text = courinfor;
                    }
                }
            }
        }

Answer 3

第二种<asp:Table id="CourseTable" runat="server" BorderWidth="1px" CellPadding="0"
CellSpacing="0" GridLines="Both" BorderColor="#000000" BorderStyle="Solid" EnableViewState="False">
<asp:TableRow HorizontalAlign="Center" Height="30px" Font-Bold="True" runat="server" >
<asp:TableCell ColumnSpan="1" Width="30px" HorizontalAlign="Center" Text="时间" runat="server"></asp:TableCell>
<asp:TableCell Width="50px" Text="节次" runat="server"></asp:TableCell>
<asp:TableCell Width="126px" Text="星期一" runat="server"></asp:TableCell>
<asp:TableCell Width="126px" Text="星期二" runat="server"></asp:TableCell>
<asp:TableCell Width="126px" Text="星期三" runat="server"></asp:TableCell>
<asp:TableCell Width="126px" Text="星期四" runat="server"></asp:TableCell>
<asp:TableCell Width="126px" Text="星期五" runat="server"></asp:TableCell>
<asp:TableCell Width="126px" Text="星期六" runat="server"></asp:TableCell>
<asp:TableCell Width="124px" Text="星期日" runat="server"></asp:TableCell>
</asp:TableRow>
<asp:TableRow VerticalAlign="Middle" HorizontalAlign="Center" runat="server">
<asp:TableCell RowSpan="2" HorizontalAlign="Center" Text="上午" runat="server"></asp:TableCell>
<asp:TableCell HorizontalAlign="Center" Text="1－2节&lt;BR&gt;8:10--" runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
</asp:TableRow>
<asp:TableRow VerticalAlign="Middle" HorizontalAlign="Center" runat="server">
<asp:TableCell HorizontalAlign="Center" Text="3－4节&lt;BR&gt;10:05-" runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
</asp:TableRow>
<asp:TableRow VerticalAlign="Middle" HorizontalAlign="Center" runat="server">
<asp:TableCell RowSpan="2" HorizontalAlign="Center" Text="下午" runat="server"></asp:TableCell>
<asp:TableCell HorizontalAlign="Center" Text="5－6节&lt;br&gt;夏14:40&lt;BR&gt;秋14:10" runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
</asp:TableRow>
<asp:TableRow VerticalAlign="Middle" HorizontalAlign="Center" runat="server">
<asp:TableCell HorizontalAlign="Center" Text="7－8节&lt;Br&gt;夏16:25&lt;BR&gt;秋15:55" runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
</asp:TableRow>
<asp:TableRow VerticalAlign="Middle" HorizontalAlign="Center" runat="server">
<asp:TableCell HorizontalAlign="Center" Text="晚上" runat="server"></asp:TableCell>
<asp:TableCell HorizontalAlign="Center" Text="9－11节&lt;br&gt;夏18:45&lt;br&gt;秋18:15" runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
<asp:TableCell runat="server"></asp:TableCell>
</asp:TableRow>
</asp:Table>
后台   private void FormatTable(DataTable dt)
    {
        if (dt.Rows.Count > 0)
        {
            int i, j, k = 0;
            string courid1 = null, courid2 = null;
            int tableRowNum = 0; //构造表行数
            string courID = null, courName = null,courOrder=null,reupTypeName=null,teacherName=null,memo=null;//备注表            
            Table table = null;//产生表结构
            foreach (DataRow dr in dt.Rows)
            {
                if (dr["WeekSection"].ToString() != "")
                {
                    i = int.Parse(dr["WeekSection"].ToString().Substring(0, 1));
                    j = int.Parse(dr["WeekSection"].ToString().Substring(1, 1));
                    courid2 = dr["CourID"].ToString().Trim();//根据不同的courid得到不同颜色
                    if (courid1 != courid2)
                    {
                        courid1 = courid2;
                        k++;
                    }
                    if ((j == 2) || (j == 4)) i = i - 1;
                    if (dr["WeekTime"].ToString() == "3" && j != 5)  //3H学时课程增加在下一行，且不是第五节课程
                    {
                        CourseTable.Rows[j].Cells[i + 1].Text += "<Table><TR bgcolor =" + Enum.GetName(typeof(Colors), k) + "> <TD>" + dr["CourName"].ToString() + "（" + dr["CourOrder"].ToString() + "）" + "<br/>"
                            + dr["CourTeacherName1"].ToString() + "     " + dr["CourTeacherName2"].ToString()
                            + "    2H" + "<BR/>" + dr["RoomID"].ToString() + "     "
                            + dr["WeekBegin"].ToString() + "-" + dr["WeekEnd"].ToString() + "周" + "（" + dr["LessType"].ToString() + "）" + "<BR/>" + "</TD></TR></Table>";
                        j = j + 1;
                        if ((j == 2) || (j == 4)) i = i - 1;
                        CourseTable.Rows[j].Cells[i + 1].Text += "<Table><TR bgcolor =" + Enum.GetName(typeof(Colors), k) + "> <TD>" + dr["CourName"].ToString() + "（" + dr["CourOrder"].ToString() + "）" + "<br/>"
                            + dr["CourTeacherName1"].ToString() + "     " + dr["CourTeacherName2"].ToString()
                            + "    1H" + "<BR/>" + dr["RoomID"].ToString() + "     "
                            + dr["WeekBegin"].ToString() + "-" + dr["WeekEnd"].ToString() + "周" + "（" + dr["LessType"].ToString() + "）" + "<BR/>" + "</TD></TR></Table>";
                        j = j - 1;
                    }
                    else
                    {
                        CourseTable.Rows[j].Cells[i + 1].Text += "<Table><TR bgcolor =" + Enum.GetName(typeof(Colors), k) + "> <TD>" + dr["CourName"].ToString() + "（" + dr["CourOrder"].ToString() + "）" + "<br/>"
                            + dr["CourTeacherName1"].ToString() + "     " + dr["CourTeacherName2"].ToString()
                            + "    " + dr["WeekTime"].ToString() + "H" + "<br/>" + dr["RoomID"].ToString() + "     "
                            + dr["WeekBegin"].ToString() + "-" + dr["WeekEnd"].ToString() + "周" + "（" + dr["LessType"].ToString() + "）" + "<BR/>" + "</TD></TR></Table>";
                    }
                }
                else
                {
                    tableRowNum++;
                    if(tableRowNum==1) //第一行产生备注表
                        table = CreateTable();
                    courID = dr["CourID"].ToString();
                    courName = dr["CourName"].ToString();
                    reupTypeName = Utility.GetReupTypeNameByID(dr["ReupFlag"].ToString());
                    courOrder = dr["CourOrder"].ToString();
                    teacherName = dr["CourTeacherName1"].ToString() + "     " + dr["CourTeacherName2"].ToString();
                    memo = dr["TaskMemo"].ToString();
                    table.Rows.Add(CreateRow(tableRowNum, reupTypeName, courID, courName, courOrder, teacherName, memo));
                }
            }
        }    }