110,538
社区成员
发帖
与我相关
我的任务
分享
--create table
create table ##a
(
aid int identity(1,1) primary key,
bid int,
uname varchar(50),
cid int
)
create table ##b
(
bid int identity(1,1) primary key,
bname varchar(50)
)
create table ##c
(
cid int identity(1,1) primary key,
cname varchar(50)
)
--insert into
insert into ##b(bname) values('部门a')
insert into ##b(bname) values('部门b')
insert into ##b(bname) values('部门c')
insert into ##b(bname) values('部门d')
insert into ##c(cname) values('初中')
insert into ##c(cname) values('高中')
insert into ##c(cname) values('大专')
insert into ##c(cname) values('大学')
insert into ##a(bid,uname,cid) values(1,'u1',1)
insert into ##a(bid,uname,cid) values(2,'u2',2)
insert into ##a(bid,uname,cid) values(3,'u3',3)
insert into ##a(bid,uname,cid) values(4,'u4',4)
insert into ##a(bid,uname,cid) values(1,'u5',4)
insert into ##a(bid,uname,cid) values(2,'u6',4)
insert into ##a(bid,uname,cid) values(3,'u7',4)
insert into ##a(bid,uname,cid) values(4,'u8',4)
--select
select cid,bid,count(1) 'num' into ##d from ##a group by cid,bid order by cid,bid
declare @sql varchar(8000)
set @sql = 'select cid [学历]'
select @sql = @sql + ' , max(case bid when ''' + convert(varchar,bid) + ''' then num else 0 end) [部门' + convert(varchar,bid) + ']'
from (select distinct bid from ##d) as a
set @sql = @sql + ' from ##d group by cid'
exec(@sql)
--create table
create table #a
(
aid int identity(1,1) primary key,
bid int,
uname varchar(50),
cid int
)
create table #b
(
bid int identity(1,1) primary key,
bname varchar(50)
)
create table #c
(
cid int identity(1,1) primary key,
cname varchar(50)
)
--insert into
insert into #b(bname) values('部门a')
insert into #b(bname) values('部门b')
insert into #b(bname) values('部门c')
insert into #b(bname) values('部门d')
insert into #c(cname) values('初中')
insert into #c(cname) values('高中')
insert into #c(cname) values('大专')
insert into #c(cname) values('大学')
insert into #a(bid,uname,cid) values(1,'u1',1)
insert into #a(bid,uname,cid) values(2,'u2',2)
insert into #a(bid,uname,cid) values(3,'u3',3)
insert into #a(bid,uname,cid) values(4,'u4',4)
insert into #a(bid,uname,cid) values(1,'u5',4)
insert into #a(bid,uname,cid) values(2,'u6',4)
insert into #a(bid,uname,cid) values(3,'u7',4)
insert into #a(bid,uname,cid) values(4,'u8',4)
--select
select
case cid when 1 then '初中' when 2 then '高中' when 3 then '大专' when 4 then '大学' end '学历',
sum(case bid when 1 then 1 else 0 end) '部门a',
sum(case bid when 2 then 1 else 0 end) '部门b',
sum(case bid when 3 then 1 else 0 end) '部门c',
sum(case bid when 4 then 1 else 0 end) '部门d'
from #a group by cid order by cid
--create table
create table #a
(
aid int identity(1,1) primary key,
bid int,
uname varchar(50),
cid int
)
create table #b
(
bid int identity(1,1) primary key,
bname varchar(50)
)
create table #c
(
cid int identity(1,1) primary key,
cname varchar(50)
)
--insert into
insert into #b(bname) values('b1')
insert into #b(bname) values('b2')
insert into #b(bname) values('b3')
insert into #b(bname) values('b4')
insert into #c(cname) values('部门a')
insert into #c(cname) values('部门b')
insert into #c(cname) values('部门c')
insert into #c(cname) values('部门d')
insert into #a(bid,uname,cid) values(1,'初中',1)
insert into #a(bid,uname,cid) values(2,'高中',2)
insert into #a(bid,uname,cid) values(3,'大专',3)
insert into #a(bid,uname,cid) values(4,'大学',4)
insert into #a(bid,uname,cid) values(1,'大学',4)
insert into #a(bid,uname,cid) values(2,'大学',4)
insert into #a(bid,uname,cid) values(3,'大学',4)
insert into #a(bid,uname,cid) values(4,'大学',4)
--select
select
cid '学历',
sum(case bid when 1 then 1 else 0 end) '部门a',
sum(case bid when 2 then 1 else 0 end) '部门b',
sum(case bid when 3 then 1 else 0 end) '部门c',
sum(case bid when 4 then 1 else 0 end) '部门d'
from #a group by cid order by cid