iT邦幫忙

0

[MySQL] 設計帳號管理資料庫、查詢語法優化

各位前輩好/images/emoticon/emoticon13.gif

目前想設計一個資料庫,負責帳號的管理
大概像這樣:

table: account
id | account | parent_account | account_level
 1 |   aa123 |           boss |             1
 2 |    boss |        bigboss |             2
 3 | bigboss |           null |             3
 4 |   bb123 |           boss |             1

account_level 數字越大階級越高
parent_account 紀錄他的主管帳號
所以以上表來看,階級關係會是: bigboss > boss > aa123

另外有一張table是用來放一些工作紀錄

table: record
id | account | whatyouneedtodo
 1 |   aa123 |            work
 2 |   aa123 |        workhard
 3 |    boss |           sleep
 4 | bigboss |       donothing
 5 |   bb123 |            work

接著會依登入的帳號,列出登入者自己和他底下所有員工的工作紀錄

例(1) 登入aa123,列出
id | account | whatyouneedtodo
 1 |   aa123 |            work
 2 |   aa123 |        workhard
例(2) 登入boss,列出
id | account | whatyouneedtodo
 1 |   aa123 |            work
 2 |   aa123 |        workhard
 3 |    boss |           sleep
 5 |   bb123 |            work
例(1) 登入bigboss,列出
id | account | whatyouneedtodo
 1 |   aa123 |            work
 2 |   aa123 |        workhard
 3 |    boss |           sleep
 4 | bigboss |       donothing
 5 |   bb123 |            work

Q: 目前卡在SQL語法不知道怎麼下會更好
假設變數loginAccount代表登入的帳號

這樣可以顯示階級1的資料
SELECT * FROM record WHERE account = loginAccount
這樣可以顯示階級2的資料
SELECT * 
FROM record 
WHERE account = loginAccount or account IN (
    SELECT account 
    FROM account 
    WHERE parent_account = loginAccount)
這樣可以顯示階級3的資料
SELECT 
    *
FROM
    record
WHERE
    account = loginAccount
        OR account IN (SELECT 
            account
        FROM
            account
        WHERE
            parent_account = loginAccount)
        OR account IN (SELECT 
            account
        FROM
            account
        WHERE
            parent_account IN (SELECT 
                    account
                FROM
                    account
                WHERE
                    parent_account = loginAccount))

不知道有沒有更好的SQL下法?想請前輩指點
或是將table更改成什麼樣子,會更好下SQL

希望可以做到日後如果再加一個account_level
也不會有太大幅度的更動

想請各位前輩指點一下,或是table能怎麼更改會更好查詢
謝謝!


補上資料庫

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(45) DEFAULT NULL,
  `parent_account` varchar(45) DEFAULT NULL,
  `account_level` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

LOCK TABLES `account` WRITE;
INSERT INTO `account` VALUES (1,'aa123','boss','1'),(2,'boss','bigboss','2'),(3,'bigboss',NULL,'3'),(4,'bb123','boss','1');
UNLOCK TABLES;

CREATE TABLE `record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(45) DEFAULT NULL,
  `whatyouneedtodo` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

LOCK TABLES `record` WRITE;
INSERT INTO `record` VALUES (1,'aa123','work'),(2,'aa123','workhard'),(3,'boss','sleep'),(4,'bigboss','donothing'),(5,'bb123','work');
UNLOCK TABLES;
可以麻煩你附上建立資料表跟輸入資料的SQL嗎?
椅恩啾 iT邦新手 5 級 ‧ 2018-01-25 13:29:48 檢舉
@牙膏大師
好的,已經補上了!

1 個回答

0
牙膏大師
iT邦新手 5 級 ‧ 2018-01-25 17:25:06
最佳解答

你好,我剛剛試著回答這個問題,
雖然這個解法並沒有達到預期的成效,希望有人能繼續幫我完成它

底下的答案參考自
How to create a MySQL hierarchical recursive query
我的答案採用自 trincot 的最佳解答
而它的替代方案-1 (with cte) 我沒有嘗試,因為我的環境不是 MySQL 8.0

分析問題

首先,你想作到的是,
當登入 bigboss 的時候,抓出 bigboss 領導的 boss
再抓出 boss 領導的 所有員工
所以你想作到的事情是「為某一員工遞迴抓出它麾下所有的員工」。

調整資料表

然後因為用 varchar 來表示資料的從屬關係不是很好,
所以我調整了一下資料庫的結構

為了把「誰領導誰」的關係拉出來獨立成一張表,
我需要先移除 account 的 parent_account 欄位:

ALTER TABLE `account`
	DROP COLUMN `parent_account`;

接著再執行底下的語法:

CREATE TABLE `leadings` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`parent_id` INT(11) NOT NULL,
	`child_id` INT(11) NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `FK__account` (`parent_id`),
	INDEX `FK__account_2` (`child_id`),
	CONSTRAINT `FK__account` FOREIGN KEY (`parent_id`) REFERENCES `account` (`id`),
	CONSTRAINT `FK__account_2` FOREIGN KEY (`child_id`) REFERENCES `account` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;


INSERT INTO `leadings` (`id`, `parent_id`, `child_id`) VALUES
	(1, 3, 2),
	(2, 2, 1),
	(3, 2, 4);

查詢

select  id,
        account,
        parent_id
from    (select account.id, account.account, account.account_level, leadings.parent_id
			from account 
			inner join leadings on account.id = leadings.child_id 
			order by leadings.parent_id desc) account,
        (select @pv := '3') initialization
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id)

可以得到底下的結果:

id account parent_id
2 boss 3
1 aa123 2
4 bb123 2

在這個 SQL 之中有幾個重點:

  1. MySQL到底會如何執行上面這筆sql
  2. Subquery 撈出什麼東西
  3. @pv 這種變數是如何運作的

問題1

它的運作過程大概是這樣的:

  1. (FROM) 抓出 account 並讓它與 leadings 連接起來,並排序
    1. 指定一個字串給 @pv,目前它的值只有 bigboss 的 id
  2. (WHERE)逐一檢視「與 leadings 連結後的 account」的每一筆帳號其「parent_id」是不是在 @pv 之中
  3. (AND) 是的話,將該帳號的 id 加入 @pv ,以利比對下一筆帳號時的時候能抓出被目前這帳號領導的帳號

問題2

id account account_level parent_id
2 boss 2 3
1 aa123 1 2
4 bb123 1 2

所以搭配上面問題1解釋過得過程,我們大概可以推測他是這樣運作的

id condition @pv
2 boss 的 parent_id(3) 在 @pv 之中,把 boss 的 id(2) 加入 @pv 2,3
1 aa123 的 parent_id(2) 在 @pv 之中,把 aa123 的 id(1) 加入 @pv 1,2,3
4 bb123 的 parent_id(2) 在 @pv 之中,把 bb123 的 id(4) 加入 @pv 4,1,2,3

故事沒這麼美好

看到這邊,不知道你有沒有注意到,
這 sql 其實很依賴每一筆帳號 parent_id 出現的順序,
比如說如果 Subquery 的結果如下,肯定是沒辦法得出這麼完美的結果的

id account account_level parent_id
1 aa123 1 2
4 bb123 1 2
2 boss 2 3

它的運作過程如下:

id condition @pv
1 aa123 的 parent_id(2) 不在 @pv 之中 3
4 bb123 的 parent_id(2) 不在 @pv 之中 1,2,3
2 boss 的 parent_id(3) 在 @pv 之中,把 boss 的 id(4) 加入 @pv 2,3

問題3

這個語法請恕我不多解釋,我也是新手 QQ

看更多先前的回應...收起先前的回應...
椅恩啾 iT邦新手 5 級 ‧ 2018-01-26 09:05:53 檢舉

非常感謝牙膏大師!
一時之間還無法全部吸收,待我研究一番!
/images/emoticon/emoticon41.gif

椅恩啾 iT邦新手 5 級 ‧ 2018-01-26 09:51:40 檢舉

原來如此!大致上了解了

但是如前輩你說的如果parent_id出現的順序沒有排列好的話,用這個方式就不能解決了
我嘗試做出一個data讓問題2的subquery重現,把原本的insert改成下面這組

INSERT INTO `account` VALUES (1,'aa123','boss','1'),(2,'bigboss',NULL,'3'),(3,'boss','bigboss','2'),(4,'bb123','boss','1');

INSERT INTO `leadings` (`id`, `parent_id`, `child_id`) VALUES
	(1, 2, 3),
	(2, 3, 1),
	(3, 3, 4);
id account account_level parent_id
4 bb123 1 3

1 aa123 1 3
3 boss 2 2

椅恩啾 iT邦新手 5 級 ‧ 2018-01-26 09:56:05 檢舉

上面沒打完,也不能編輯QQ

id account account_level parent_id
4 bb123 1 3
1 aa123 1 3
3 boss 2 2

這樣的subquery跑出來就會是錯的

id account parent_id
4 bb123 3
1 aa123 3
椅恩啾 iT邦新手 5 級 ‧ 2018-01-26 10:04:42 檢舉

然後思考一下subquery的邏輯:
似乎是需要讓account_level較高的的account先被運算到
所以我增加了account_level的排序到subquery裡面

select  id,
        account,
        parent_id
from    (select account.id, account.account, account.account_level, leadings.parent_id
			from account 
			inner join leadings on account.id = leadings.child_id 
			order by account.account_level desc, leadings.parent_id desc) account,
        (select @pv := '2') initialization
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id)
id account parent_id
3 boss 2
4 bb123 3
1 aa123 3

看起來好像可以解決問題2!
不知道有沒有漏掉的部分/images/emoticon/emoticon06.gif

如果你的從屬鍊上每個職等都是不重複的,對,那就完美的解決了!

椅恩啾 iT邦新手 5 級 ‧ 2018-01-26 17:18:26 檢舉

感謝牙膏大師前輩/images/emoticon/emoticon07.gif

我要發表回答

立即登入回答