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

sql 子查詢

dayday566123 2021-11-29 06:02:24 ‧ 764 瀏覽

分享至

我是寫這樣，寫著邏輯就亂，能否指教一下
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)

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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2021-11-29 09:10:43

最佳解答

如果這不是你要的結果

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

回應 1
分享
檢舉

rogeryao iT邦超人 7 級 ‧ 2021-11-29 09:28:15 檢舉

SELECT SUM(workhour)/COUNT(DISTINCT(worknumber)) AS Average
FROM work

登入發表回應

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;

執行結果