Zi 字媒體

SQL 同張資料表紀錄具有樹狀結構資料，利用 子查詢 + 統計 計算出一個根節點下面的子節點的數量 資料表: CREATE TABLE IF NOT EXISTS `device` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ip` varchar(30) DEFAULT NULL, `port` int(11) DEFAULT NULL, `mode` int(11) DEFAULT NULL, `alias` varchar(255) DEFAULT NULL, `map_id` varchar(255) DEFAULT NULL, `resource_id` varchar(255) DEFAULT NULL, `amount` int(11) DEFAULT NULL, `is_connected` int(11) DEFAULT NULL, `uid` int(11) DEFAULT NULL, `serial_number` int(11) DEFAULT NULL, `model_code` int(11) DEFAULT NULL, `sycg_id` varchar(255) DEFAULT NULL, `apb_group` int(11) DEFAULT NULL, `type` int(11) DEFAULT NULL COMMENT '[1]->C,[2]->D,[3]->M', `function` int(11) DEFAULT NULL COMMENT '[1]->input, [2]->output', `state` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; 測試資料數量: SELECT count(*) FROM `device` WHERE type=3 #模組數量 -> 260 SELECT count(uid) FROM `device` WHERE type=3 GROUP BY uid SELECT uid,count(uid) FROM `device` WHERE type=3 GROUP BY uid SELECT count(*) FROM `device` WHERE type=2 #門的數量 -> 1966 SELECT id,alias FROM `device` WHERE type=2 SQL [關聯子查詢]語法(每個門區下面安裝的模組數量)(32秒): SELECT id,alias,is_connected,(SELECT COUNT(*) FROM device WHERE uid=d.id) AS module_count FROM device AS d WHERE type=2 #( LIMIT 0 , 1966 ) SQL [虛擬表 + LEFT JOIN]語法(每個門區下面安裝的模組數量)(0.0234 秒): SELECT A.id AS id ,A.alias AS alias,A.is_connected AS is_connected,B.num AS num FROM (SELECT id,alias,is_connected FROM `device` WHERE type=2) AS A LEFT JOIN (SELECT uid,count(uid) AS num FROM `device` WHERE type=3 GROUP BY uid) AS B ON A.id=B.uid