declare @year integer,@month integer set @year = 2003,@month = 7 select tb1.number,tb2.name,tb1.am,tb1.pm from (select number,count((case when am = '出勤' then 1 else 0 end)) as am, count((case when pm = '出勤' then 1 else 0 end)) as pm, from tablename whenre year(date) = @year and month(date) = @month group by name) as tb1, (select distinct number,name from tablename) as tb2 where tb1.number = tb2.number
select number,name,count(am) as am,count(pm) as pm where am='出勤' or pm='出勤' group by number,name
更正: count 改 sum
select identity(int,1,1) as id, name,count(am) am,count(pm) pm into #t from yourtable where am='出勤' and pm='出勤' group by name select * from #t drop table #T
以下语句我亲自调试通过。 select tb1.number,tb1.name,tb2.am,tb2.pm from (select number, sum((case when am = '出勤' then 1 else 0 end)) as am, sum((case when pm = '出勤' then 1 else 0 end)) as pm from member where year(date) = '2003' and month(date) = '07' group by number) as tb2, (select distinct number,name from member) as tb1 where tb1.number=tb2.number
select number,name,sum(case am when '出勤' then 1 else 0 end)as am,sum(case pm when '出勤' then 1 else 0 end) as pmfrom yourtable where month(date)=7 and year(date)=2003 group by number,name
select a.number,a.name,a.am,b.am from( sel number,name,count(am) as am from 你的表 where am='出勤' group by number,name ) a full join ( sel number,name,count(pm) as pm from 你的表 where pm='出勤' group by number,name ) b on a.number=b.number
select b.number,b.name,count(b.am) as am,count(b.pm) as pm from (select * from yourtable where month(date)=7 and year(date)=2003) b where am='出勤' or pm='出勤' group by number
select number,[name],count(case when am='出勤' then 1 else null end) as am, count(case when pm='出勤' then 1 else null end) as am from tablename where month([date])=7 group by number,[name] order by number
set @year = 2003,@month = 7
select tb1.number,tb2.name,tb1.am,tb1.pm from
(select number,count((case when am = '出勤' then 1 else 0 end)) as am,
count((case when pm = '出勤' then 1 else 0 end)) as pm,
from tablename whenre year(date) = @year and month(date) = @month
group by name) as tb1,
(select distinct number,name from tablename) as tb2
where tb1.number = tb2.number
where am='出勤' or pm='出勤'
group by number,name
from yourtable
where am='出勤' and pm='出勤' group by name select * from #t
drop table #T
select tb1.number,tb1.name,tb2.am,tb2.pm
from
(select number,
sum((case when am = '出勤' then 1 else 0 end)) as am,
sum((case when pm = '出勤' then 1 else 0 end)) as pm
from member
where year(date) = '2003' and month(date) = '07'
group by number)
as tb2,
(select distinct number,name from member)
as tb1
where tb1.number=tb2.number
group by number,name
from(
sel number,name,count(am) as am
from 你的表 where am='出勤'
group by number,name
) a full join (
sel number,name,count(pm) as pm
from 你的表 where pm='出勤'
group by number,name
) b on a.number=b.number
from (select * from yourtable where month(date)=7 and year(date)=2003) b
where am='出勤' or pm='出勤'
group by number
count(case when pm='出勤' then 1 else null end) as am
from tablename
where month([date])=7
group by number,[name]
order by number