自己解决了,我还是不太懂SQL啊 select b.code, b.name, b.duty, b.introducer,
s.s1, s.s2, s.s3, s.s4, s.s5, s.s6, s.s7, s.s15, s.s67,
(sum(if(weekday(a.date)=0,1,0))) as k1,
(sum(if(weekday(a.date)=1,1,0))) as k2,
(sum(if(weekday(a.date)=2,1,0))) as k3,
(sum(if(weekday(a.date)=3,1,0))) as k4,
(sum(if(weekday(a.date)=4,1,0))) as k5,
(sum(if(weekday(a.date)=5,1,0))) as k6,
(sum(if(weekday(a.date)=6,1,0))) as k7,
(sum(if(weekday(a.date) between 0 and 4,1,0))) as k15,
(sum(if(weekday(a.date) between 5 and 6,1,0))) as k67
from baseinfo b inner join attendance a on b.code=a.code
inner join works w on a.id=w.attendance
inner join contact c on w.id=c.works
inner join
(select b.code,
sum(if(weekday(a.date)=0,1,0)) as s1,
sum(if(weekday(a.date)=1,1,0)) as s2,
sum(if(weekday(a.date)=2,1,0)) as s3,
sum(if(weekday(a.date)=3,1,0)) as s4,
sum(if(weekday(a.date)=4,1,0)) as s5,
sum(if(weekday(a.date)=5,1,0)) as s6,
sum(if(weekday(a.date)=6,1,0)) as s7,
sum(if(weekday(a.date) between 0 and 4,1,0)) as s15,
sum(if(weekday(a.date) between 5 and 6,1,0)) as s67
from baseinfo b inner join attendance a on b.code=a.code
inner join works w on a.id=w.attendance
where (day(a.date) between 23 and 29)
group by b.code) as s on s.code=b.code
where day(a.date) between 23 and 29
group by b.code
HAVING s.s15>=4 and s.s67>=1 and k15>=4 and k67>=1
order by code
s.s1, s.s2, s.s3, s.s4, s.s5, s.s6, s.s7, s.s15, s.s67,
(sum(if(weekday(a.date)=0,1,0))) as k1,
(sum(if(weekday(a.date)=1,1,0))) as k2,
(sum(if(weekday(a.date)=2,1,0))) as k3,
(sum(if(weekday(a.date)=3,1,0))) as k4,
(sum(if(weekday(a.date)=4,1,0))) as k5,
(sum(if(weekday(a.date)=5,1,0))) as k6,
(sum(if(weekday(a.date)=6,1,0))) as k7,
(sum(if(weekday(a.date) between 0 and 4,1,0))) as k15,
(sum(if(weekday(a.date) between 5 and 6,1,0))) as k67
from baseinfo b inner join attendance a on b.code=a.code
inner join works w on a.id=w.attendance
inner join contact c on w.id=c.works
inner join
(select b.code,
sum(if(weekday(a.date)=0,1,0)) as s1,
sum(if(weekday(a.date)=1,1,0)) as s2,
sum(if(weekday(a.date)=2,1,0)) as s3,
sum(if(weekday(a.date)=3,1,0)) as s4,
sum(if(weekday(a.date)=4,1,0)) as s5,
sum(if(weekday(a.date)=5,1,0)) as s6,
sum(if(weekday(a.date)=6,1,0)) as s7,
sum(if(weekday(a.date) between 0 and 4,1,0)) as s15,
sum(if(weekday(a.date) between 5 and 6,1,0)) as s67
from baseinfo b inner join attendance a on b.code=a.code
inner join works w on a.id=w.attendance
where (day(a.date) between 23 and 29)
group by b.code) as s on s.code=b.code
where day(a.date) between 23 and 29
group by b.code
HAVING s.s15>=4 and s.s67>=1 and k15>=4 and k67>=1
order by code
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货