关于多个联表子查询的建议和BUG 返回

反馈

 5  147

悬赏：5 飞吻

普通的查询sqlSugar做的很好，一般的统计也可以进行查询，但是如果是很复杂的话，目前还不支持

1.目前最多是3个Queryable的联表查询，感觉不是很够用,而且语法不像普通连表查询,用起来不习惯

这个是普通的连表查询语法

new JoinQueryInfos(

    JoinType.Left, o.Id == i.OrderId,

    JoinType.Left, o.CustomId == c.Id

)

2.如果使用把查询.MergeTable()后将这个查询放在多个Queryable中会出现异常,生成的SQL不正确,且表名称会变成ExpandObject

3.多表查询现在最多扩展是12个表,希望可以扩展到20个

如果是下面的语句用SqlSugar进行转换，就很难

MyTest..dept A WITH(NOLOCK)

LEFT JOIN MyTest..person B WITH(NOLOCK) ON A.manager = B.ID

LEFT JOIN(

SELECT bigDept, COUNT(0) AS Num

FROM MyTest..person WITH(NOLOCK)

WHERE del = 0

GROUP BY bigDept

)TM1 ON A.id = TM1.bigDept

LEFT JOIN(

SELECT bigDept, COUNT(0) AS Num

FROM MyTest..person WITH(NOLOCK)

WHERE del in(0, 1) AND add_date>={ 0}

AND add_date<={ 1}

GROUP BY bigDept

)TM2 ON A.id = TM2.bigDept

LEFT JOIN(

SELECT bigDept, COUNT(0) AS Num

FROM MyTest..person WITH(NOLOCK)

WHERE del = 1 AND del_date>={ 0}

AND del_date<={ 1}

GROUP BY bigDept

)TM3 ON A.id = TM3.bigDept

LEFT JOIN(

SELECT bigDept, COUNT(0) AS Num

FROM MyTest..person B WITH(NOLOCK)

LEFT JOIN MyTest..tRole R WITH(NOLOCK) ON B.per_busi = R.RoleID

GROUP BY bigDept

)TM4 ON A.id = TM4.bigDept

LEFT JOIN(

SELECT A.addPerBigDept AS DID, COUNT(0) AS Num

FROM[dbo].[CtmLookPlanTbl] A WITH(NOLOCK)

WHERE feedbackType IN(1)

AND A.seeDateTime >={ 0}

AND A.seeDateTime <= CONVERT(CHAR(10),{ 1},120)+' 00:00:00'

AND A.feedbackTime >={ 0}

AND A.feedbackTime <={ 1}

GROUP BY A.addPerBigDept

)T1 ON A.ID = T1.DID

LEFT JOIN(

SELECT addPerBigDept AS DID, COUNT(0) AS Num

FROM(

SELECT addPerBigDept, A.addPer, ctmId

FROM[dbo].[CtmLookPlanTbl] A WITH(NOLOCK)

WHERE feedbackType IN(1)

AND A.seeDateTime >={ 0}

AND A.seeDateTime <= CONVERT(CHAR(10),{ 1},120)+' 00:00:00'

AND A.feedbackTime >={ 0}

AND A.feedbackTime <={ 1}

GROUP BY addPerBigDept,A.addPer,ctmId

GROUP BY addPerBigDept

)T1_1 ON A.ID = T1_1.DID

分享朋友圈：

热忱回答（5）

fate stay night VIP0
2周前

复杂操作出现的兼容问题，给我一个重现的DEMO

0 回复
fate stay night VIP0
2周前

12个表不够用，你以用2个queryable进行联表，那就等于24个表

三个queryableJOIN我可以后续在支持到3个以上

0 回复
银 VIP0
1周前

@fate stay night：
.MergeTable()后出现的sql生成错误demo

链接：https://pan.baidu.com/s/1ciRcJEo0wlkFxsOu1vFw1Q
提取码：i0eu

0 回复

fate stay night VIP0

1周前

@银：

你先这样用，后续我会优下一下你的写法

    
            var query1 = db.Queryable(db.Queryable<Table1>()
                .Select(a => new Table1
                {
                    AutoId = a.AutoId,
                    deptId = a.deptId
                })
                 .GroupBy(a => a.deptId));
            var query2 = db.Queryable<Table2>();


            var query=db.Queryable(query1, query2, (p1, p2) => p1.deptId == p2.id);
            var sql = query.ToSql();

0 回复