各位GG好，小弟從考勤系統中查詢出某人的出勤記錄，想讓它自動加總它的平時加班時間和假日加班、法寫加班時間。顯示在旁邊。不知道該如何寫。 代碼如下：
procedure TForm1.BitBtn1Click(Sender: TObject);
var sql1:string;
VAR BDAT,EDAT:TDATE;
begin
BDAT:=DATETIMEPICKER1.Date;
EDAT:=DATETIMEPICKER2.Date;
query1.SQL.Clear;
query1.SQL.Add('select a.emp_no,b.emp_name,a.att_date,a.att_dpt,');
query1.SQL.Add('convert(varchar,a.in_time1,120) as in_time1, convert(varchar,a.out_time1,120) as out_time1,');
query1.SQL.Add('convert(varchar,a.in_time2,120) as in_time2, convert(varchar,a.out_time2,120) as out_time2,');
query1.SQL.Add('convert(varchar,a.in_time3,120) as in_time3, convert(varchar,a.out_time3,120) as out_time3,');
query1.SQL.Add('a.work_time as work_time, a.over_time as over_time , a.fdj_over_time as fdj_over_time, a.jr_over_time as jr_over_time');
query1.SQL.Add('from attrecord a, employee b');
query1.SQL.Add('Where a.emp_no = b.emp_no and');
query1.SQL.Add('a.att_date between :DB AND :DE');
query1.SQL.Add('and a.emp_no like :emp_no');
query1.SQL.Add('order by a.att_dpt,a.emp_no');
query1.ParamByName('DB').AsDate:=BDAT;
query1.ParamByName('DE').AsDate:=EDAT;
query1.ParamByName('emp_no').AsString:=edit1.Text;
query1.Prepare;
query1.Open;
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
DB1.DriverName:='MSSQL';
DB1.Params.Clear;
DB1.Params.Add('SERVER NAME=mis-02');
DB1.Params.Add('DATABASE NAME=pis2005');
DB1.Params.Add('USER NAME=sa');
DB1.DatabaseName:='MSQL';
DB1.LoginPrompt:=false;
DB1.Connected:=TRUE;
Query1.DatabaseName:='MSQL';
Query1.RequestLive:=False;
DBGrid1.ReadOnly:=true;
end;
procedure TForm1.BitBtn2Click(Sender: TObject);
var
i:integer;
xlsfile:string;
elcapp,workbook:variant;
begin
savedialog1.Execute;
xlsfile:=savedialog1.filename;
elcapp:=Createoleobject('excel.application');
workbook:=createoleobject('excel.sheet');
workbook:=elcapp.workbooks.add;
elcapp.cells(1,1):='工號';
elcapp.cells(1,2):='姓名';
elcapp.cells(1,3):='出勤日期';
elcapp.cells(1,4):='部門';
elcapp.cells(1,5):='上班1';
elcapp.cells(1,6):='下班1';
elcapp.cells(1,7):='上班2';
elcapp.cells(1,8):='下班2';
elcapp.cells(1,9):='上班3';
elcapp.cells(1,10):='下班3';
elcapp.cells(1,11):='正常上班時間';
elcapp.cells(1,12):='加班時間';
elcapp.cells(1,13):='法定加班';
elcapp.cells(1,14):='假日加班';
query1.First;
for i:=0 to query1.recordcount-1 do
begin
elcapp.cells(i 2,1):=query1.fieldbyname('emp_no').asstring;
elcapp.cells(i 2,2):=query1.fieldbyname('emp_name').asstring;
elcapp.cells(i 2,3):=query1.fieldbyname('att_date').asstring;
elcapp.cells(i 2,4):=query1.fieldbyname('att_dpt').asstring;
elcapp.cells(i 2,5):=query1.fieldbyname('in_time1').asstring;
elcapp.cells(i 2,6):=query1.fieldbyname('out_time1').asstring;
elcapp.cells(i 2,7):=query1.fieldbyname('in_time2').asstring;
elcapp.cells(i 2,8):=query1.fieldbyname('out_time2').asstring;
elcapp.cells(i 2,9):=query1.fieldbyname('in_time3').asstring;
elcapp.cells(i 2,10):=query1.fieldbyname('out_time3').asstring;
elcapp.cells(i 2,11):=query1.fieldbyname('work_time').asstring;
elcapp.cells(i 2,12):=query1.fieldbyname('over_time').asstring;
elcapp.cells(i 2,13):=query1.fieldbyname('fdj_over_time').asstring;
elcapp.cells(i 2,14):=query1.fieldbyname('jr_over_time').asstring;
query1.Next;
end;

查一下 sum(), group by 怎麼用

列出所有人平日加班時間總合
select emp_no, sum(over_time) from employee group by emp_no

------
能力不足，求助於人；有能力時，幫幫別人；如果您滿意答覆，請適時結案！

子曰：問有三種，不懂則問，雖懂有疑則問，雖懂而想知更多則問！