我看到在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> '.$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
我想得到帮助,至少自动增量标识
2条答案
按热度按时间w7t8yxp51#
向resultset的每条记录添加行号的一种方法是使用会话变量:
b5lpy0ml2#
不确定我是否真的遇到了这个问题,但是你可以通过在它前面加前缀或者别的什么来生成一个新的唯一id。
选择concat(“a-”,idcarga)作为id,c2.clave作为text,concat(“a-”,idplan\u estudios)作为parent\u id