现在有如下一张表a_AttendData
UserID Date Type
1001 2006-8-11 1
1002 2006-8-13 2
1001 2006-8-13 1
1001 2006-8-15 2现在需要根据 Date和UserID统计Type的类型总数。
比如我要统计 2006-8-11到2006-8-13号的数据,将得到
UserID Natural Late ...
1001 2 0 ...
1002 0 1 ...
在SQL中用如下语句可以得出正确结果,但在ACCESS数据库中就会提示 未定义的错误或者操作符丢失,请各位指教Select distinct UserID ,
Natural=sum(case when type=1 then 1 else 0 end),
Late=sum(case when type=2 then 1 else 0 end),
LeaveEarly=sum(case when type=3 then 1 else 0 end),
Absent=sum(case when type=4 then 1 else 0 end),
Leave=sum(case when type=5 then 1 else 0 end),
Evection=sum(case when type=6 then 1 else 0 end),
Overtime=sum(case when type=7 then 1 else 0 end),
Abnormity=sum(case when type=8 then 1 else 0 end)
from a_AttendData group by UserID
UserID Date Type
1001 2006-8-11 1
1002 2006-8-13 2
1001 2006-8-13 1
1001 2006-8-15 2现在需要根据 Date和UserID统计Type的类型总数。
比如我要统计 2006-8-11到2006-8-13号的数据,将得到
UserID Natural Late ...
1001 2 0 ...
1002 0 1 ...
在SQL中用如下语句可以得出正确结果,但在ACCESS数据库中就会提示 未定义的错误或者操作符丢失,请各位指教Select distinct UserID ,
Natural=sum(case when type=1 then 1 else 0 end),
Late=sum(case when type=2 then 1 else 0 end),
LeaveEarly=sum(case when type=3 then 1 else 0 end),
Absent=sum(case when type=4 then 1 else 0 end),
Leave=sum(case when type=5 then 1 else 0 end),
Evection=sum(case when type=6 then 1 else 0 end),
Overtime=sum(case when type=7 then 1 else 0 end),
Abnormity=sum(case when type=8 then 1 else 0 end)
from a_AttendData group by UserID
with Table do
begin
close;
SQL.Clear;
SQL.Add('Select distinct UserID , ');
SQL.Add('(select Name from Users where ID=a_AttendData.UserID)as UserName,');
SQL.Add('sum(iif(type=1,1,0)) as Natural,');
SQL.Add('sum(iif(type=2,1,0)) as Late,');
SQL.Add('sum(iif(type=3,1,0)) as LeaveEarly,');
SQL.Add('sum(iif(type=4,1,0)) as Absent,');
SQL.Add('sum(iif(type=5,1,0)) as Leave,');
SQL.Add('sum(iif(type=6,1,0)) as Evection,');
SQL.Add('sum(iif(type=7,1,0)) as Overtime,');
SQL.Add('sum(iif(type=8,1,0)) as Abnormity from a_AttendData ');
SQL.Add(' where AttendDate between :aa and :bb ');
Parameters.ParamByName('aa').Value := formatdatetime('yyyy-MM-dd',dBeginDate.Date);
Parameters.ParamByName('bb').Value := formatdatetime('yyyy-MM-dd',dEndDate.Date); if cDepartment.Items.Text <> '<所有>' then //选择具体部门时
begin
if cUser.ItemIndex <= 0 then //不选择具体员工时
begin
Close;
SQL.Add(' and UserID in(select ID from Users where DepartmentID in(select ID from Department where Name = '''+cDepartment.Text+''' )) group by UserID');
Open;
end
else //选择具体员工时
begin
Close;
SQL.Add(' and UserID in (select ID from Users where Name='''+cUser.Text+''') group by UserID');
Open;
end;
end
else //不选择具体部门时
begin
if cUser.ItemIndex <= 0 then //不选择具体员工时
begin
Close;
SQL.Add('group by UserID');
Open;
end
else //选择具体员工时
begin
Close;
SQL.Add(' and UserID in (select ID from Users where Name='''+cUser.Text+''') group by UserID');
Open;
end;
end;
end;
这句有问题吧,Access只能支持标准的SQL语言,其它很多东西是不能支持的.楼主怎么不直接用SQLSERVER呢
有没有人遇到过相似的情况啊?
明天过来结贴了
我也想用SQL,但公司要做成单机版的,没办法啊
把Natural替换掉就OK了。