主页 / server / 问题 / 582130
Accepted
driz
Asked: 2014-03-15 07:36:17 +0800 CST

从 Cisco UCM 数据库中提取电话数据的嵌套/连接 SQL 查询

  • 772

我需要分别查询每个站点的模拟电话和 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
query

1 个回答

  1. Best Answer

    那应该这样做。这个想法是同时获取两个查询，将它们合并在一起，然后将它们分组到设备池中，这样每个池就有一行。

    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
    • 1

相关问题