iT邦幫忙

0

sql 如何個人時數加總小於 全體加總平均時數 的子查詢

https://ithelp.ithome.com.tw/upload/images/20211129/201443513yGO7BskVQ.png
我是寫這樣,寫著邏輯就亂,能否指教一下
select worknumber,sum(workhour) from hourpay group by worknumber
having
sum(workhour) < (select worknumber,round(avg(sumhour),1) as avhour from (select worknumber,sum(workhour) as sumhour from hourpay group by worknumber ) as temptable)

因為每個人加總出的時數 要再加總平均 要和 個人加總平均時數 做比較

2 個回答

4
海綿寶寶
iT邦大神 1 級 ‧ 2021-11-29 09:10:43
最佳解答

如果這不是你要的結果

https://ithelp.ithome.com.tw/upload/images/20211129/20001787aMnCdORVdw.png

請到db fiddle建立你的資料
並用你的資料說明你要做的結果
再等待高手回答

rogeryao iT邦大師 1 級 ‧ 2021-11-29 09:28:15 檢舉
SELECT SUM(workhour)/COUNT(DISTINCT(worknumber)) AS Average
FROM work
1
change
iT邦新手 4 級 ‧ 2021-11-29 15:27:40

簡單弄了個測試, 不知道是不是你想要的(select 有些多餘的欄位, 請自行刪除 ^^)

create table #Work (
  workNo varchar(4),
  workHour int);

insert into #Work (workNo, workHour)
  values ('E001', 9),('E001', 1), ('E002', 19), ('E002', 1),
         ('E003',29),('E003', 1), ('E004', 39), ('E004', 1); 

with List as (
  select workNo, Sum(workHour) as WorkHour, avg(workHour) as AvgHour
    from #work
   group by workNo),
  Total as (select Sum(workHour) as TotalWorkHour, avg(workHour) as TotalAvgHour
    from #Work)
select *
  from List
  cross join Total  
 where List.AvgHour < Total.TotalAvgHour

drop table #Work;

執行結果
https://ithelp.ithome.com.tw/upload/images/20211129/20034543fkHhsJNzmY.jpg

我要發表回答

立即登入回答