请教一下使用Storageable更新某列时使用GREATEST的正确方式 返回
SqlSugar
沟通中
2
402
悬赏:0 飞吻
我用的MYSQL,SqlSugar版本是5.1.4.213
我想在更新时使用GREATEST写入最大值,但是出现了一些问题
下面是我的代码
var storage = await db.Storageable(
accounts
.Select(x => new PlayerClearFinalDungeon
{
PlayerId = x.UserId,
DungeonId = dto.DungeonId,
Difficulty = dto.Difficulty,
})
.ToArray()
)
.WhereColumns(x => new { x.PlayerId, x.DungeonId })
.ToStorageAsync();
await storage.AsInsertable.ExecuteCommandAsync();
await storage
.AsUpdateable.SetColumns(
x => SqlFunc.MappingColumn<ushort>("difficulty"),
x => SqlFunc.MappingColumn<ushort>($"GREATEST(difficulty, {dto.Difficulty})")
)
.ExecuteCommandAsync();SetColumns这里报错了,说difficulty有歧义
MySqlConnector.MySqlException (0x80004005): Column 'difficulty' in field list is ambiguous
打印出来的SQL是
UPDATE `player_clear_final_dungeons` S INNER JOIN ( SELECT N'0' AS `id`,N'1' AS `difficulty`,N'1' AS `player_id`,N'1' AS `dungeon_id` UNION ALL SELECT N'0' AS `id`,N'1' AS `difficulty`,N'2' AS `player_id`,N'1' AS `dungeon_id` ) T ON S.`player_id`=T.`player_id` AND S.`dungeon_id`=T.`dungeon_id` SET `difficulty`=GREATEST(difficulty, 5) ;
这里我晓得是SET有歧义,所以我就尝试改成
await storage
.AsUpdateable.SetColumns(
x => SqlFunc.MappingColumn<ushort>("S.`difficulty`"),
x => SqlFunc.MappingColumn<ushort>($"GREATEST(S.`difficulty`, {dto.Difficulty})")
)
.ExecuteCommandAsync();错误是解决了,但是打印出来的SQL没有了GREATEST
UPDATE `player_clear_final_dungeons` S INNER JOIN ( SELECT N'0' AS `id`,N'1' AS `difficulty`,N'1' AS `player_id`,N'1' AS `dungeon_id` UNION ALL SELECT N'0' AS `id`,N'1' AS `difficulty`,N'2' AS `player_id`,N'1' AS `dungeon_id` ) T ON S.`player_id`=T.`player_id` AND S.`dungeon_id`=T.`dungeon_id` SET S.`difficulty`=T.`difficulty` ;
只要SetColumns的第一个参数用了 x => SqlFunc.MappingColumn<ushort>("S.`difficulty`"),就会变成 SET S.`difficulty`=T.`difficulty` ;
即使我把第二个参数改成 x => SqlFunc.MappingColumn<ushort>("GREATEST(S.`difficulty`, T.`difficulty`)") 也是一样
完全忽略了第二个参数指定的语句,不知道是BUG还是我用的姿势不对,请教一下
热忱回答(2)
-
fate sta VIP0
2026/4/17SqlFunc.MappingColumn<ushort>(sql)这样他要是一个SQL。 不能用拼接。0 回复 -
裸奔的代码 VIP0
2026/4/17@fate sta:不懂你的意思,是不是storage.AsUpdateable.SetColumns这里不能用SqlFunc.MappingColumn?
0 回复