union all 使用

union和union all  操作符用于合并两个或多个 SELECT 语句的结果集

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

eg1:

select A.VehicleId, A.VehicleNum,A.PlateColor, A.OwnerId,A.OrgId, RO.OwnerName, AP.ShortName ,sum(A.Quantity) from
(
select V.*,VDC.ServerId, VDC.Quantity
from VE_Vehicle V left join GNSS_VehicleGpsDailyCount partition(P_201405) VDC
on V.VehicleNum = VDC.VehicleNum and V.PlateColor = VDC.PlateColor 
where  V.VehicleNum='苏ES0890'
union all
select V.*,VDC.ServerId, VDC.Quantity
from VE_Vehicle V left join GNSS_VehicleGpsDailyCount partition(P_201406) VDC
on V.VehicleNum = VDC.VehicleNum and V.PlateColor = VDC.PlateColor 
where  V.VehicleNum='苏ES0890'
)A 
left join GNSS_AccessPlatform AP on A.ServerId = AP.AccessCode 
,RO_RoadOwner RO,SYS_Organize O,GNSS_Area AC 
where A.OwnerId = RO.OwnerId and A.OrgId=O.OrgId and O.AreaCode=AC.AreaCode 
group by A.VehicleId, A.VehicleNum,A.PlateColor, A.OwnerId,A.OrgId, RO.OwnerName, AP.ShortName 

eg2:

        /// <summary>
        /// 获取车辆定位信息上传量列表--可跨一个月
        /// </summary>
        [LogPermission("获取车辆定位信息上传量列表--可跨一个月")]
        public virtual QueryResult GetVehicleUploadCountListByTwoMonth(Hashtable htParam, int intCurrentIndex, int intPageSize)
        {
            QueryResult qr = new QueryResult();
            qr.CurrentIndex = intCurrentIndex;
            qr.PageSize = intPageSize;
            QueryParamCollection queryParam = new QueryParamCollection();

            StringBuilder sb = new StringBuilder();
            if (ParamIsNullOrEmpty(htParam["MidTime1"]))
            {
                sb.Append(" (select V.*, VDC.Quantity ");
                sb.AppendFormat(" from {0} V left join {1} partition(P_{2}) VDC ", NHSessionFactory.GetTableName(typeof(Vehicle)), NHSessionFactory.GetTableName(typeof(VehicleGpsDailyCount)), Convert.ToDateTime(htParam["StartTime"]).ToString("yyyyMM"));
                sb.Append(" on V.VehicleNum = VDC.VehicleNum and V.PlateColor = VDC.PlateColor and VDC.CreateTime >= :StartTime and VDC.CreateTime <= :EndTime ");
                sb.AppendFormat(" where V.CertState = {0} and V.IsTrailer = {1} and V.OperState <> {2}", (int)EnumCertificateState.Valid, (int)EnumYesOrNo.ynNo, (int)EnumVehicleOperState.Stop);
                //准备查询参数
                PrepareQueryParamForVehicleUp(htParam, ref sb, ref queryParam);
                sb.Append(" )A ");
                
                //准备查询参数
                queryParam.SetDateTime("StartTime", Convert.ToDateTime(htParam["StartTime"].ToString() + " 00:00:00"));
                queryParam.SetDateTime("EndTime", Convert.ToDateTime(htParam["EndTime"].ToString() + " 23:59:59"));  
            }
            else
            {
                sb.Append(" (select V.*, VDC.Quantity ");
                sb.AppendFormat(" from {0} V left join {1} partition(P_{2}) VDC ", NHSessionFactory.GetTableName(typeof(Vehicle)), NHSessionFactory.GetTableName(typeof(VehicleGpsDailyCount)), Convert.ToDateTime(htParam["StartTime"]).ToString("yyyyMM"));
                sb.Append(" on V.VehicleNum = VDC.VehicleNum and V.PlateColor = VDC.PlateColor and VDC.CreateTime >= :StartTime and VDC.CreateTime <= :MidTime1 ");
                sb.AppendFormat(" where V.CertState = {0} and V.IsTrailer = {1} and V.OperState <> {2}", (int)EnumCertificateState.Valid, (int)EnumYesOrNo.ynNo, (int)EnumVehicleOperState.Stop);
                //准备查询参数
                PrepareQueryParamForVehicleUp(htParam, ref sb, ref queryParam); 
                sb.Append(" union all "); 
                sb.Append(" select V.*, VDC.Quantity ");
                sb.AppendFormat(" from {0} V left join {1} partition(P_{2}) VDC ", NHSessionFactory.GetTableName(typeof(Vehicle)), NHSessionFactory.GetTableName(typeof(VehicleGpsDailyCount)), Convert.ToDateTime(htParam["EndTime"]).ToString("yyyyMM"));
                sb.Append(" on V.VehicleNum = VDC.VehicleNum and V.PlateColor = VDC.PlateColor and VDC.CreateTime >= :MidTime2 and VDC.CreateTime <= :EndTime ");
                sb.AppendFormat(" where V.CertState = {0} and V.IsTrailer = {1} and V.OperState <> {2}", (int)EnumCertificateState.Valid, (int)EnumYesOrNo.ynNo, (int)EnumVehicleOperState.Stop);
                //准备查询参数
                PrepareQueryParamForVehicleUp(htParam, ref sb, ref queryParam);
                sb.Append(" )A ");
                 
                //准备查询参数
                queryParam.SetDateTime("StartTime", Convert.ToDateTime(htParam["StartTime"].ToString() + " 00:00:00"));
                queryParam.SetDateTime("MidTime1", Convert.ToDateTime(htParam["MidTime1"].ToString() + " 23:59:59"));
                queryParam.SetDateTime("MidTime2", Convert.ToDateTime(htParam["MidTime2"].ToString() + " 00:00:00"));
                queryParam.SetDateTime("EndTime", Convert.ToDateTime(htParam["EndTime"].ToString() + " 23:59:59")); 
            }

            sb.AppendFormat(" , {0} RO ,{1} O, {2} AC ", NHSessionFactory.GetTableName(typeof(RoadOwner)), NHSessionFactory.GetTableName(typeof(Organize)), NHSessionFactory.GetTableName(typeof(Area)));
            sb.Append(" where A.OwnerId = RO.OwnerId and A.OrgId = O.OrgId and O.AreaCode = AC.AreaCode ");
            //区域
            if (!ParamIsNullOrEmpty(htParam["Areas"]))
            {
                sb.AppendFormat(" and AC.AreaCode in ({0})", htParam["Areas"].ToString().Trim());
            }
            //业户名称
            if (!ParamIsNullOrEmpty(htParam["OwnerName"]))
            {
                sb.Append(" and RO.OwnerName like :OwnerName ");
                queryParam.SetString("OwnerName", "%" + htParam["OwnerName"].ToString().Trim() + "%");
            } 
            sb.Append(" group by A.VehicleId, A.VehicleNum,A.PlateColor, A.OwnerId, RO.OwnerName ");
            //上传次数小于
            if (!ParamIsNullOrEmpty(htParam["MaxValue"]))
            {
                sb.Append(" having sum(nvl(A.Quantity,0)) <= :MaxValue ");
                queryParam.SetInt32("MaxValue", Convert.ToInt32(htParam["MaxValue"]));
            }

            //查询记录数量
            qr.TotalRecordCount = GetSelectCountBySql(" select count(*) from ( select A.VehicleId from " + sb.ToString() + ")B ", queryParam);

            //查询记录
            string strQuery = " select A.VehicleId, A.VehicleNum,A.PlateColor, A.OwnerId, RO.OwnerName, sum(nvl(A.Quantity,0)) as Quantity from " + sb.ToString() + " Order by sum(nvl(A.Quantity,0)) desc nulls last";

            IList<VehicleUploadQuantity> lstReturn = new List<VehicleUploadQuantity>();
            IList lstTemp = GetListBySql(strQuery, queryParam);
            for (int i = 0; i < lstTemp.Count; i++)
            {
                Object[] lstObj = (Object[])lstTemp[i]; 
                VehicleUploadQuantity objVehicleUploadQuantity = new VehicleUploadQuantity();
                objVehicleUploadQuantity.VehicleId = lstObj[0].ToString();
                objVehicleUploadQuantity.VehicleNum = lstObj[1].ToString();
                objVehicleUploadQuantity.PlateColor = lstObj[2].ToString();
                objVehicleUploadQuantity.OwnerId = lstObj[3].ToString();
                objVehicleUploadQuantity.OwnerName = lstObj[4].ToString(); 
                if (lstObj[5] != null) objVehicleUploadQuantity.TotalQuantity = lstObj[5] == null ? 0 : Convert.ToInt32(lstObj[5]);
                 
                lstReturn.Add(objVehicleUploadQuantity);
            }

            qr.Data = lstReturn;
            return qr;
        }