MergeTable问题 返回
幽灵 发布于2周前
var splitQuery = _sqlSugarClient
.Queryable<MkHaveCouponShareRecordEntity>()
.IncludesAllFirstLayer()
.With(SqlWith.NoLock)
.Where(filter.FFilterRules)
.Select(x => new SplitSelectOutput
{
FId = x.FRecordId,
FIndex = SqlFunc.Floor(SqlFunc.RowNumber(SqlFunc.Desc(x.FRecordId)) / pageSize * 1M)
})
.MergeTable()
.GroupBy(x => x.FIndex)
.Select(x => new SplitSelectOutput
{
FIndex = x.FIndex,
FMaxId = SqlFunc.AggregateMin(x.FId)
})
.OrderBy(x => x.FIndex);
var sql = splitQuery.ToSqlString();生成的sql为
SELECT
[FIndex] AS [FIndex],
MIN([FId]) AS [FMaxId],
max([FRecordId]) AS SugarNav_FRecordId,
max([FShareMemberId]) AS SugarNav_FShareMemberId,
max([FTriggerCouponId]) AS SugarNav_FTriggerCouponId,
max([FClaimMemberId]) AS SugarNav_FClaimMemberId,
max([FActivityId]) AS SugarNav_FActivityId,
max([FShareMemberBelongShoppeId]) AS SugarNav_FShareMemberBelongShoppeId
FROM
(
SELECT
[FRecordId] AS [FId],
FLOOR(
(
(
row_number() over(
order by
[FRecordId] DESC
) / 100000
) * 1
)
) AS [FIndex],
[FRecordId] AS SugarNav_FRecordId,
[FShareMemberId] AS SugarNav_FShareMemberId,
[FTriggerCouponId] AS SugarNav_FTriggerCouponId,
[FClaimMemberId] AS SugarNav_FClaimMemberId,
[FActivityId] AS SugarNav_FActivityId,
[FShareMemberBelongShoppeId] AS SugarNav_FShareMemberBelongShoppeId
FROM
[T_MK_HaveCouponShareRecord] WITH(NOLOCK)
WHERE
(1 = 1)
) MergeTable
GROUP BY
[FIndex]
ORDER BY
[FIndex] ASC
执行时出现异常，
"列名 'FRecordId' 无效。\r\n列名 'FShareMemberId' 无效。\r\n列名 'FTriggerCouponId' 无效。\r\n列名 'FClaimMemberId' 无效。\r\n列名 'FActivityId' 无效。\r\n列名 'FShareMemberBelongShoppeId' 无效。",
出现了无用的字段，如： [FShareMemberId] AS SugarNav_FShareMemberId, 我的select中没有这个字段，但是生成的sql中存在，另外错误的原因是字段没有携带MergeTable前缀
理想的sql语句如下：
SELECT
MergeTable.[FIndex] AS [FIndex],
MIN(MergeTable.[FId]) AS [FMaxId]
FROM
(
SELECT
[FRecordId] AS [FId],
FLOOR(
(
(
row_number() over(
order by
[FRecordId] DESC
) / 100000
) * 1
)
) AS [FIndex]
FROM
[T_MK_HaveCouponShareRecord] WITH(NOLOCK)
WHERE
(1 = 1)
) MergeTable
GROUP BY
[FIndex]
ORDER BY
[FIndex] ASC
热忱回答（9）
-
fate sta VIP0
2周前.MergeTable().GroupBy(x => x.FIndex).Select(x =>newSplitSelectOutput{FIndex = x.FIndex,FMaxId = SqlFunc.AggregateMin(x.FId)}).MergeTable()//这儿少了.OrderBy(x => x.FIndex);varsql = splitQuery.ToSqlString();0 回复 -
幽灵 VIP0
2周前
@fate sta：还是一样的错误
var splitQuery = _sqlSugarClient
.Queryable<MkHaveCouponShareRecordEntity>()
.IncludesAllFirstLayer()
.With(SqlWith.NoLock)
.Where(filter.FFilterRules)
.Select(x => new SplitSelectOutput
{
FId = x.FRecordId,
FIndex = SqlFunc.Floor(SqlFunc.RowNumber(SqlFunc.Desc(x.FRecordId)) / pageSize * 1M)
})
.MergeTable()
.GroupBy(x => x.FIndex)
.Select(x => new SplitSelectOutput
{
FIndex = x.FIndex,
FMaxId = SqlFunc.AggregateMin(x.FId)
})
.MergeTable()
.OrderBy(x => x.FIndex);
生成的sql：
SELECT
*
FROM
(
SELECT
[FIndex] AS [FIndex],
MIN([FId]) AS [FMaxId],
max([FRecordId]) AS SugarNav_FRecordId,
max([FShareMemberId]) AS SugarNav_FShareMemberId,
max([FTriggerCouponId]) AS SugarNav_FTriggerCouponId,
max([FClaimMemberId]) AS SugarNav_FClaimMemberId,
max([FActivityId]) AS SugarNav_FActivityId,
max([FShareMemberLevelId]) AS SugarNav_FShareMemberLevelId,
max([FShareMemberBelongShoppeId]) AS SugarNav_FShareMemberBelongShoppeId
FROM
(
SELECT
[FRecordId] AS [FId],
FLOOR(
(
(
row_number() over(
order by
[FRecordId] DESC
) / 100000
) * 1
)
) AS [FIndex],
[FRecordId] AS SugarNav_FRecordId,
[FShareMemberId] AS SugarNav_FShareMemberId,
[FTriggerCouponId] AS SugarNav_FTriggerCouponId,
[FClaimMemberId] AS SugarNav_FClaimMemberId,
[FActivityId] AS SugarNav_FActivityId,
[FShareMemberLevelId] AS SugarNav_FShareMemberLevelId,
[FShareMemberBelongShoppeId] AS SugarNav_FShareMemberBelongShoppeId
FROM
[T_MK_HaveCouponShareRecord] WITH(NOLOCK)
WHERE
(1 = 1)
) MergeTable
GROUP BY
[FIndex]
) MergeTable
ORDER BY
[FIndex] ASC
0 回复 -
幽灵 VIP0
2周前
MergeTable.[FIndex] AS [FIndex],
MIN(MergeTable.[FId]) AS [FMaxId]
错误的原因是字段没有携带MergeTable前缀
0 回复 -
幽灵 VIP0
2周前
MergeTable方法是不是可以带一个参数，自定义表的别名，然后Select的时候把别名带上
0 回复 -
fate sta VIP0
2周前0 回复
-
幽灵 VIP0
1周前
我试过了，单表无导航属性时，没有问题，但是如果存在导航属性时就会出现这样的问题
0 回复 -
幽灵 VIP0
1周前
根据生成的sql,加上MergeTable就可以正常执行了，如：MergeTable.[FIndex] AS [FIndex],
0 回复 -
幽灵 VIP0
1周前
上面是一个简单的demo，执行结果：
{ "statusCode": 500, "data": null, "succeeded": false, "errors": "列名 'FRecordId' 无效。\r\n列名 'FMemberId' 无效。", "extras": null, "timestamp": 1767948549}0 回复 -
幽灵 VIP0
5天前
这个问题有办法解决吗
0 回复
