请教关于多个同级左连接子查询的 Freesql 写法

[Jess-Qiu]

打扰了，这边我写的代码生成出来的 Left Join 不是同级的，如果我想要生成出类似一下 SQL 需要怎么修改

SELECT ... FROM [T1] a 
 LeftJoin ...
 Left Join ...

   var subQueryStockQty = db.Select<tb_WH_Matmain>().Where(whMatMainWhere).GroupBy(e => e.FMATCODE).WithTempQuery(e => new { MatCode = e.Key, StockQty = e.Sum(e.Value.FSTOCKQNTY) });

   var subQueryZaiTuQty = db.Select<tb_WH_Matmain>().Where(whMatMainZaiTuWhere).GroupBy(e => e.FMATCODE).WithTempQuery(e => new { MatCode = e.Key, OutQty = e.Sum(e.Value.FSTOCKQNTY) });

           var query = db.Select<tb_WH_ChaoShi_Pingu>()
               .FromQuery(subQueryStockQty)
               .LeftJoin((a, b) => a.FMATCODE.Equals(b.MatCode))
               .WithTempQuery((a, b) => new
               {
                   MatCode = a.FMATCODE,
                   Hight = a.FHIGH,
                   Low = a.FLOW,
                   ShiFa = a.FSHIFA,
                   XuQiu = a.FXUQIU,
                   StockQty = b.StockQty
               })
               .FromQuery(subQueryZaiTuQty)
               .LeftJoin((c,d) => c.MatCode.Equals(d.MatCode) )
               .GroupBy((a, b) => new { a.MatCode, a.Hight, a.Low })
               .OrderBy(a => a.Key.MatCode); 

生成的 SQL

SELECT a.[MatCode] as1, a.[Hight] as2, a.[Low] as3, (sum(a.[XuQiu]) - sum(a.[ShiFa])) as4, min(a.[StockQty]) as5, min(d.[OutQty]) as6
FROM (
    SELECT a.[FMATCODE] [MatCode], a.[FHIGH] [Hight], a.[FLOW] [Low], a.[FSHIFA] [ShiFa], a.[FXUQIU] [XuQiu], b.[StockQty]
    FROM [tb_WH_ChaoShi_Pingu] a
    LEFT JOIN (
        SELECT a.[FMATCODE] [MatCode], sum(a.[FSTOCKQNTY]) [StockQty]
        FROM [tb_WH_Matmain] a
        WHERE ((a.[FCUSTOMERID] = N'94') AND (a.[FWAREHOUSE] = N'01') AND a.[FISOUT] = 0 AND a.[FTuiCangType] = 0)
        GROUP BY a.[FMATCODE] ) b ON (a.[FMATCODE] = b.[MatCode]) ) a
LEFT JOIN (
    SELECT a.[FMATCODE] [MatCode], sum(a.[FSTOCKQNTY]) [OutQty]
    FROM [tb_WH_Matmain] a
    WHERE ((a.[FCUSTOMERID] = N'94') AND not((a.[FWAREHOUSE] = N'01')) AND (a.[FZhuanyi_WAREHOUSE] = N'01') AND a.[FISOUT] = 1 AND a.[FTuiCangType] = 0)
    GROUP BY a.[FMATCODE] ) d ON (a.[MatCode] = d.[MatCode])
GROUP BY a.[MatCode], a.[Hight], a.[Low]
ORDER BY a.[MatCode]

[2881099]

暂时是这样的，FromQuery 只能传一个 ISelect<T>，方便更深层的嵌套结合。

执行计划差不多，以后有空再优化 SQL

[2881099]

db.Select<tb_WH_ChaoShi_Pingu>()
               .FromQuery(subQueryStockQty)
               .LeftJoin((a, b) => a.FMATCODE.Equals(b.MatCode))
               .WithTempQuery((a, b) => new { Item1 = a, Item2 = b } )
               .FromQuery(subQueryZaiTuQty)
               。。。。

可以这样写。

[2881099]

WithTempQuery 使用对象，不用一个个字段选择，减少 c# 代码。