使用union all为来自不同表的id添加自动增量列

iq0todco  于 2021-06-15  发布在  Mysql
关注(0)|答案(2)|浏览(444)

我看到在SQLServer中有一个名为row\u number的函数,在我的例子中,我连接了3个表,每个表的id列有不同的名称(这是我为jstree插件做的)
我有这个密码:

$sql= "(SELECT
                c1.idcarrera AS id,
                c1.nombre as text,
                null as parent_id
        FROM siiupv.carrera AS c1)";
$sql.=" UNION ALL ";
$sql.= " (SELECT
                pe.idplan_estudios AS id,
                pe.clave AS text,
                pe.idcarrera as parent_id
        FROM siiupv.plan_estudios AS pe)";
$sql.=" UNION ALL ";
$sql.= " (SELECT idcarga AS id, c2.clave AS text, idplan_estudios as parent_id FROM siiupv.carga AS c2)";
$qResults = $pdo->prepare($sql);
$qResults->execute();
$count = $qResults->rowCount();
while($m = $qResults->fetch(PDO::FETCH_ASSOC)){ 
        echo '<tr><td>&nbsp;'.$m["id"].'</td><td>'.$m["text"].'</td><td>'.$m["parent_id"].'</td></tr>';
}

它产生以下输出:

+------+------------------+
| id   | text | parent_id |
+------+------------------+
|    1 |  A1  | NULL      |
|    2 |  A2  | NULL      |
|    3 |  A3  | NULL      |
|    1 |  B1  |   1       |
|    2 |  B2  |   2       |
|    3 |  B3  |   1       |
|    4 |  B4  |   2       |
|    5 |  B5  |   5       |
|    1 |  C1  |   4       | Note! (Child of "B4")
|    2 |  C2  |   4       | Note! (Child of "B4")
+------+------------------+

但我想得到:

+------+------------------+
| id   | text | parent_id |
+------+------------------+
|    1 |  A1  | NULL      |
|    2 |  A2  | NULL      |
|    3 |  A3  | NULL      |
|    4 |  B1  |   1       |
|    5 |  B2  |   2       |
|    6 |  B3  |   1       |
|    7 |  B4  |   2       |
|    8 |  B5  |   5       |
|    9 |  C1  |   7       | Note! (Child of "B4")
|   10 |  C2  |   7       | Note! (Child of "B4")
+------+------------------+

注意!>ID被遍历,因此必须有值 7 我想得到帮助,至少自动增量标识

s6fujrry

s6fujrry1#

不确定我是否真的遇到了这个问题,但是你可以通过在它前面加前缀或者别的什么来生成一个新的唯一id。
选择concat(“a-”,idcarga)作为id,c2.clave作为text,concat(“a-”,idplan\u estudios)作为parent\u id

yiytaume

yiytaume2#

向resultset的每条记录添加行号的一种方法是使用会话变量:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) id, -- t.id
    t.text,
    t.parent_id
FROM (
    SELECT c1.idcarrera AS id, c1.nombre as text, null as parent_id FROM siiupv.carrera AS c1 ORDER BY c1.id
    UNION ALL SELECT pe.idplan_estudios AS id, pe.clave AS text, pe.idcarrera as parent_id FROM siiupv.plan_estudios AS pe ORDER BY c1.id
    UNION ALL SELECT idcarga AS id, c2.clave AS text, idplan_estudios as parent_id FROM siiupv.carga AS c2 ORDER BY c1.id
) t

相关问题