时间戳

首页 > 解决方案 > 在 mysql DB 中创建视图时如何将行号作为列？

mysql - 在 mysql DB 中创建视图时如何将行号作为列？

问题描述

我有一个选择查询，它可以在结果中自动生成行号列作为 ID。

SELECT
    @curRow := @curRow + 1 AS rowID,
    `tt`.`STATUS` AS `STATUS`,
    `tt`.`PERFORMED_BY` AS `PERFORMED_BY`,
    `tt`.`TMS_LOAD_ID` AS `TMS_LOAD_ID`,
    `tt`.`SHIP_LOCATION_ID` AS `SHIP_LOCATION_ID`,
    `tt`.`TRIP_END_TIME` AS `TRIP_END_TIME`,
    `tt`.`COMMENTS` AS `ASSIGN_DETAILS_COMMENT`,
   
FROM
    abc as tt
                       
    JOIN(
    SELECT
        @curRow := 0
    ) r

但是当我创建视图时，它告诉我选择命令有参数。

我该如何解决这个问题？

谢谢。

标签： mysqlview

解决方案

理想情况下，如果你使用的是 MySQL 8+，你可以在ROW_NUMBER这里使用：

CREATE VIEW yourView AS
SELECT
    ROW_NUMBER() OVER (ORDER BY some_col) AS rowID,
    STATUS AS STATUS,
    PERFORMED_BY AS PERFORMED_BY,
    TMS_LOAD_ID AS TMS_LOAD_ID,
    SHIP_LOCATION_ID AS SHIP_LOCATION_ID,
    TRIP_END_TIME AS TRIP_END_TIME,
    COMMENTS AS ASSIGN_DETAILS_COMMENT
FROM abc;

仔细注意，调用ROW_NUMBER具有一个ORDER BY子句，该子句确定哪一行将编号为 1、2 等。没有ORDER BY，上述调用ROW_NUMBER和您的用户变量方法都没有多大意义。

话虽如此，如果您坚持使用 MySQL 5.7 或更早版本，您也许可以使用相关子查询来模拟行号：

CREATE VIEW yourView AS
SELECT
    (SELECT COUNT(*) FROM abc t2 WHERE t2.some_col < t1.some_col) AS rowID,
    STATUS AS STATUS,
    PERFORMED_BY AS PERFORMED_BY,
    TMS_LOAD_ID AS TMS_LOAD_ID,
    SHIP_LOCATION_ID AS SHIP_LOCATION_ID,
    TRIP_END_TIME AS TRIP_END_TIME,
    COMMENTS AS ASSIGN_DETAILS_COMMENT
FROM abc t1

some_col如果没有重复值，这种方法会很好用。

推荐阅读