項目中經常會遇到父子結構顯示的問題,不同的數據庫有不同的寫的方式,比如SqlServer中用with union 實現,而Mysql則沒有這麼方便的語句。
如下category表,食品有pizaa,buger,coffee,而pizza又分了加cheese幾種,如何將他們的父子結構表現出來呢?
CREATE TABLE category( id INT(10), parent_id INT(10), name VARCHAR(50) ); INSERT INTO category (id, parent_id, name) VALUES (1, 0, 'pizza'), --node 1 (2, 0, 'burger'), --node 2 (3, 0, 'coffee'), --node 3 (4, 1, 'piperoni'), --node 1.1 (5, 1, 'cheese'), --node 1.2 (6, 1, 'vegetariana'), --node 1.3 (7, 5, 'extra cheese'); --node 1.2.1
stackoverflow上一個人給了一個很好的解決方案:
1. 創建一個函數
delimiter ~ DROP FUNCTION getPriority~ CREATE FUNCTION getPriority (inID INT) RETURNS VARCHAR(255) DETERMINISTIC begin DECLARE gParentID INT DEFAULT 0; DECLARE gPriority VARCHAR(255) DEFAULT ''; SET gPriority = inID; SELECT parent_id INTO gParentID FROM category WHERE ID = inID; WHILE gParentID > 0 DO /*0為根*/ SET gPriority = CONCAT(gParentID, '.', gPriority); SELECT parent_id INTO gParentID FROM category WHERE ID = gParentID; END WHILE; RETURN gPriority; end~ delimiter ;2. 調用函數得到的便是排完序的結果
SELECT * FROM category ORDER BY getPriority(ID);
☆ getPriority 這個函數的限制條件是:所有數據追溯上去必須有唯一的祖先。從樹結構來看,不能有多棵樹。