iT邦幫忙

0

SQL語法如何修改

sql

不好意思,上網詢問SQL語法,小妹功力不好.一直無法走出這個迴圈,只好上網求助,請各位前輩指導一下,謝謝

圖片裡有語法出來的內容及想要作的內容
https://imgur.com/a/GuqmurW
語法如下:

select  distinct   dirdept.Code 上級部門編號,dirdept.Name 上級部門名稱,
',,,'+isnull(stuff((
		select   ',,,' +item.Name+'計算值'+replace(CONVERT(nvarchar(20),cast(sum(detail.ItemValue) as money),1),'.00','') 	
		from SalaryResult result,SalaryResultDetail detail,SalaryItem item,Employee emp,SalaryMonth , Department dept 
		where result.SalaryResultId = detail.SalaryResultId
		and result.EmployeeId = emp.EmployeeId
		and result.DepartmentId = Department.DepartmentId
		and detail.SalaryItemId = item.SalaryItemId
		and result.SalaryMonthId = SalaryMonth.SalaryMonthId
		and Department.ParentId = dept.DepartmentId
	and	  (SalaryMonth.SalaryYear = '2018') AND (SalaryMonth.Month = '8') 
	AND   (result.PayTimeNo = '2')
	and Department.code LIKE 'F%'
	AND item.name in ('底薪','伙食津貼') 
		and item.IsSalary = 1
		and detail.ItemValue <> 0
		group by item.ItemID,item.Name,dept.code
		order by item.ItemID for xml path('')),1,3,''),'') +
		(
		select 
		isnull(case when SUM(result.LabourEmpFee) <>0 then ',,,勞保費計算值'+replace(CONVERT(nvarchar(20),cast(sum(result.LabourEmpFee) as money),1),'.00','') end,'') +
		isnull(case when SUM(result.HealthFee) <>0 then ',,,健保費計算值'+replace(CONVERT(nvarchar(20),cast(sum(result.HealthFee) as money),1),'.00','') end,'')
		
		from SalaryResult result 
		  left join Employee emp on result.EmployeeId = emp.EmployeeId
		  left join SalaryMonth on result.SalaryMonthId = SalaryMonth.SalaryMonthId
		  left join Department dept1  on  Department.ParentId = dept1.DepartmentId
		 
		where result.DepartmentId = Department.DepartmentId
		and	  (SalaryMonth.SalaryYear = '2018') AND (SalaryMonth.Month = '8') 
	AND   (result.PayTimeNo = '2')
	and Department.code LIKE 'F%'
	group by dept1.code
		) 部門計算項 ,
		'應發合計' 應發合計名,'應扣合計' 應扣合計名,'實發' 實發合計名,dirdept.name 上級部門
		
	from SalaryResult
  left join Employee on SalaryResult.EmployeeId = Employee.EmployeeId
  left join Department on SalaryResult.DepartmentId = Department.DepartmentId
  left join Corporation on Department.CorporationId = Corporation.CorporationId
  left join SalaryMonth on SalaryResult.SalaryMonthId = SalaryMonth.SalaryMonthId 
  left join Department dirdept on Department.ParentId = dirdept.DepartmentId 
  
  where 		  (SalaryMonth.SalaryYear = '2018') AND (SalaryMonth.Month = '8') 	AND   (SalaryResult.PayTimeNo = '2') 	and Department.code LIKE 'F%'

1 個回答

0
rogeryao
iT邦研究生 4 級 ‧ 2018-12-04 16:43:16

猜的 :

group by item.ItemID,item.Name,dept.code
改成
group by item.ItemID,item.Name,Department.code
group by dept1.code
改成
group by Department.code
看更多先前的回應...收起先前的回應...
cat87031 iT邦新手 4 級 ‧ 2018-12-04 16:48:01 檢舉

結果出來也不是,謝謝

rogeryao iT邦研究生 4 級 ‧ 2018-12-04 16:52:35 檢舉

若是把上式改成 group by dept.code 是否可以 ?

cat87031 iT邦新手 4 級 ‧ 2018-12-04 16:54:53 檢舉

select 內有item.Name,group by不能拿掉item.name

rogeryao iT邦研究生 4 級 ‧ 2018-12-04 17:05:42 檢舉

group by item.ItemID,item.Name,dept.code
group by dept1.code
以上兩式的 code 可能有一個(或兩個)必須為 "上級部門編號"

cat87031 iT邦新手 4 級 ‧ 2018-12-04 17:17:28 檢舉

dept.code 及dept1.code 都是"上級部門編號"

rogeryao iT邦研究生 4 級 ‧ 2018-12-04 17:49:30 檢舉

已更新,如上式

我要發表回答

立即登入回答