Мне нужно запросить количество телефонов на сайте для аналоговых и 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