联表查询的参考代码

2017-05-18 09:19:13 访问(2257) 赞(0) 踩(0)


SELECT *
FROM BF_COMBINATION A
LEFT JOIN BF_ARRIVAL B ON A.ARRIVAL_FLIGHT_DATE = B.FLIGHT_DATE AND A.ARRIVAL_FLIGHT_NUMBER = B.FLIGHT_NUMBER
LEFT JOIN BF_DEPARTURE C ON A.DEPARTURE_FLIGHT_DATE = C.FLIGHT_DATE AND A.DEPARTURE_FLIGHT_NUMBER = C.FLIGHT_NUMBER

 #region 校验表格

        /// <summary>
        /// 校验表格
        /// </summary>
        /// <param name="xdbHelper"></param>
        /// <returns></returns>
        protected OperResult SlowXSQLOper(DBHelper xdbHelper)
        {
            eKing.SzdfLib.Business.HR.UTB_SZDF_HR_ZZGL
                bll = eKing.SzdfLib.Business.HR.UTB_SZDF_HR_ZZGL.instance;

            eKing.SzdfLib.Entity.HR.UTB_SZDF_HR_ZZGL
                entity
                =
                new eKing.SzdfLib.Entity.HR.UTB_SZDF_HR_ZZGL();

            eKing.SzdfLib.Entity.HR.UTB_SZDF_HR_USER_ITEM
                userEntity
                =
                new eKing.SzdfLib.Entity.HR.UTB_SZDF_HR_USER_ITEM();

            IFromTableDriver ifrom = entity;
            ISelectDriver iselect = entity;

            ifrom.AddIFromTableItemWithBaseEntity
                (
                    "tu", userEntity, SlowX.Core.Enums.SqlJoin.EmSqlJoin.LeftJoin,
                    entity._User_Item_Id,
                    userEntity._ID
                );

            DBInvokeParameter dbInvoke =  bll.BuildDBInvokeParameterList(entity, xdbHelper);

            if (dbInvoke == null)
                return OperResult.ToSucc("null");
            else
                return OperResult.ToSucc(dbInvoke.SQL);
        }


        /// <summary>
        /// 校验表格
        /// </summary>
        public ActionResult SlowXSQL()
        {
            OperResult op = null;

            try
            {
                op = SlowXSQLOper(null);
            }
            catch (Exception err)
            {
                op = OperResult.ToError("发生异常：" + err.Message);
            }

            ContentResult theResult = new ContentResult();
            theResult.ContentEncoding = UTF8Encoding.UTF8;
            theResult.ContentType = MIMETypes.Json;
            theResult.Content = op.ToJson();

            return theResult;
        }

        #endregion 校验表格



        #region 校验表格

        /// <summary>
        /// 校验表格
        /// </summary>
        /// <param name="xdbHelper"></param>
        /// <returns></returns>
        protected OperResult SlowXSQL4MoreOper(DBHelper xdbHelper)
        {
            eKing.SzdfLib.Business.HR.UTB_SZDF_HR_ZZGL
                bll = eKing.SzdfLib.Business.HR.UTB_SZDF_HR_ZZGL.instance;

            eKing.SzdfLib.Entity.HR.UTB_SZDF_HR_ZZGL
                entity
                =
                new eKing.SzdfLib.Entity.HR.UTB_SZDF_HR_ZZGL();

            eKing.SzdfLib.Entity.HR.UTB_SZDF_HR_USER_ITEM
                userEntity
                =
                new eKing.SzdfLib.Entity.HR.UTB_SZDF_HR_USER_ITEM();

            IFromTableDriver ifrom = entity;
            ISelectDriver iselect = entity;

            string leftName = entity._User_Item_Id._Name+"|"+entity._InputUserId._Name;
            string rightName = userEntity._ID._Name + "|" + userEntity._InputUserId._Name;

            ifrom.AddIFromTableItemWithBaseEntity4More
                (
                    "tu", 
                    userEntity, SlowX.Core.Enums.SqlJoin.EmSqlJoin.LeftJoin,
                    leftName,
                    rightName
                );

            DBInvokeParameter dbInvoke = bll.BuildDBInvokeParameterList(entity, xdbHelper);

            if (dbInvoke == null)
                return OperResult.ToSucc("null");
            else
                return OperResult.ToSucc(dbInvoke.SQL);
        }


        /// <summary>
        /// 校验表格
        /// </summary>
        public ActionResult SlowXSQL4More()
        {
            OperResult op = null;

            try
            {
                op = SlowXSQL4MoreOper(null);
            }
            catch (Exception err)
            {
                op = OperResult.ToError("发生异常：" + err.Message);
            }

            ContentResult theResult = new ContentResult();
            theResult.ContentEncoding = UTF8Encoding.UTF8;
            theResult.ContentType = MIMETypes.Json;
            theResult.Content = op.ToJson();

            return theResult;
        }

        #endregion 校验表格