两个表关联汇总，行变列的问题

Question

有两个表，一个员工表，一个是上班或者休息的工作安排表，表结构如下：（每个数据库可能稍微不太一样）
create table employee (
userId int,
userName varchar(20),
primary key (userId)
);create table shift (
shiftId int,
userId int,
shiftDate date, ----日期
shift varchar(20),  ----当天的上班或者休息的类型，如WD(Working Day)/OD(Off Day)/AL(Annual Leave)等等，用缩写。
primary key (shiftId)
);
数据如下：employee
--------------------------
userId      userName 
1           eric
2           jacky
3           sandy
4           tommyshift
----------------------------
shiftId     userId      shiftDate     shift 
1           1           2007-1-1      WD
2           1           2007-1-3      WD
3           1           2007-1-6      WD
4           1           2007-1-12     OD
5           2           2007-1-1      WD
6           2           2007-1-5      OD
7           2           2007-1-8      AL
8           4           2007-1-1      AL我想得到的数据结果为：userId  count(WD)    count(AL)      count(OD)
1       3            0              1 
2       1            0              1
3       0            0              0
4       0            1              1
就是把行经过汇总变成列，请问如何实现，谢谢！！！

Answer 1

那几个shift是固定的，所以可以直接写在sql语句里面，不要用临时表之类的

Answer 2

状态应该固定的seelct a.userId,
sum(case when b.shift='WD' then 1 else 0 end) as [count(WD)],
sum(case when b.shift='AL' then 1 else 0 end) as [count(AL)],
sum(case when b.shift='OD' then 1 else 0 end) as [count(OD)]
from employee a left join shift b
on a.userId=b.userId

Answer 3

declare @sql varchar(8000)
set @sql=''select @sql=@sql+',sum(case b.shift when '''+shift+''' then 1 else 0 end) as [count('+shift+')]'
from shift group by shiftset @sql='select a.userId'+@sql+' from employee a left join shift b on a.userId=b.userId group by a.userId'exec(@sql)

Answer 4

修正下:select a.userId,
sum(case when b.shift='WD' then 1 else 0 end) as [count(WD)],
sum(case when b.shift='AL' then 1 else 0 end) as [count(AL)],
sum(case when b.shift='OD' then 1 else 0 end) as [count(OD)]
from employee a left join shift b
on a.userId=b.userId
group by a.userId

Answer 5

select userId,sum(case when shift='WD' then 1 else 0 end)[count(WD)],
sum(case when shift='AL' then 1 else 0 end)[count(WD)],
sum(case when shift='AL' then 1 else 0 end)[count(WD)]
group by userId

Answer 6

create table employee (
userId int,
userName varchar(20),
primary key (userId)
);create table shift (
shiftId int,
userId int,
shiftDate datetime,
shift varchar(20),
primary key (shiftId));insert into employee select 1,'eric '
insert into employee select 2,'jacky'
insert into employee select 3,'sandy'
insert into employee select 4,'tommy'insert into shift select 1,1,'2007-1-1 ','WD'
insert into shift select 2,1,'2007-1-3 ','WD'
insert into shift select 3,1,'2007-1-6 ','WD'
insert into shift select 4,1,'2007-1-12','OD'
insert into shift select 5,2,'2007-1-1 ','WD'
insert into shift select 6,2,'2007-1-5 ','OD'
insert into shift select 7,2,'2007-1-8 ','AL'
insert into shift select 8,4,'2007-1-1 ','AL'
go
declare @sql varchar(8000)
set @sql=''select @sql=@sql+',sum(case b.shift when '''+shift+''' then 1 else 0 end) as [count('+shift+')]'
from shift group by shiftset @sql='select a.userId'+@sql+' from employee a left join shift b on a.userId=b.userId group by a.userId'exec(@sql)
go/*
userId      count(AL)   count(OD)   count(WD)   
----------- ----------- ----------- ----------- 
1           0           1           3
2           1           1           1
3           0           0           0
4           1           0           0
*/drop table employee,shift
go

Answer 7

create table employee (userId int,userName varchar(20),primary key (userId));
insert into employee select 1,'eric '
insert into employee select 2,'jacky'
insert into employee select 3,'sandy'
insert into employee select 4,'tommy'create table shift (shiftId int,userId int,shiftDate datetime,shift varchar(20),primary key (shiftId));
insert into shift select 1,1,'2007-1-1 ','WD'
insert into shift select 2,1,'2007-1-3 ','WD'
insert into shift select 3,1,'2007-1-6 ','WD'
insert into shift select 4,1,'2007-1-12','OD'
insert into shift select 5,2,'2007-1-1 ','WD'
insert into shift select 6,2,'2007-1-5 ','OD'
insert into shift select 7,2,'2007-1-8 ','AL'
insert into shift select 8,4,'2007-1-1 ','AL'
goselect 
    a.userId,
    sum(case b.shift when 'AL' then 1 else 0 end) as [count(AL)],
    sum(case b.shift when 'OD' then 1 else 0 end) as [count(OD)],
    sum(case b.shift when 'WD' then 1 else 0 end) as [count(WD)] 
from 
    employee a 
left join 
    shift b 
on 
    a.userId=b.userId 
group by 
    a.userId
go/*
userId      count(AL)   count(OD)   count(WD)   
----------- ----------- ----------- ----------- 
1           0           1           3
2           1           1           1
3           0           0           0
4           1           0           0
*/drop table employee,shift
go

Answer 8

--建立测试环境
create table #employee(userId int,userName varchar(20))
insert #employee(userId,userName)
select '1','eric' union all
select '2','jacky' union all
select '3','sandy' union all
select '4','tommy'
go
create table #shift(shiftId int,userId int,shiftDate datetime,shift varchar(20))
insert #shift(shiftId,userId,shiftDate,shift)
select '1','1','2007-1-1','WD' union all
select '2','1','2007-1-3','WD' union all
select '3','1','2007-1-6','WD' union all
select '4','1','2007-1-12','OD' union all
select '5','2','2007-1-1','WD' union all
select '6','2','2007-1-5','OD' union all
select '7','2','2007-1-8','AL' union all
select '8','4','2007-1-1','AL'
go
--执行测试语句
select e.userId
      ,sum(case when shift = 'wd' then 1 else 0 end) as [count(WD)]
      ,sum(case when shift = 'al' then 1 else 0 end) as [count(al)]
      ,sum(case when shift = 'od' then 1 else 0 end) as [count(od)]
from #shift s
right join #employee e on e.userid = s.userid
group by e.userid
go
--删除测试环境
drop table #employee,#shift
go
/*--测试结果userId      count(WD)   count(al)   count(od)   
----------- ----------- ----------- ----------- 
1           3           0           1
2           1           1           1
3           0           0           0
4           0           1           0(4 row(s) affected)
*/

Answer 9

libin_ftsafe(子陌红尘：TS for Banking Card) ( ) 支持这个，两种方法的都写了，有动态的有固定的。