select max(b.workshop) as 课别,max(b.suit) as 组别,b.workdate,a.personid,a.personname,case a.分到率 when 0 then 0 else (sum(a.pay/a.分到率)) end as pay,
max(c.上班时间) as 上班时间
from new员工工资明细 a left join 每日输入明细 b on a.autoid = b.id left join
(select 员工工号,sum(上班时间) as 上班时间 from 员工上班时间 where 日期 between '2009-4-1' and '2009-4-5' group by 员工工号) c
on a.personid = c.员工工号 where workdate between '2009-4-1' and '2009-4-5' and b.suit like '%九线%'
group by b.workdate,a.personid,a.personname提示的错误是
服务器: 消息 8120,级别 16,状态 1,行 1
列 'a.分到率' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
如何可以让分到率为零的情况下, 可以通过SQL语句, 这SQL语句该如何修改呢
max(c.上班时间) as 上班时间
from new员工工资明细 a left join 每日输入明细 b on a.autoid = b.id left join
(select 员工工号,sum(上班时间) as 上班时间 from 员工上班时间 where 日期 between '2009-4-1' and '2009-4-5' group by 员工工号) c
on a.personid = c.员工工号 where workdate between '2009-4-1' and '2009-4-5' and b.suit like '%九线%'
group by b.workdate,a.personid,a.personname提示的错误是
服务器: 消息 8120,级别 16,状态 1,行 1
列 'a.分到率' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
如何可以让分到率为零的情况下, 可以通过SQL语句, 这SQL语句该如何修改呢
1.select a/case when b=0 then 1 else b end as c;2.select case when b=0 then null else a/b end;
select max(g.课别) as 课别,max(g.组别) as 组别,g.workdate,g.personid,g.personname,sum(g.pay) as pay,
max(g.上班时间) as 上班时间
from
(select max(b.workshop) as 课别,max(b.suit) as 组别,b.workdate,a.personid,a.personname,sum(a.pay/a.分到率) as pay,
max(c.上班时间) as 上班时间
from new员工工资明细 a left join 每日输入明细 b on a.autoid = b.id left join
(select 员工工号,sum(上班时间) as 上班时间 from 员工上班时间 where 日期 between '2009-4-1' and '2009-4-5' group by 员工工号) c
on a.personid = c.员工工号 where workdate between '2009-4-1' and '2009-4-5' and b.suit like '%九线%'
--add
and a.分到率<>0
group by b.workdate,a.personid,a.personname
union all
select max(b.workshop) as 课别,max(b.suit) as 组别,b.workdate,a.personid,a.personname,0 as pay,
max(c.上班时间) as 上班时间
from new员工工资明细 a left join 每日输入明细 b on a.autoid = b.id left join
(select 员工工号,sum(上班时间) as 上班时间 from 员工上班时间 where 日期 between '2009-4-1' and '2009-4-5' group by 员工工号) c
on a.personid = c.员工工号 where workdate between '2009-4-1' and '2009-4-5' and b.suit like '%九线%'
--add
and a.分到率=0
group by b.workdate,a.personid,a.personname) g
group by workdate,personid,personname
max(c.上班时间) as 上班时间
from new员工工资明细 a left join 每日输入明细 b on a.autoid = b.id left join
(select 员工工号,sum(上班时间) as 上班时间 from 员工上班时间 where 日期 between '2009-4-1' and '2009-4-5' group by 员工工号) c
on a.personid = c.员工工号 where workdate between '2009-4-1' and '2009-4-5' and b.suit like '%九线%'
group by b.workdate,a.personid,a.personname我改成上面这样子,可以了,不知道结果是不是正确.