Pertanyaan Bagaimana cara melakukan query SELECT Rekursif di MySQL?


Saya mendapat tabel berikut:

col1 | col2 | col3
-----+------+-------
1    | a    | 5
5    | d    | 3
3    | k    | 7
6    | o    | 2
2    | 0    | 8

Jika seorang pengguna mencari "1", program akan melihat col1 yang memiliki "1" maka itu akan mendapatkan nilai col3 "5", maka program akan terus mencari "5" di col1 dan itu akan mendapat "3" di col3, dan seterusnya. Jadi itu akan dicetak:

1   | a   | 5
5   | d   | 3
3   | k   | 7

Jika pengguna mencari "6", itu akan mencetak:

6   | o   | 2
2   | 0   | 8

Bagaimana cara membangun sebuah SELECT permintaan untuk melakukan itu?


75
2018-05-13 00:31


asal


Jawaban:


Edit

Solusi yang disebutkan oleh @leftclickben juga efektif. Kami juga dapat menggunakan prosedur yang tersimpan untuk hal yang sama.

CREATE PROCEDURE get_tree(IN id int)
 BEGIN
 DECLARE child_id int;
 DECLARE prev_id int;
 SET prev_id = id;
 SET child_id=0;
 SELECT col3 into child_id 
 FROM table1 WHERE col1=id ;
 create TEMPORARY  table IF NOT EXISTS temp_table as (select * from table1 where 1=0);
 truncate table temp_table;
 WHILE child_id <> 0 DO
   insert into temp_table select * from table1 WHERE col1=prev_id;
   SET prev_id = child_id;
   SET child_id=0;
   SELECT col3 into child_id
   FROM TABLE1 WHERE col1=prev_id;
 END WHILE;
 select * from temp_table;
 END //

Kami menggunakan temp table untuk menyimpan hasil dari output dan sebagai tabel temp yang berbasis sesi kami tidak akan ada masalah apapun mengenai data output yang salah.

SQL FIDDLE Demo

Coba kueri ini:

SELECT 
    col1, col2, @pv := col3 as 'col3' 
FROM 
    table1
JOIN 
    (SELECT @pv := 1) tmp
WHERE 
    col1 = @pv

SQL FIDDLE Demo:

| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |    a |    5 |
|    5 |    d |    3 |
|    3 |    k |    7 |


66
2018-05-13 03:21



Jawaban yang diterima oleh @Meherzad hanya berfungsi jika data dalam urutan tertentu. Ini terjadi untuk bekerja dengan data dari pertanyaan OP. Dalam kasus saya, saya harus memodifikasinya agar bekerja dengan data saya.

Catatan Ini hanya berfungsi ketika setiap "id" record (col1 dalam pertanyaan) memiliki nilai LEBIH BESAR DARI "parent id" yang direkam (col3 dalam pertanyaan). Ini sering terjadi, karena biasanya orang tua perlu dibuat terlebih dahulu. Namun jika aplikasi Anda memungkinkan perubahan pada hierarki, di mana item dapat di-re-parented di tempat lain, maka Anda tidak dapat bergantung pada ini.

Ini adalah pertanyaan saya jika itu membantu seseorang; Catatan itu tidak bekerja dengan pertanyaan yang diberikan karena data tidak mengikuti struktur yang diperlukan yang dijelaskan di atas.

select t.col1, t.col2, @pv := t.col3 col3
from (select * from table1 order by col1 desc) t
join (select @pv := 1) tmp
where t.col1 = @pv

Perbedaannya adalah itu table1 sedang dipesan oleh col1 sehingga orang tua akan mengikutinya (sejak orang tua col1 nilainya lebih rendah dari anak).


49
2017-07-23 04:30



Jawaban leftclickben bekerja untuk saya, tetapi saya ingin sebuah jalur dari simpul yang diberikan mem-back up pohon ke akarnya, dan ini sepertinya berjalan ke arah lain, ke bawah pohon. Jadi, saya harus membalik beberapa bidang di sekitar dan berganti nama untuk kejelasan, dan ini bekerja untuk saya, jika ini adalah apa yang orang lain inginkan juga -

item | parent
-------------
1    | null
2    | 1
3    | 1
4    | 2
5    | 4
6    | 3

dan

select t.item_id as item_id, @pv:=t.parent as parent
from (select * from item_tree order by item_id desc) t
join
(select @pv:=6)tmp
where t.item_id=@pv;

memberi:

item | parent
-------------
6    | 3
3    | 1
1    | null

15
2017-09-25 05:16



Prosedur tersimpan adalah cara terbaik untuk melakukannya. Karena solusi Meherzad akan berfungsi hanya jika data mengikuti urutan yang sama.

Jika kita memiliki struktur tabel seperti ini

col1 | col2 | col3
-----+------+------
 3   | k    | 7
 5   | d    | 3
 1   | a    | 5
 6   | o    | 2
 2   | 0    | 8

Itu tidak akan berhasil. SQL Fiddle Demo

Berikut ini adalah contoh kode prosedur untuk mencapai hal yang sama.

delimiter //
CREATE PROCEDURE chainReaction 
(
    in inputNo int
) 
BEGIN 
    declare final_id int default NULL;
    SELECT col3 
    INTO final_id 
    FROM table1
    WHERE col1 = inputNo;
    IF( final_id is not null) THEN
        INSERT INTO results(SELECT col1, col2, col3 FROM table1 WHERE col1 = inputNo);
        CALL chainReaction(final_id);   
    end if;
END//
delimiter ;

call chainReaction(1);
SELECT * FROM results;
DROP TABLE if exists results;

7
2018-01-27 10:37



Jika Anda ingin dapat memiliki SELECT tanpa masalah id induk harus lebih rendah dari id anak, fungsi dapat digunakan. Mendukung juga beberapa anak (sebagai pohon harus dilakukan) dan pohon dapat memiliki banyak kepala. Ini juga memastikan untuk memutuskan apakah ada loop dalam data.

Saya ingin menggunakan SQL dinamis untuk dapat melewati nama tabel / kolom, tetapi fungsi di MySQL tidak mendukung ini.

DELIMITER $$

CREATE FUNCTION `isSubElement`(pParentId INT, pId INT) RETURNS int(11)
DETERMINISTIC    
READS SQL DATA
BEGIN
DECLARE isChild,curId,curParent,lastParent int;
SET isChild = 0;
SET curId = pId;
SET curParent = -1;
SET lastParent = -2;

WHILE lastParent <> curParent AND curParent <> 0 AND curId <> -1 AND curParent <> pId AND isChild = 0 DO
    SET lastParent = curParent;
    SELECT ParentId from `test` where id=curId limit 1 into curParent;

    IF curParent = pParentId THEN
        SET isChild = 1;
    END IF;
    SET curId = curParent;
END WHILE;

RETURN isChild;
END$$

Di sini, meja test harus dimodifikasi ke nama tabel sebenarnya dan kolom (ParentId, Id) mungkin harus disesuaikan dengan nama asli Anda.

Penggunaan:

SET @wantedSubTreeId = 3;
SELECT * FROM test WHERE isSubElement(@wantedSubTreeId,id) = 1 OR ID = @wantedSubTreeId;

Hasil:

3   7   k
5   3   d
9   3   f
1   5   a

SQL untuk pembuatan percobaan:

CREATE TABLE IF NOT EXISTS `test` (
  `Id` int(11) NOT NULL,
  `ParentId` int(11) DEFAULT NULL,
  `Name` varchar(300) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

insert into test (id, parentid, name) values(3,7,'k');
insert into test (id, parentid, name) values(5,3,'d');
insert into test (id, parentid, name) values(9,3,'f');
insert into test (id, parentid, name) values(1,5,'a');
insert into test (id, parentid, name) values(6,2,'o');
insert into test (id, parentid, name) values(2,8,'c');

EDIT: Ini dia biola untuk mengujinya sendiri. Itu memaksa saya untuk mengubah pembatas menggunakan yang telah ditetapkan, tetapi itu berhasil.


6
2018-06-23 13:23