教师号 星期号 是否有课
１ ２ 有
１ ３ 有
２ １ 有
３ ２ 有`
１ ２ 有
写一条sql语句让你变为这样的表
教师号 星期一 星期二 星期三
１ ２ １
２ １
３ １
各星期下的数字表示：对应的教师在星期几已经排的课数
------解决方案--------------------
create table table10
(
teachID int,
weeksID int,
isbool tinyint
)
go
insert table10(teachid,weeksid,isbool) values(1,2,1)
insert table10(teachid,weeksid,isbool) values(1,3,1)
insert table10(teachid,weeksid,isbool)values(2,1,1)
insert table10(teachid,weeksid,isbool) values(3,2,1)
insert table10(teachid,weeksid,isbool) values(1,2,1)
go
select * from table10
select DISTINCT(a.teachid),
(select count(weeksid) from table10 as b where b.teachid=a.teachid and isbool=1 and weeksid = 1) as '星期一 ',
(select count(weeksid) from table10 as b where b.teachid=a.teachid and isbool=1 and weeksid = 2) as '星期二 ',
(select count(weeksid) from table10 as b where b.teachid=a.teachid and isbool=1 and weeksid = 3) as '星期三 '
from table10 as a
------解决方案--------------------
--搞个动态的
create table tbl
(
teachID int,
weeksID int,
isbool tinyint
)
go
insert tbl(teachid,weeksid,isbool) values(1,2,1)
insert tbl(teachid,weeksid,isbool) values(1,3,1)
insert tbl(teachid,weeksid,isbool)values(2,1,1)
insert tbl(teachid,weeksid,isbool) values(3,2,1)
insert tbl(teachid,weeksid,isbool) values(1,2,1)
declare @v varchar(1000)
set @v= 'select teachId '
select @[email protected]+ ',(select count(weeksid)
from tbl b where b.teachid=a.teachid and isbool=1
and weeksid= ' ' '+rtrim(weeksid)+ ' ' ' )as 星期 '+rtrim(weeksid)
from (select distinct weeksid from tbl ) a
set @[email protected]+ ' from tbl a GROUP BY TEACHID '
exec(@v)
------解决方案--------------------
select 教师号, count(case when (星期号 = 1) and (是否有课 = '有 ') then 教师号 else Null end) as 星期一,
count(case when (星期号 = 2) and (是否有课 = '有 ') then 教师号 else Null end) as 星期二,
count(case when (星期号 = 3) and (是否有课 = '有 ') then 教师号 else Null end) as 星期三
group by 教师号
------解决方案--------------------
select teachid, sum(decode (weeksid,1,1,null )) xq1,
sum(decode (weeksid,2,1,null )) xq2,
sum(decode (weeksid,3,1,null )) xq3
from table10
group by teachid