to 暐翰, 請問該改什麼指令才對?
PRD_ID 應該是 specificationList的欄位
你的c子查詢需要select名為PRD_ID欄位，請看以下圖片
子查詢內的select有查詢SUBSTRING(b.PRD_ID,3,2)、b.PRD_KIND b.PRD_NT 欄位後的結果命名為c,和外圍的select 的b join,看不出我的指令哪裡錯了?該如何修正?
三個最笨SQL的指令如下圖該如何用一個SQL搞定,還有期望變成紅框內的結果,SQL指令該如何下?
你用子查詢把哪段包起來之後
又將 b.PRD_ID as 項目
哪你 應該 要改成 c.項目 吧
改成這樣試試看
group by SUBSTRING(b.PRD_ID,3,2)) as c on SUBSTRING(b.PRD_ID,3,2)=c.項目
改了後出現錯誤訊息如下:
訊息 207，層級 16，狀態 1，行 8
無效的資料行名稱 'PRD_ID'。
訊息 207，層級 16，狀態 1，行 13
無效的資料行名稱 'PRD_ID'。
我改的SQL指令如下:
SELECT
SUBSTRING(b.PRD_ID,3,2) 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
(SELECT SUBSTRING(c.PRD_ID,3,2) as 項目,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 FormDetails as c on a.BPM單號 = c.BPM_serial
where b.PRD_ID like '%PC%' and c.end_time is not null and a.啟用狀態='1'
group by SUBSTRING(b.PRD_ID,3,2)) as c on SUBSTRING(b.PRD_ID,3,2)=SUBSTRING(c.PRD_ID,3,2)
group by
SUBSTRING(b.PRD_ID,3,2)
,c.已承租主機
hheyjen 因為你改變欄位名稱了，原本寫法改成圖片這樣
多第８行指令後，是我要的結果，謝謝各位的幫忙．
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.每月主機租金
iThome鐵人賽
看影片追技術