我需要分别查询模拟和IP电话的每个站点的电话数量。 我可以用这两个查询来做到这一点。
IP电话：
select count(d.name) as IP_Phones, dp.name as DevicePool from Device as d inner join DevicePool as dp on d.fkDevicePool=dp.pkid inner join typemodel as tm on tm.enum=d.tkmodel where (tm.name != 'Analog Phone' and tm.name != 'Conference Bridge' and tm.name != 'CTI Route Point' and tm.name != 'CTI Port' and tm.name != 'MGCP Station' and tm.name != 'Route List' and tm.name != 'H.323 Gateway' and tm.name != 'Music On Hold' and tm.name != 'Media Termination Point' and tm.name != 'Tone Announcement Player' and tm.name != 'Cisco IOS Conference Bridge (HDV2)' and tm.name != 'Cisco IOS Software Media Termination Point (HDV2)' and tm.name != 'Cisco IOS Media Termination Point (HDV2)' and tm.name != 'SIP Trunk' and dp.name like '%PH%') group by dp.name order by dp.name 这导致了
ip_phones devicepool ========= ================ 815 Site1-DP 43 Site2-DP 32 Site3-DP 890 Site4-DP
模拟电话：
select count(d.name) as Analog_Phones, dp.name as DevicePool from Device as d inner join DevicePool as dp on d.fkDevicePool=dp.pkid inner join typemodel as tm on tm.enum=d.tkmodel where (tm.name = 'Analog Phone' and dp.name like '%PH%') group by dp.name order by dp.name
这导致了
analog_phones devicepool ============= ============== 12 Site1-DP 14 Site2-DP 1 Site3-DP 4 Site4-DP
我正在寻找的是一个单一的查询结果是这样的：
ip_phones analog_phones devicepool ========= ============= ========== 815 12 Site1-DP 43 14 Site2-DP 32 1 Site3-DP 890 4 Site4-DP
这应该做到这一点。 这个想法是采取两个查询，将它们联合在一起，然后将它们分组在设备池上，以便每个池有一行。
SELECT sum(analog_phones) as analog_phones, sum(ip_phones) as ip_phones, devicepool FROM (SELECT 0 AS analog_phones, count(d.name) AS IP_Phones, dp.name AS DevicePool FROM Device AS d INNER JOIN DevicePool AS dp ON d.fkDevicePool=dp.pkid INNER JOIN typemodel AS tm ON tm.enum=d.tkmodel WHERE (tm.name != 'Analog Phone' AND tm.name != 'Conference Bridge' AND tm.name != 'CTI Route Point' AND tm.name != 'CTI Port' AND tm.name != 'MGCP Station' AND tm.name != 'Route List' AND tm.name != 'H.323 Gateway' AND tm.name != 'Music On Hold' AND tm.name != 'Media Termination Point' AND tm.name != 'Tone Announcement Player' AND tm.name != 'Cisco IOS Conference Bridge (HDV2)' AND tm.name != 'Cisco IOS Software Media Termination Point (HDV2)' AND tm.name != 'Cisco IOS Media Termination Point (HDV2)' AND tm.name != 'SIP Trunk' AND dp.name LIKE '%PH%') GROUP BY dp.name UNION ALL SELECT count(d.name) AS Analog_Phones, 0 AS ip_phones, dp.name AS DevicePool FROM Device AS d INNER JOIN DevicePool AS dp ON d.fkDevicePool=dp.pkid INNER JOIN typemodel AS tm ON tm.enum=d.tkmodel WHERE (tm.name = 'Analog Phone' AND dp.name LIKE '%PH%') GROUP BY dp.name) a GROUP BY devicepool ORDER BY devicepool