MySQL 查询从表计数总计和已使用中选择，计算剩余

Question

我有两张 table :

-- **phones**

CREATE TABLE IF NOT EXISTS `phones` (
`phone_id` int(11) NOT NULL AUTO_INCREMENT,
`phone_type_id` int(11) NOT NULL,
`phone_mac` varchar(255) NOT NULL,
`office_id` int(11) DEFAULT NULL,
`did_id` int(11) DEFAULT NULL,
`virtual_extension_id` int(11) DEFAULT NULL,
PRIMARY KEY (`phone_id`),
UNIQUE KEY `phone_mac` (`phone_mac`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=67 ;

INSERT INTO `phones` (`phone_id`, `phone_type_id`, `phone_mac`, `office_id`, `did_id`, `virtual_extension_id`) VALUES
(1, 1, '0004f22ccccc', 5, NULL, NULL),
(63, 1, '0004f22bbbbb', 9, 33, NULL),
(64, 20, '0004f22aaaaa', 6, NULL, 6600),
(65, 2, '000033434333', 9, NULL, NULL),
(66, 20, '21232da32434', 6, NULL, NULL);

--  **phone_types**

CREATE TABLE IF NOT EXISTS `phone_types` (
  `phone_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `phone_type_manufacturer` varchar(255) NOT NULL,
  `phone_type_model` varchar(255) NOT NULL,
  PRIMARY KEY (`phone_type_id`),
  UNIQUE KEY `phone_type_model` (`phone_type_model`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

INSERT INTO `phone_types` (`phone_type_id`, `phone_type_manufacturer`, `phone_type_model`) VALUES
(1, 'Polycom', 'SoundPoint IP 550'),
(2, 'Polycom', 'SoundPoint IP 450'),
(3, 'Polycom', 'SoundPoint IP 335'),
(4, 'Polycom', 'SoundPoint IP 560'),
(5, 'Polycom', 'SoundPoint IP 670'),
(6, 'Polycom', 'SoundStation IP 5000'),
(7, 'Polycom', 'SoundStation IP 6000'),
(8, 'Yealink', 'W52P'),
(20, 'Aastra', '6757i CT');

我可以从phone_types 表中选择所有电话，然后获取Phones 表的phone_type_id 列中包含这些电话的总数。

我需要计算有多少部电话在 Phones 表的 did_id 或 virtual_extension_id 中具有值(非 NULL)。这就是我挂断的地方。

这是我到目前为止所拥有的。请注意，我目前使用的是相同的总计数，因为我无法计算出已使用的计数。任何帮助是极大的赞赏。提前致谢。

SELECT 
phone_types.phone_type_id AS phone_type_id, 
phone_type_manufacturer AS phone_type_manufacturer, 
phone_type_model AS phone_type_model, 
COUNT( phones.phone_type_id ) AS total_count, 
COUNT( phones.phone_type_id ) AS used_count
FROM  phone_types 
LEFT JOIN phones ON phones.phone_type_id = phone_types.phone_type_id
GROUP BY phone_types.phone_type_id

示例输出:

    1 | Polycom | SoundPoint IP 550    | 2 | 2
    2 | Polycom | SoundPoint IP 450    | 0 | 0
    3 | Polycom | SoundPoint IP 335    | 0 | 0
    4 | Polycom | SoundPoint IP 560    | 0 | 0
    5 | Polycom | SoundPoint IP 670    | 0 | 0
    6 | Polycom | SoundStation IP 5000 | 0 | 0
    7 | Polycom | SoundStation IP 6000 | 0 | 0
    8 | Yealink | W52P                 | 0 | 0
   20 | Aastra  | 6757i CT             | 1 | 1

编辑:添加了创建和插入语句。

已编辑:似乎正在取得进展。

选择phone_types.phone_type_manufacturer，phone_types.phone_type_model，COUNT(phones.phone_type_id)AS总计，COUNT(phones.did_id)或 virtual_extension_id ) AS 使用来自手机INNER JOIN 电话类型 ON 电话. 电话类型_id = 电话_类型. 电话类型_id按电话分组。phone_type_id

这给了我以下输出:制造商型号 总计 已使用Polycom Soundpoint IP 550 2 1宝利通 SoundPoint IP 450 1 0阿斯特拉 6757i CT 2 1

这是正确的，但我需要显示 did_id 和 virtual_extension_id 中都有 NULL 值的其他手机？

Answer 1

我猜你正在追求这样的东西......虽然我不太明白你的数据集如何与你的结果集相符？!？!

SELECT t.phone_type_id
     , t.phone_type_manufacturer
     , t.phone_type_model
     , COUNT(COALESCE(did_id,virtual_extension_id)) x
  FROM phone_types t 
  LEFT 
  JOIN phones p 
    ON p.phone_type_id = t.phone_type_id
 GROUP 
    BY t.phone_type_id;