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