如我有MYSQL语句SET @a=0;
UPDATE `Sys_SecurityMenu` a INNER JOIN
(SELECT *,@a:=(Case When @a + 1 = 0 Then @a + 2 ELSE @a + 1 END) AS na FROM `Sys_SecurityMenu` ORDER BY SortNumber,`Guid`) b
ON a.`Guid` = b.`Guid` AND 1 = 1 AND (a.`SecurityMenuGuid` is null) SET a.`SortNumber`=b.na;
Select @a但这条语句放到NHibernate的时候他会把
@a:=(Case When @a + 1 = 0 Then @a + 2 ELSE @a + 1 END)
中的“:”当做参数来用,这样导致这条语句不能正确运行
那上面的SQL语句,如何不在存在“:”而实现上面的效果呢?谢谢
UPDATE `Sys_SecurityMenu` a INNER JOIN
(SELECT *,@a:=(Case When @a + 1 = 0 Then @a + 2 ELSE @a + 1 END) AS na FROM `Sys_SecurityMenu` ORDER BY SortNumber,`Guid`) b
ON a.`Guid` = b.`Guid` AND 1 = 1 AND (a.`SecurityMenuGuid` is null) SET a.`SortNumber`=b.na;
Select @a但这条语句放到NHibernate的时候他会把
@a:=(Case When @a + 1 = 0 Then @a + 2 ELSE @a + 1 END)
中的“:”当做参数来用,这样导致这条语句不能正确运行
那上面的SQL语句,如何不在存在“:”而实现上面的效果呢?谢谢
UPDATE `Sys_SecurityMenu` a INNER JOIN
(SELECT *
FROM `Sys_SecurityMenu` ORDER BY SortNumber,`Guid`
) b
ON a.`Guid` = b.`Guid` AND 1 = 1 AND (a.`SecurityMenuGuid` is null)
SET a.`SortNumber`=b.na;SELECT sum(Case When @a + 1 = 0 Then @a + 2 ELSE @a + 1 END) into @a
FROM `Sys_SecurityMenu` ORDER BY SortNumber,`Guid`
Select @a
将
SELECT * FROM `Sys_SecurityMenu` ORDER BY SortNumber,`Guid`
插入有自增字段(ID)的临时表中,再UPDATE ID字段,最后与工作表
连接替换