iT邦幫忙

0

詢問SQL 指令

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

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.每月主機租金

To: hheyjen
暐翰大大 幫你解決了問題後,別忘了按下『最佳解答』按鈕。這樣下一個遇到相同問題的人才知道這個問題『已解決』。
hheyjen iT邦新手 5 級 ‧ 2018-11-26 10:22:34 檢舉
暐翰大大 並沒有幫我解決問題哦,是我自己找到問題
hheyjen iT邦新手 5 級 ‧ 2018-11-26 10:37:38 檢舉
多第8行指令後,是我要的結果,謝謝各位的幫忙.
SELECT
d.team_CName as 區處名稱
,isnull(c.已承租主機,0) as 已承租主機
,(isnull(c.每月主機租金,0))*12 as每年總租金
FROM device_list as a
LEFT JOIN
specificationList as b on a.型號=b.PRD_ID
LEFT JOIN deptList as d on SUBSTRING(a.預算邊號,4,3)=d.dept_id
LEFT JOIN
(SELECT c.team_CName,count(b.PRD_KIND) as 已承租主機,sum(b.PRD_NT) as 每月主機租金
FROM device_list as a
LEFT JOIN specificationList as b on a.型號=b.PRD_ID
LEFT JOIN deptList as c on SUBSTRING(a.預算邊號,4,3)=c.dept_id
LEFT JOIN FormDetails as d on a.BPM單號 = d.BPM_serial
where b.PRD_ID like '%PC%' and d.end_time is not null and a.啟用狀態='1'
group by c.team_CName) as c on d.team_CName=c.team_CName

group by
d.team_CName
,c.已承租主機
,c.每月主機租金

2 個回答

1
暐翰
iT邦大師 4 級 ‧ 2018-11-20 16:36:46

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

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

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


更新問題:

https://ithelp.ithome.com.tw/upload/images/20181120/20108157AsrA8Uz1eW.png

上面的圖是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 區處編號,區處名稱
看更多先前的回應...收起先前的回應...
hheyjen iT邦新手 5 級 ‧ 2018-11-20 16:57:51 檢舉

https://ithelp.ithome.com.tw/upload/images/20181120/20108157AsrA8Uz1eW.png

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

暐翰 iT邦大師 4 級 ‧ 2018-11-20 17:06:09 檢舉

可以使用GroupBy合併成一筆,但是你要考慮那些欄位要保留,舉例,像是要保留租金需要加總,或是平均處理

你期望得到那些欄位? hheyjen

hheyjen iT邦新手 5 級 ‧ 2018-11-20 17:21:43 檢舉

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

暐翰 iT邦大師 4 級 ‧ 2018-11-20 17:55:50 檢舉

我更新回答了,你看一下有問題再我說。
hheyjen

rogeryao iT邦研究生 5 級 ‧ 2018-11-20 22:14:19 檢舉

to 暐翰
這麼長的 SQL 你都逗得起來......../images/emoticon/emoticon12.gif
可否貼張資料跑出來的示意圖,謝謝啦

暐翰 iT邦大師 4 級 ‧ 2018-11-20 22:23:22 檢舉

TO rogeryao
你可以看到cte裡面SQL是他原先的查詢
我只是在他查完的資料,照他需求再作group by加工
所以,沒有示意圖 XD
但答案應該是他需要的

rogeryao iT邦研究生 5 級 ‧ 2018-11-20 22:56:05 檢舉

to 暐翰
我的直覺是像這張圖(因為預算部門不要了,總管理處要合成一筆),真是猜不透...
/images/emoticon/emoticon06.gif
https://ithelp.ithome.com.tw/upload/images/20181120/2008502178yxe6ZEy4.png

hheyjen iT邦新手 5 級 ‧ 2018-11-21 09:21:38 檢舉

to 暐翰
出現以下訊息,看不出來第一行哪裏有錯?
訊息 245,層級 16,狀態 1,行 1
將 varchar 值 ', ' 轉換成資料類型 int 時,轉換失敗。

hheyjen iT邦新手 5 級 ‧ 2018-11-21 09:24:13 檢舉

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

kuosheng iT邦新手 5 級 ‧ 2018-11-21 13:17:16 檢舉

佩服萬分,都不用確認業務羅輯,就可以直接改sql 語句,真是甘拜下風。

暐翰 iT邦大師 4 級 ‧ 2018-11-21 13:51:39 檢舉

出現以下訊息,看不出來第一行哪裏有錯?
訊息 245,層級 16,狀態 1,行 1
將 varchar 值 ', ' 轉換成資料類型 int 時,轉換失敗。

hheyjen 麻煩截圖錯誤畫面,需要帶行號

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

會簡單。

hheyjen iT邦新手 5 級 ‧ 2018-11-22 14:51:33 檢舉

我自己解決了,謝謝您

0
rogeryao
iT邦研究生 5 級 ‧ 2018-11-22 00:39:10

https://ithelp.ithome.com.tw/upload/images/20181122/20085021OFuF5oF4g9.png
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

看更多先前的回應...收起先前的回應...
hheyjen iT邦新手 5 級 ‧ 2018-11-22 09:45:06 檢舉

TO rogeryao,謝謝您.
除了要PC(b.PRD_KIND like '%PC%')若要再加入b.PRD_KIND like '%PC%'和b.PRD_KIND like '%PC%'要如何寫SQL指令?

https://ithelp.ithome.com.tw/upload/images/20181122/20108157nMGUFBzKVU.png

rogeryao iT邦研究生 5 級 ‧ 2018-11-22 10:12:26 檢舉

如上式(已修改),
若是 b.PRD_KIND 可能會有'%電腦%' 或 '%PC%' 就改成
where (b.PRD_KIND like '%電腦%'
or b.PRD_KIND like '%PC%')

模擬 SQL 的網頁中文顯示會有問題 , 所以才 %電腦% => '%PC%'

hheyjen iT邦新手 5 級 ‧ 2018-11-22 11:10:38 檢舉

筆電和螢幕要分開顥示如上圖,要分已承租螢幕、每月螢幕租金、已承租筆電、每月筆電租金,要如何下SQL指令?

rogeryao iT邦研究生 5 級 ‧ 2018-11-22 12:06:46 檢舉

假設在 device 內有一個欄位 [可承租數量] , 這樣才可算出[尚可承租數量]
SQL 指令太長 , 請參考以下連結
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d03688f0909fafb9f2f744dadf783105

我要發表回答

立即登入回答