未增加序号的代码
SELECT
a.*,
b.object_name,
b.object_model,
c.depart_name
FROM
public_depart_use a,
public_object b,
hr_departs c
WHERE
a.depart_code = c.depart_code
AND a.object_code = b.object_code
ORDER BY
a.creation_date DESC;
增加序号后的代码
SELECT
(@row_number:=@row_number + 1) AS num,
a.*,
b.object_name,
b.object_model,
c.depart_name
FROM
public_depart_use a,
public_object b,
hr_departs c,
(SELECT @row_number:=0) AS t
WHERE
a.depart_code = c.depart_code
AND a.object_code = b.object_code
ORDER BY
a.creation_date DESC;
在这个查询中,我们使用了一个变量
@row_number
来模拟行号的功能。通过在查询中初始化并递增
@row_number
变量,我们可以为每一行赋予一个唯一的序号值。这样,查询结果将包含一个名为“num”的列,从1开始依次排序,同时保留原始查询的其他列数据。注:在使用这个查询之前,确保在执行它之前先执行
SET @row_number:=0;
这个语句,以初始化@row_number
变量。