多表联查(左联)计算多个子查询的Count数作为属性输出

 

服务实现描述
1 FDC_View_Status_Report_By_Res
1 ProcStep=1 :按 Fab 查。联合查询 FDCLOCATION FDCRES FDCDCP 表,
查询条件为
RES_CONNECTED
1. FDCLOCATION.FACTORY = FDCRES.FACTORY
2. FDCLOCATION.LOCATION_ID = FDCRES.LOCATION_ID
3. FDCLOCATION.FAB = InTag.CONDITION_1
4. 得到查找结果的行数
RES_ACTIVATED
1. FDCLOCATION.FACTORY = FDCRES.FACTORY
2. FDCRES.FACTORY = FDCDCP.FACTORY
3. FDCLOCATION.LOCATION_ID = FDCRES.LOCATION_ID
4. FDCRES.RES_ID = FDCDCP.RES_ID
5. FDCDCP.DCP_STATUS = “ACTIVED”
6. FDCLOCATION.FAB = InTag.CONDITION_1
7. 得到查找结果的行数
MODULE_CONNECTED
1. FDCLOCATION.FACTORY = FDCRES.FACTORY
2. FDCLOCATION.LOCATION_ID = FDCRES.LOCATION_ID
3. FDCLOCATION.FAB = InTag.CONDITION_1
4. 得到 GROUP BY PARENT_RES_ID 的结果行数
返回参数: SITE FAB LINE RES_CONNECTED RES_ACTIVATED
MODULE_CONNECTED
2 ProcStep=2 :按 Line
3 ProcStep=3 :按 Area
4 ProcStep=4 :按 Res Model
5 ProcStep=5 :按 Res Module 查:查询 FDCTRACETRXDATA 表,查询条件为
6 ) 返回查询结果列表
2 FDC_View_Lot_History_Detail
1 ) 查询 FDCDCP 表,查询条件为
RAWID = InTag.RAWID
2 ) 返回查询结果
======ProStep=1(Fab):
            string sFactory = "SYSTEM";
            string sFab = "**";
            var query1 = ctx.Fdclocation
                .Where(t => t.Factory == sFactory && t.Fab == sFab)
                .Select(t => new { t.Site, t.Fab, t.Line })
                .Distinct();
            var query2 = ctx.Fdclocation
                .Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
                .Where(t => t.l.Factory == sFactory && t.l.Fab == sFab && t.r.ResLevel == 0)
                .GroupBy(t => t.l.Line)
                .Select(g => new { Line = g.Key, RES_CONNECTED = g.Count() });
            var query3 = ctx.Fdclocation
                .Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
                .Join(ctx.Fdcdcp, l2 => new { l2.l.Factory, l2.r.ResId }, r2=> new { r2.Factory, r2.ResId }, (l2, r2) => new { l2, r2 })
                .Where(t => t.l2.l.Factory == sFactory && t.l2.l.Fab == sFab && t.l2.r.ResLevel == 0 && t.r2.DcpState == "ACTIVE")
                .GroupBy(t => t.l2.l.Line)
                .Select(g => new { Line = g.Key, RES_ACTIVATED = g.Count() });
            var query4 = ctx.Fdclocation
                .Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
                .Where(t => t.l.Factory == sFactory && t.l.Fab == sFab && t.r.ResLevel > 0)
                .GroupBy(t => t.l.Line)
                .Select(g => new { Line = g.Key, MODULE_CONNECTED = g.Count() });
            var query = query1
                .GroupJoin(query2, l => l.Line, r => r.Line, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { Line = "", RES_CONNECTED = 0 }), (a, b) => new { a.l.Site, a.l.Fab, a.l.Line, b.RES_CONNECTED })
                .GroupJoin(query3, l => l.Line, r => r.Line, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { Line = "", RES_ACTIVATED = 0 }), (a, b) => new { a.l.Site, a.l.Fab, a.l.Line, a.l.RES_CONNECTED, b.RES_ACTIVATED })
                .GroupJoin(query4, l => l.Line, r => r.Line, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { Line = "", MODULE_CONNECTED = 0 }), (a, b) => new { a.l.Site, a.l.Fab, a.l.Line, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, b.MODULE_CONNECTED })
                .Select(t => new { t.Site, t.Fab, t.Line, t.RES_CONNECTED, t.RES_ACTIVATED, t.MODULE_CONNECTED })
                .ToList();

======ProStep=2(Line):

            var query1 = ctx.Fdclocation
                .Where(t => t.Factory == sFactory && t.Line == sLine)
                .Select(t => new { t.Area })
                .Distinct();
            var query2 = ctx.Fdclocation
                .Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
                .Where(t => t.l.Factory == sFactory && t.l.Line == sLine && t.r.ResLevel == 0)
                .GroupBy(t => t.l.Area)
                .Select(g => new { Area = g.Key, RES_CONNECTED = g.Count() });
            var query3 = ctx.Fdclocation
                .Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
                .Join(ctx.Fdcdcp, l2 => new { l2.l.Factory, l2.r.ResId }, r2 => new { r2.Factory, r2.ResId }, (l2, r2) => new { l2, r2 })
                .Where(t => t.l2.l.Factory == sFactory && t.l2.l.Line == sLine && t.l2.r.ResLevel == 0 && t.r2.DcpState == "ACTIVE")
                .GroupBy(t => t.l2.l.Area)
                .Select(g => new { Area = g.Key, RES_ACTIVATED = g.Count() });
            var query4 = ctx.Fdclocation
                .Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
                .Where(t => t.l.Factory == sFactory && t.l.Line == sLine && t.r.ResLevel > 0)
                .GroupBy(t => t.l.Area)
                .Select(g => new { Area = g.Key, MODULE_CONNECTED = g.Count() });
            var query5 = ctx.Fdclocation
                .Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
                .Join(ctx.Fdcdcptracepara, l2 => new { l2.l.Factory, l2.r.ResId }, r2 => new { r2.Factory, r2.ResId }, (l2, r2) => new { l2, r2 })
                .Where(t => t.l2.l.Factory == sFactory && t.l2.l.Line == sLine)
                .GroupBy(t => t.l2.l.Area)
                .Select(g => new { Area = g.Key, TRACE_PARA_COLLECTED = g.Count() });
            var query6 = ctx.Fdclocation
                .Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
                .Join(ctx.Fdcdcptracepara, l2 => new { l2.l.Factory, l2.r.ResId }, r2 => new { r2.Factory, r2.ResId }, (l2, r2) => new { l2, r2 })
                .Join(ctx.Fdctracespec, l3 => new { l3.l2.l.Factory, l3.r2.ParaDisplayName }, r3 => new { r3.Factory, r3.ParaDisplayName }, (l3, r3) => new { l3, r3 })
                .Where(t => t.l3.l2.l.Factory == sFactory && t.l3.l2.l.Line == sLine)
                .GroupBy(t => t.l3.l2.l.Area)
                .Select(g => new { Area = g.Key, TRACE_PARA_MODELED = g.Count() });
            var query = query1
                .GroupJoin(query2, l => l.Area, r => r.Area, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { Area = "", RES_CONNECTED = 0 }), (a, b) => new { a.l.Area, b.RES_CONNECTED })
                .GroupJoin(query3, l => l.Area, r => r.Area, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { Area = "", RES_ACTIVATED = 0 }), (a, b) => new { a.l.Area, a.l.RES_CONNECTED, b.RES_ACTIVATED })
                .GroupJoin(query4, l => l.Area, r => r.Area, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { Area = "", MODULE_CONNECTED = 0 }), (a, b) => new { a.l.Area, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, b.MODULE_CONNECTED })
                .GroupJoin(query5, l => l.Area, r => r.Area, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { Area = "", TRACE_PARA_COLLECTED = 0 }), (a, b) => new { a.l.Area, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, a.l.MODULE_CONNECTED, b.TRACE_PARA_COLLECTED })
                .GroupJoin(query6, l => l.Area, r => r.Area, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { Area = "", TRACE_PARA_MODELED = 0 }), (a, b) => new { a.l.Area, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, a.l.MODULE_CONNECTED, a.l.TRACE_PARA_COLLECTED, b.TRACE_PARA_MODELED })
                .Select(t => new { t.Area, t.RES_CONNECTED, t.RES_ACTIVATED, t.MODULE_CONNECTED, t.TRACE_PARA_COLLECTED, EVENT_PARA_COLLECTED = 0, RECIPE_MODELED = 0, TRACE_SUM_PARAS = 0, EVENT_SUM_PARAS = 0, t.TRACE_PARA_MODELED, EVENT_COUNT = 0 })
                .ToList();

======ProStep=3(Area):

            var query1 = ctx.Fdclocation
                .Join(ctx.Fdcresmodel, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
                .Where(t => t.l.Factory == sFactory && t.l.Area == sArea)
                .Select(t => new { t.r.ModelId })
                .Distinct();
            var query2 = ctx.Fdcres
                .Where(t => t.Factory == sFactory && t.ResLevel == 0)
                .GroupBy(t => t.ResModelId)
                .Select(g => new { ModelId = g.Key, RES_CONNECTED = g.Count() });
            var query3 = ctx.Fdcres
                .Join(ctx.Fdcdcp, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
                .Where(t => t.l.Factory == sFactory && t.l.ResLevel == 0 && t.r.DcpState == "ACTIVE")
                .GroupBy(t => t.l.ResModelId)
                .Select(g => new { ModelId = g.Key, RES_ACTIVATED = g.Count() });
            var query4 = ctx.Fdcres
                .Where(t => t.Factory == sFactory && t.ResLevel > 0)
                .GroupBy(t => t.ResModelId)
                .Select(g => new { ModelId = g.Key, MODULE_CONNECTED = g.Count() });
            var query5 = ctx.Fdcres
                .Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
                .Where(t => t.l.Factory == sFactory)
                .GroupBy(t => t.l.ResModelId)
                .Select(g => new { ModelId = g.Key, TRACE_PARA_COLLECTED = g.Count() });
            var query6 = ctx.Fdcres
                .Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
                .Join(ctx.Fdctracespec, l2 => new { l2.l.Factory, l2.r.ParaDisplayName }, r2 => new { r2.Factory, r2.ParaDisplayName }, (l2, r2) => new { l2, r2 })
                .Where(t => t.l2.l.Factory == sFactory)
                .GroupBy(t => t.l2.l.ResModelId)
                .Select(g => new { ModelId = g.Key, TRACE_PARA_MODELED = g.Count() });
            var query = query1
                .GroupJoin(query2, l => l.ModelId, r => r.ModelId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { ModelId = "", RES_CONNECTED = 0 }), (a, b) => new { a.l.ModelId, b.RES_CONNECTED })
                .GroupJoin(query3, l => l.ModelId, r => r.ModelId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { ModelId = "", RES_ACTIVATED = 0 }), (a, b) => new { a.l.ModelId, a.l.RES_CONNECTED, b.RES_ACTIVATED })
                .GroupJoin(query4, l => l.ModelId, r => r.ModelId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { ModelId = "", MODULE_CONNECTED = 0 }), (a, b) => new { a.l.ModelId, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, b.MODULE_CONNECTED })
                .GroupJoin(query5, l => l.ModelId, r => r.ModelId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { ModelId = "", TRACE_PARA_COLLECTED = 0 }), (a, b) => new { a.l.ModelId, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, a.l.MODULE_CONNECTED, b.TRACE_PARA_COLLECTED })
                .GroupJoin(query6, l => l.ModelId, r => r.ModelId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { ModelId = "", TRACE_PARA_MODELED = 0 }), (a, b) => new { a.l.ModelId, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, a.l.MODULE_CONNECTED, a.l.TRACE_PARA_COLLECTED, b.TRACE_PARA_MODELED })
                .Select(t => new { t.ModelId, t.RES_CONNECTED, t.RES_ACTIVATED, t.MODULE_CONNECTED, t.TRACE_PARA_COLLECTED, EVENT_PARA_COLLECTED = 0, RECIPE_MODELED = 0, TRACE_SUM_PARAS = 0, EVENT_SUM_PARAS = 0, t.TRACE_PARA_MODELED, EVENT_COUNT = 0 })
                .ToList();

======ProStep=4(Model):

            var query1 = ctx.Fdcres
                .Where(t => t.Factory == sFactory && t.ResModelId == sModelId && t.ResLevel == 0)
                .Select(t => new { t.ResId })
                .Distinct();
            var query2 = ctx.Fdcres
                .Where(t => t.Factory == sFactory && t.ResLevel > 0)
                .GroupBy(t => t.BaseResId)
                .Select(g => new { ResId = g.Key, MODULE_CONNECTED = g.Count() });
            var query3 = ctx.Fdcres
                .Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
                .Where(t => t.l.Factory == sFactory)
                .GroupBy(t => t.l.ResId)
                .Select(g => new { ResId = g.Key, TRACE_PARA_COLLECTED = g.Count() });
            var query4 = ctx.Fdcres
                .Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
                .Join(ctx.Fdctracespec, l2 => new { l2.l.Factory, l2.r.ParaDisplayName }, r2 => new { r2.Factory, r2.ParaDisplayName }, (l2, r2) => new { l2, r2 })
                .Where(t => t.l2.l.Factory == sFactory)
                .GroupBy(t => t.l2.l.ResId)
                .Select(g => new { ResId = g.Key, TRACE_PARA_MODELED = g.Count() });
            var query = query1
                .GroupJoin(query2, l => l.ResId, r => r.ResId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { ResId = "", MODULE_CONNECTED = 0 }), (a, b) => new { a.l.ResId, b.MODULE_CONNECTED })
                .GroupJoin(query3, l => l.ResId, r => r.ResId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { ResId = "", TRACE_PARA_COLLECTED = 0 }), (a, b) => new { a.l.ResId, a.l.MODULE_CONNECTED, b.TRACE_PARA_COLLECTED })
                .GroupJoin(query4, l => l.ResId, r => r.ResId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { ResId = "", TRACE_PARA_MODELED = 0 }), (a, b) => new { a.l.ResId, a.l.MODULE_CONNECTED, a.l.TRACE_PARA_COLLECTED, b.TRACE_PARA_MODELED })
                .Select(t => new { t.ResId, t.MODULE_CONNECTED, t.TRACE_PARA_COLLECTED, EVENT_PARA_COLLECTED = 0, RECIPE_MODELED = 0, TRACE_SUM_PARAS = 0, EVENT_SUM_PARAS = 0, t.TRACE_PARA_MODELED, EVENT_COUNT = 0 })
                .ToList();

======ProStep=4(Res):

            var query1 = ctx.Fdcres
                .Where(t => t.Factory == sFactory && t.BaseResId == sResId)
                .Select(t => new { t.ResId })
                .Distinct();
            var query2 = ctx.Fdcres
                .Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
                .Where(t => t.l.Factory == sFactory)
                .GroupBy(t => t.l.ResId)
                .Select(g => new { ResId = g.Key, TRACE_PARA_COLLECTED = g.Count() });
            var query3 = ctx.Fdcres
                .Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
                .Join(ctx.Fdctracespec, l2 => new { l2.l.Factory, l2.r.ParaDisplayName }, r2 => new { r2.Factory, r2.ParaDisplayName }, (l2, r2) => new { l2, r2 })
                .Where(t => t.l2.l.Factory == sFactory)
                .GroupBy(t => t.l2.l.ResId)
                .Select(g => new { ResId = g.Key, TRACE_PARA_MODELED = g.Count() });
            var query = query1
                .GroupJoin(query2, l => l.ResId, r => r.ResId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { ResId = "", TRACE_PARA_COLLECTED = 0 }), (a, b) => new { a.l.ResId, b.TRACE_PARA_COLLECTED })
                .GroupJoin(query3, l => l.ResId, r => r.ResId, (l, r) => new { l, r })
                .SelectMany(lr => lr.r.DefaultIfEmpty(new { ResId = "", TRACE_PARA_MODELED = 0 }), (a, b) => new { a.l.ResId, a.l.TRACE_PARA_COLLECTED, b.TRACE_PARA_MODELED })
                .Select(t => new { t.ResId, t.TRACE_PARA_COLLECTED, EVENT_PARA_COLLECTED = 0, RECIPE_MODELED = 0, TRACE_SUM_PARAS = 0, EVENT_SUM_PARAS = 0, t.TRACE_PARA_MODELED, EVENT_COUNT = 0 })
                .ToList();

 

转载于:https://my.oschina.net/8824/blog/3098947

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值