group by 和 having的入门操作

2015-04-07 21:46:12 访问(1284) 赞(0) 踩(0)

生成和输出的SQL语句

select 
(count(t.ID)) as '学生个数', 
tc.TheName as TheName
from UTB_EXAM_STUDENT t , 
UTB_EXAM_CLASS tc
where t.ClassId = tc.ID group by 
tc.ID, tc.TheName having count(t.ID) > 5 and count(t.ID) < 555
order by 
tc.ID asc

@tp1 = 5
@tp2 = 555


select 
(count(t.ID)) as '学生个数', 
tc.TheName as TheName
from UTB_EXAM_STUDENT t , 
UTB_EXAM_CLASS tc
where t.ClassId = tc.ID group by 
tc.ID, tc.TheName having count(t.ID) > @tp1 and count(t.ID) < @tp2
order by 
tc.ID asc

@tp1 	 5 	 Int32 	 Input 	 SqlParameter

@tp2 	 555 	 Int32 	 Input 	 SqlParameter

关键代码

 ihaving.AddHaving
                (
                    SlowX.Core.Enums.SqlCommonFn.EmSqlCommonFn._Count,
                    entity._ID,
                    SlowX.Core.Enums.SqlLink.EmSqlLink.Greater,
                    5
                );

            ihaving.AddHaving
                (
                    SlowX.Core.Enums.SqlCommonFn.EmSqlCommonFn._Count,
                    entity._ID,
                    SlowX.Core.Enums.SqlLink.EmSqlLink.Less,
                    555
                );

代码写法

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using SlowX.DAL.Helpers;
using SlowX.Core.ICoreClasses;
using System.Data;
using SlowX.Functions.Functions;
using SlowX.Core.CoreClasses;

public partial class ExamSQL : System.Web.UI.Page
{
    protected string strResult = "";

    protected void Page_Load(object sender, EventArgs e)
    {
        DataBindTheContorls(null);
    }
    #region 操作

    /// <summary>
    /// 
    /// </summary>
    /// <param name="xdbHelper"></param>
    protected void DataBindTheContorls(DBHelper xdbHelper)
    {
        string theResult = null;
        bool bIsCreate = true;

        if (xdbHelper == null)
        {
            xdbHelper = SlowX.DAL.Helpers.DBHelper.CreateDBHelper();
        }
        else
        {
            // 没有打开链接 //
            bIsCreate = xdbHelper.IsNotOpen();
        }

        try
        {
            if (bIsCreate)
                xdbHelper.OpenDBHelper();

            SlowX.ExamLib.Business.UTB_EXAM_STUDENT 
                bll
                =
                SlowX.ExamLib.Business.UTB_EXAM_STUDENT.instance;

            SlowX.ExamLib.Entity.UTB_EXAM_STUDENT 
                entity
                =
                new SlowX.ExamLib.Entity.UTB_EXAM_STUDENT();

            SlowX.ExamLib.Entity.UTB_EXAM_CLASS
               entityClass
               =
               new SlowX.ExamLib.Entity.UTB_EXAM_CLASS();

            IFromTableDriver ifrom = entity;
            ISelectDriver iselect = entity;
            IQueryDriver iq = entity;
            IGroupByDriver igroup = entity;
            IQueryHavingDriver ihaving = entity;

            ifrom.AddIFromTableItemWithBaseEntity("tc", entityClass);

            iselect.AddISelectItemWithEntityFieldInfo
                ( 
                    SlowX.Core.Enums.SqlCommonFn.EmSqlCommonFn._Count,
                    "'学生个数'",
                    entity._ID
                );

            iselect.AddISelectItemWithEntityFieldInfo
                (
                    entityClass._TheName
                );

            IOrderByDriver iorder = entity;
            iorder.AddIOrderByItemWithEntityFieldInfo
                (
                    entityClass._ID, 
                    SlowX.Core.Enums.OrderBy.EmOrderBy.ASC
                );

            iq.AddIQueryItemWithEntityFieldInfo(entity._ClassId, entityClass._ID);

            ihaving.AddHaving
                (
                    SlowX.Core.Enums.SqlCommonFn.EmSqlCommonFn._Count,
                    entity._ID,
                    SlowX.Core.Enums.SqlLink.EmSqlLink.Greater,
                    5
                );

            ihaving.AddHaving
                (
                    SlowX.Core.Enums.SqlCommonFn.EmSqlCommonFn._Count,
                    entity._ID,
                    SlowX.Core.Enums.SqlLink.EmSqlLink.Less,
                    555
                );

            igroup.AddIGroupByItemWithEntityFieldInfo(entityClass._ID);
            igroup.AddIGroupByItemWithEntityFieldInfo(entityClass._TheName);

            
            theResult =
                @"<textarea name=""TextBox1"" rows=""2"" cols=""20"" id=""TextBox1"" style=""height:400px;width:99%;"">"
                + bll.BuildSqlList(entity, xdbHelper)
                + "</textarea>";

            theResult += "<br /><br />";

            try
            {
                DataSet ds = bll.List(entity, xdbHelper);

                theResult += DataSetSlowXFunctions.DataSetToHtmlTable(ds, true);
            }
            catch (Exception err)
            {
                theResult += "异常：" + err.Message;
            }


            if (bIsCreate)
                xdbHelper.EndDBHelper();

        }
        catch (Exception err)
        {
            if (bIsCreate)
                xdbHelper.TranDBHelper();

            throw err;
        }
        finally
        {
            if (bIsCreate)
                xdbHelper.FinallyDBHelper();
        }

        strResult = theResult;
    }

    #endregion 操作


}