MySQL - 在 SELECT 子句中 [英] MySQL - While in SELECT clause

问题描述

是否可以在 MySQL 的 SELECT 子句中调用 while 语句?

Is it possible to call a while statement inside a SELECT clause in MySQL ?

这是我想做的一个例子:

Here is a example of what I want to do :

CREATE TABLE `item` (
  `id` int,
  `parentId` int,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `FK_parentId` (`parentId`),
  CONSTRAINT `FK_parentId` FOREIGN KEY (`parentId`) REFERENCES `item` (`id`)
);

我想选择每个项目的根，即更高的祖先(没有 parentId 的项目).在我看来，我会做这样的事情:

I would like to select the root of each item, i.e. the higher ancestor (the item that has no parentId). In my mind, I would do something like this :

select 
    `id` as 'ID',
    while `parentId` is not null do `id` = `parentId` end while as 'Root ID'
from
    `item`

这当然行不通.实现类似目标的更好方法是什么?

Of course this can't work. What is the better way to achieve something like that ?

编辑

这里是一个示例数据:

id  |   parentId
1   |   NULL
2   |   1
3   |   2
4   |   2
5   |   3
6   |   NULL
7   |   6
8   |   7
9   |   7

预期结果:

ID  |   RootId
1   |   NULL
2   |   1
3   |   1
4   |   1
5   |   1
6   |   NULL
7   |   6
8   |   6
9   |   6

谢谢.

推荐答案

流程如下:

BEGIN

    -- declare variables
    DECLARE cursor_ID INT;
    DECLARE cursor_PARENTID INT;
    DECLARE done BOOLEAN DEFAULT FALSE;

    -- declare cursor
    DECLARE cursor_item CURSOR FOR SELECT id, parentId FROM item;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- create a temporary table
    create TEMPORARY  table IF NOT EXISTS temp_table as (select id, parentId from item);
    truncate table temp_table;

    OPEN cursor_item;
    item_loop: LOOP

    -- fetch row through cursor
    FETCH cursor_item INTO cursor_ID, cursor_PARENTID;
        IF done THEN
            -- end loop if cursor is empty
            LEAVE item_loop;
        END IF;
            -- insert into 
            insert into temp_table
            select MAX(t.id) id, MIN(@pv := t.parentId) parentId
            from (select * from item order by id desc) t
            join (select @pv := cursor_ID) tmp
            where t.id = @pv;
    END LOOP;

    -- close cursor
    CLOSE cursor_item;

    -- get the results
    SELECT id id, parentid RootId from temp_table order by id ASC;
END

我创建了一个临时表，并在运行游标时将结果保存在其中.我想不出只有一个查询的解决方案.我不得不去找一个游标.

I created a temporary table and kept the results into it while running cursor. I couldn't think of a solution with just one query. I had to go for a cursor.

我从以下链接获得帮助:

如何在 MySQL 中进行递归 SELECT 查询?

如何创建 MySQL 分层递归查询

这篇关于MySQL - 在 SELECT 子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！