高手来，数据表统计问题
我的数据表是一张每月值班表,记录每天各个岗位是谁值班.一个岗位只有一人值班
我现在想统计每个月每个人在每个岗位上值班的次数(周六 周天另外统计).
表样式:
年 月 日 星期 岗位1 岗位2 岗位3 岗位4
2007 10 11 5 黄 张 ... ...
2007 10 12 6 张 黄
输出样式大概是这样
姓名 岗位1 岗位2 岗位3 周六(日)
黄 2次 1次 3次 2次
请问各路 大虾 帮我出个主意 该怎么查询 比较好
[解决办法]
- SQL code
动态得到，但是没有链接成一个表create table tb(y int,m int ,d int,w int,gw1 varchar(10),gw2 varchar(10), gw3 varchar(10))insert tbselect 2007,2,3,1,'黄','王','李' union allselect 2007,2,4,2,'张','张','张' union allselect 2007,2,8,6,'王','黄','张' union allselect 2007,3,1,4,'张','王','黄' union allselect 2007,3,4,7,'李','张','黄' union allselect 2007,4,2,1,'张','黄','黄' union allselect 2007,4,3,2,'黄','王','王' union allselect 2007,5,1,2,'黄','张','张' union allselect 2007,5,2,3,'王','张','王' union allselect 2007,5,3,4,'李','王','李' union allselect 2007,5,4,5,'王','黄','黄' union allselect 2007,5,5,6,'张','王','黄' union allselect 2007,5,6,7,'黄','张','王' union allselect 2007,5,7,1,'张','张','张' union allselect 2007,6,7,1,'王','黄','李'select distinct gw as [name], identity(int,1,1) as id into #tempTable from (select gw1 as gw from tb union all select gw2 as gw from tb union all select gw3 as gw from tb) as tdeclare @i intset @i=(select count(*) from #tempTable)declare @k intset @k=1declare @sql varchar(500) while @i>=@k begin set @sql = 'Select Top 1 * into #tempTable' + convert(varchar(5),@k)+ ' From #tempTable Where id in (Select Top '+ convert(varchar(5),@k)+' id From #tempTable) Order by id desc' set @sql = @sql +' select #tempTable'+ convert(varchar(5),@k)+ '.[name], sum(case when gw1=#tempTable'+ convert(varchar(5),@k)+ '.[name] then 1 else 0 end) as gw1,sum(case when gw2=#tempTable'+ convert(varchar(5),@k)+ '.[name] then 1 else 0 end) as gw2,sum(case when gw3=#tempTable'+ convert(varchar(5),@k)+ '.[name] then 1 else 0 end) as gw3, sum(case when gw2=#tempTable'+ convert(varchar(5),@k)+ '.[name] and (w=6 or w=7) then 1 else 0 end) as '+ char(39)+'周六、日'+ char(39)+' from tb, #tempTable'+ convert(varchar(5),@k)+' group by name drop table #tempTable'+ convert(varchar(5),@k) set @k=@k+1 exec(@sql) enddrop table tb,#tempTable/*name gw1 gw2 gw3 周六、日---------- ----------- ----------- ----------- -----------黄 4 4 5 1name gw1 gw2 gw3 周六、日---------- ----------- ----------- ----------- -----------李 2 0 3 0name gw1 gw2 gw3 周六、日---------- ----------- ----------- ----------- -----------王 4 5 3 1name gw1 gw2 gw3 周六、日---------- ----------- ----------- ----------- -----------张 5 6 4 2*/
[解决办法]
借用楼上的数据:
create table tb(y int,m int ,d int,w int,gw1 varchar(10),gw2 varchar(10), gw3 varchar(10))
insert tb
select 2007,2,3,1,'黄','王','李' union all
select 2007,2,4,2,'张','张','张' union all
select 2007,2,8,6,'王','黄','张' union all
select 2007,3,1,4,'张','王','黄' union all
select 2007,3,4,7,'李','张','黄' union all
select 2007,4,2,1,'张','黄','黄' union all
select 2007,4,3,2,'黄','王','王' union all
select 2007,5,1,2,'黄','张','张' union all
select 2007,5,2,3,'王','张','王' union all
select 2007,5,3,4,'李','王','李' union all
select 2007,5,4,5,'王','黄','黄' union all
select 2007,5,5,6,'张','王','黄' union all
select 2007,5,6,7,'黄','张','王' union all
select 2007,5,7,1,'张','张','张' union all
select 2007,6,7,1,'王','黄','李'
select a.gw,isnull(a.gw1,0) gw1,isnull(b.gw2,0) gw2,isnull(c.gw3,0) gw3 ,isnull(sum(a.ts+b.ts+c.ts),0) [周六、日] from
(select a.gw1 as gw ,count(a.gw1) gw1, b.ts from tb a left join (select gw1 as gw ,count(*) as ts from tb where w=6 or w= 7 group by gw1) b on a.gw1=b.gw group by a.gw1,b.ts)a left join
(select a.gw2 as gw ,count(a.gw2) gw2, b.ts from tb a left join (select gw2 as gw ,count(*) as ts from tb where w=6 or w= 7 group by gw2) b on a.gw2=b.gw group by a.gw2,b.ts)b on a.gw=b.gw inner join
(select a.gw3 as gw ,count(a.gw3) gw3, b.ts from tb a left join (select gw3 as gw ,count(*) as ts from tb where w=6 or w= 7 group by gw3) b on a.gw3=b.gw group by a.gw3,b.ts)c on a.gw=c.gw
group by a.gw,a.gw1,b.gw2,c.gw3
结果:
name gw1 gw2 gw3 周六、日
黄4454
李2030
王4533
张5644