以下的SQL指令,該如何改為只以區處編號為主篩選資料?
備註:team_CName 只有在dept_id的table 才有

可以提供參考數據跟期望數據嗎?
參考別人文章提問方式:請問如何抓出 '20歲以下' 佔總會數前25% 的所有總會數跟總資產和平均資產和人數? - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

就目前SQL能看出你已經查出d.team_id as 區處編號
假如以區處編號為主篩選資料，在where加上條件篩選就可以，但這應該不是你要的

更新問題:

上面的圖是SQL指令的執行結果，該如何下SQL指令不用where條件，將圖片中的總管理處合成一筆？

保留區處名稱、已承租主機、每月主機租金、每年總租金，其中每月主機租金、每年總租金是加總，期望是用同一個SQL指令完成

回答:

可以使用cte + group
其中因為已承租主機有多筆資料，要使用FOR XML PATH組合，反之不要已承租主機資料可以去掉。

若d.team_CName as 區處名稱,SUBSTRING(a.預算邊號,4,3) as 預算部門，這2個欄位都不要，SQL指令會比較簡單嗎?

with cte as (
	SELECT
	d.team_id as 區處編號
	,d.team_CName as 區處名稱
	,SUBSTRING(a.預算邊號,4,3) as 預算部門
	,isnull(c.已承租主機,0) as 已承租主機
	,isnull(c.每月主機租金,0) as 每月主機租金
	,(isnull(c.每月主機租金,0))*12 as 每年總租金
	FROM device as a

	LEFT OUTER JOIN spec as b on a.型號=b.PRD_ID
	LEFT OUTER JOIN
	(SELECT SUBSTRING(a.預算邊號,4,3) as 預算部門,count(b.PRD_KIND) as 已承租主機,sum(b.PRD_NT) as 每月主機租金
	FROM device as a
	LEFT OUTER JOIN spec as b on a.型號=b.PRD_ID
	LEFT OUTER JOIN dept as c on SUBSTRING(a.預算邊號,4,3)=c.dept_id
	LEFT OUTER JOIN Form as d on a.BPM單號 = d.BPM_serial
	where b.PRD_KIND like '%電腦%' and d.end_time is not null and a.啟用狀態='1'
	group by c.team_id,c.team_CName,SUBSTRING(a.預算邊號,4,3)) as c
	on SUBSTRING(a.預算邊號,4,3)=c.預算部門

	LEFT OUTER JOIN dept as d on SUBSTRING(a.預算邊號,4,3)=d.dept_id

	group by d.team_id
	,d.team_CName
	,SUBSTRING(a.預算邊號,4,3)
	,c.已承租主機
	,c.每月主機租金
)
select 區處編號,區處名稱
	,STUFF((
		SELECT ', ' + 已承租主機 
		FROM cte T2
        where T1.區處編號 = T2.區處編號 
		FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
	,1,2,'') as 已承租主機
	,sum(每月主機租金) as 每月主機租金
	,sum(每年總租金) as 每年總租金
from cte T1
group by 區處編號,區處名稱

1.假設期望報表如上圖
2.%電腦% => '%PC%'
3.SQL 如下:
-- 假設樓主 hheyjen 期望報表
SELECT c.team_id as 區處編號,
c.team_CName as 區處名稱,
isnull(count(b.PRD_KIND),0) as 已承租主機,
sum(isnull(b.PRD_NT,0)) as 每月主機租金,
sum(isnull(b.PRD_NT,0))*12 as 每年總租金
FROM device as a
LEFT OUTER JOIN spec as b on a.型號=b.PRD_ID
LEFT OUTER JOIN dept as c on SUBSTRING(a.預算邊號,4,3)=c.dept_id
LEFT OUTER JOIN Form as d on a.BPM單號 = d.BPM_serial
where (b.PRD_KIND like '%電腦%'
or b.PRD_KIND like '%PC%')
and d.end_time is not null and a.啟用狀態='1'
group by c.team_id,c.team_CName
order by c.team_id

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ffc0925796ec10cee0577cc7391de15f