select B.Username, sum(case when worid='W1' then 1 else 0 end) as W1, sum(case when worid='W2' then 1 else 0 end) as W2, sum(case when worid='W3' then 1 else 0 end) as W3 from ( select B.userId,B.Username,A.workid from TableB as B left join TableA as A on B.UserID=A.UserID ) c group by userid
上面的错了select B.Username, sum(case when worid='W1' then 1 else 0 end) as W1, sum(case when worid='W2' then 1 else 0 end) as W2, sum(case when worid='W3' then 1 else 0 end) as W3 from ( select B.userId,B.Username,A.workid from TableB as B left join TableA as A on B.UserID=A.UserID ) c group by username
declare @sql varchar(500) declare @courseStr varchar(300) set @sql='select username' set @courseStr='' select @courseStr=@courseStr+',sum(case workid when '''+workid+''' then 1 then 0 end) as '+workid+' ' from (select distinct workid from TableC) C set @sql=@sql+@courseStr+' from (select B.userId,B.Username,A.workid from TableB as B left join TableA as A on B.UserID=A.UserID) D group by userid,username' exec(@sql)这条是动态sql。
with tablea as ( select 1 as id,'u1' userid ,'w1' as workid union all select 2 as id,'u1' userid ,'w2' as workid union all select 3 as id,'u1' userid ,'w1' as workid union all select 4 as id,'u2' userid ,'w2' as workid union all select 5 as id,'u2' userid ,'w2' as workid ), tableb as ( select 'u1' as userid,'王一' as username union all select 'u2' as userid,'李二' as username union all select 'u3' as userid,'赵三' as username ), tablec as ( select 'W1' as workid,'卫生' as workname union all select 'w2' as workid,'服务' as workname union all select 'w3' as workid,'安全' as workname ) , temp as ( select username,tablec.workname,tableb.userid from tableb left join tablea on tableb.userid = tablea.userid left join tablec on tablea.workid = tablec.workid
) select username,卫生,服务,安全 from temp pivot ( count(workname) for workname in (卫生,服务,安全) ) t
如果在结果集dataA 最后一行 求出每一列的和 好算吗 高手
create table #A(id int,userid varchar(10),workid varchar(10)) insert #A select 1 as id,'u1' userid ,'w1' as workid union all select 2 as id,'u1' userid ,'w2' as workid union all select 3 as id,'u1' userid ,'w1' as workid union all select 4 as id,'u2' userid ,'w2' as workid union all select 5 as id,'u2' userid ,'w2' as workid create table #B(userid varchar(10),username varchar(10)) insert #B select 'u1' as userid,'王一' as username union all select 'u2' as userid,'李二' as username union all select 'u3' as userid,'赵三' as username create table #C(workid varchar(10),workname varchar(10)) insert #C select 'W1' as workid,'卫生' as workname union all select 'w2' as workid,'服务' as workname union all select 'w3' as workid,'安全' as workname
declare @s varchar(max)
select @s=ISNULL(@s+',','')+'sum(case when workId='''+workId+''' then 1 else 0 end) as ['+workId+']' from #C group by workId set @s='select username,'+@s+' from #b b left join #a a on a.UserId=b.userID group by b.username with rollup '
-- print @s
exec(@s) ----求和用with rollup
IF OBJECT_ID('TableA') IS NOT NULL DROP TABLE TableA IF OBJECT_ID('TableB') IS NOT NULL DROP TABLE TableB IF OBJECT_ID('TableC') IS NOT NULL DROP TABLE TableC GOCREATE TABLE TableA(ID int,UserID varchar(10),workid varchar(10)) GO INSERT INTO TableA SELECT 1,'U1','W1' UNION ALL SELECT 2,'U1','W2' UNION ALL SELECT 3,'U1','W1' UNION ALL SELECT 4,'U2','W2' UNION ALL SELECT 5,'U2','W2' GOCREATE TABLE TableB(UserID varchar(10),Username nvarchar(10)) GOINSERT INTO TableB SELECT 'U1',N'王一' UNION ALL SELECT 'U2',N'李二' UNION ALL SELECT 'U3',N'赵三' GOCREATE TABLE TableC (workid varchar(10),workname nvarchar(10)) GOINSERT INTO TableC SELECT 'W1',N'卫生' UNION ALL SELECT 'W2',N'服务' UNION ALL SELECT 'W3',N'安全' GODECLARE @fields varchar(max) DECLARE @columns varchar(max) DECLARE @sql varchar(max)SET @fields = '' SET @columns = ''SELECT @fields = @fields + ',ISNULL(T.' + workid + ',0) ' + workid, @columns = @columns + ',[' + workid + ']' FROM TableCSET @sql = 'SELECT b.UserName' + @fields + ' FROM TableB b LEFT JOIN ( SELECT * FROM TableA PIVOT ( COUNT(ID) FOR workid IN (' + SUBSTRING(@columns,2,LEN(@columns)-1) + ') ) P ) T ON b.UserID = T.UserID'PRINT @sql EXEC (@sql) SELECT b.UserName,ISNULL(T.W1,0) W1,ISNULL(T.W2,0) W2,ISNULL(T.W3,0) W3 FROM TableB b LEFT JOIN ( SELECT * FROM TableA PIVOT ( COUNT(ID) FOR workid IN ([W1],[W2],[W3]) ) P ) T ON b.UserID = T.UserID UserName W1 W2 W3 ---------- ----------- ----------- ----------- 王一 2 1 0 李二 0 2 0 赵三 0 0 0
with tablea as ( select 1 as id,'u1' userid ,'w1' as workid union all select 2 as id,'u1' userid ,'w2' as workid union all select 3 as id,'u1' userid ,'w1' as workid union all select 4 as id,'u2' userid ,'w2' as workid union all select 5 as id,'u2' userid ,'w2' as workid ), tableb as ( select 'u1' as userid,'王一' as username union all select 'u2' as userid,'李二' as username union all select 'u3' as userid,'赵三' as username ), tablec as ( select 'W1' as workid,'卫生' as workname union all select 'w2' as workid,'服务' as workname union all select 'w3' as workid,'安全' as workname ) select username,ISNULL(W1,0) AS W1,ISNULL(W2,0) AS W2, ISNULL(W3,0) AS W3 from (select username,tablec.workid,a.counts from (select userid,workid,COUNT(*)as counts from tablea group by userid,workid) as a RIGHT join tableb on a.userid=tableb.userid LEFT join tablec on a.workid=tablec.workid)AS T pivot (sum(counts) for workid in (W1,W2,W3)) AS P --结果 username W1 W2 W3 -------- ----------- ----------- ----------- 李二 0 2 0 王一 2 1 0 赵三 0 0 0(3 行受影响)
如果在结果集dataA 最后一行 求出每一列的和 好算吗 高手 稍作修改declare @sql varchar(500) declare @courseStr varchar(300) set @sql='select username' set @courseStr='' select @courseStr=@courseStr+',sum(case workid when '''+workid+''' then 1 then 0 end) as '+workid+' ' from (select distinct workid from TableC) C set @sql=@sql+@courseStr+' from (select B.userId,B.Username,A.workid from TableB as B left join TableA as A on B.UserID=A.UserID) D group by username with rollup' exec(@sql)
TableBUserID Username
U1 王一
U2 李二
U3 赵三
B表的应该是U 不是PK 不好意思 写错了
sum(case when worid='W1' then 1 else 0 end) as W1,
sum(case when worid='W2' then 1 else 0 end) as W2,
sum(case when worid='W3' then 1 else 0 end) as W3
from (
select B.userId,B.Username,A.workid from TableB as B left join TableA as A on B.UserID=A.UserID
) c group by userid
sum(case when worid='W1' then 1 else 0 end) as W1,
sum(case when worid='W2' then 1 else 0 end) as W2,
sum(case when worid='W3' then 1 else 0 end) as W3
from (
select B.userId,B.Username,A.workid from TableB as B left join TableA as A on B.UserID=A.UserID
) c group by username
declare @courseStr varchar(300)
set @sql='select username'
set @courseStr=''
select @courseStr=@courseStr+',sum(case workid when '''+workid+''' then 1 then 0 end) as '+workid+' '
from (select distinct workid from TableC) C
set @sql=@sql+@courseStr+' from (select B.userId,B.Username,A.workid from TableB as B left join TableA as A on B.UserID=A.UserID) D group by userid,username'
exec(@sql)这条是动态sql。
as
(
select 1 as id,'u1' userid ,'w1' as workid
union all
select 2 as id,'u1' userid ,'w2' as workid
union all
select 3 as id,'u1' userid ,'w1' as workid
union all
select 4 as id,'u2' userid ,'w2' as workid
union all
select 5 as id,'u2' userid ,'w2' as workid
),
tableb as
(
select 'u1' as userid,'王一' as username
union all
select 'u2' as userid,'李二' as username
union all
select 'u3' as userid,'赵三' as username
),
tablec as
(
select 'W1' as workid,'卫生' as workname
union all
select 'w2' as workid,'服务' as workname
union all
select 'w3' as workid,'安全' as workname
) ,
temp as
(
select username,tablec.workname,tableb.userid
from tableb
left join tablea
on tableb.userid = tablea.userid
left join tablec
on tablea.workid = tablec.workid
)
select username,卫生,服务,安全 from temp
pivot
(
count(workname) for workname in (卫生,服务,安全)
)
t
insert #A
select 1 as id,'u1' userid ,'w1' as workid
union all
select 2 as id,'u1' userid ,'w2' as workid
union all
select 3 as id,'u1' userid ,'w1' as workid
union all
select 4 as id,'u2' userid ,'w2' as workid
union all
select 5 as id,'u2' userid ,'w2' as workid
create table #B(userid varchar(10),username varchar(10))
insert #B
select 'u1' as userid,'王一' as username
union all
select 'u2' as userid,'李二' as username
union all
select 'u3' as userid,'赵三' as username
create table #C(workid varchar(10),workname varchar(10))
insert #C
select 'W1' as workid,'卫生' as workname
union all
select 'w2' as workid,'服务' as workname
union all
select 'w3' as workid,'安全' as workname
declare @s varchar(max)
select @s=ISNULL(@s+',','')+'sum(case when workId='''+workId+'''
then 1 else 0 end) as ['+workId+']'
from #C group by workId
set @s='select username,'+@s+' from #b b left join #a a on a.UserId=b.userID group by b.username with rollup '
-- print @s
exec(@s)
----求和用with rollup
IF OBJECT_ID('TableB') IS NOT NULL DROP TABLE TableB
IF OBJECT_ID('TableC') IS NOT NULL DROP TABLE TableC
GOCREATE TABLE TableA(ID int,UserID varchar(10),workid varchar(10))
GO
INSERT INTO TableA
SELECT 1,'U1','W1' UNION ALL
SELECT 2,'U1','W2' UNION ALL
SELECT 3,'U1','W1' UNION ALL
SELECT 4,'U2','W2' UNION ALL
SELECT 5,'U2','W2'
GOCREATE TABLE TableB(UserID varchar(10),Username nvarchar(10))
GOINSERT INTO TableB
SELECT 'U1',N'王一' UNION ALL
SELECT 'U2',N'李二' UNION ALL
SELECT 'U3',N'赵三'
GOCREATE TABLE TableC (workid varchar(10),workname nvarchar(10))
GOINSERT INTO TableC
SELECT 'W1',N'卫生' UNION ALL
SELECT 'W2',N'服务' UNION ALL
SELECT 'W3',N'安全'
GODECLARE @fields varchar(max)
DECLARE @columns varchar(max)
DECLARE @sql varchar(max)SET @fields = ''
SET @columns = ''SELECT @fields = @fields + ',ISNULL(T.' + workid + ',0) ' + workid,
@columns = @columns + ',[' + workid + ']'
FROM TableCSET @sql = 'SELECT b.UserName' + @fields + '
FROM TableB b
LEFT JOIN (
SELECT *
FROM TableA
PIVOT (
COUNT(ID)
FOR workid IN (' + SUBSTRING(@columns,2,LEN(@columns)-1) + ')
) P
) T
ON b.UserID = T.UserID'PRINT @sql
EXEC (@sql)
SELECT b.UserName,ISNULL(T.W1,0) W1,ISNULL(T.W2,0) W2,ISNULL(T.W3,0) W3
FROM TableB b
LEFT JOIN (
SELECT *
FROM TableA
PIVOT (
COUNT(ID)
FOR workid IN ([W1],[W2],[W3])
) P
) T
ON b.UserID = T.UserID
UserName W1 W2 W3
---------- ----------- ----------- -----------
王一 2 1 0
李二 0 2 0
赵三 0 0 0
with tablea
as
(
select 1 as id,'u1' userid ,'w1' as workid
union all
select 2 as id,'u1' userid ,'w2' as workid
union all
select 3 as id,'u1' userid ,'w1' as workid
union all
select 4 as id,'u2' userid ,'w2' as workid
union all
select 5 as id,'u2' userid ,'w2' as workid
),
tableb as
(
select 'u1' as userid,'王一' as username
union all
select 'u2' as userid,'李二' as username
union all
select 'u3' as userid,'赵三' as username
),
tablec as
(
select 'W1' as workid,'卫生' as workname
union all
select 'w2' as workid,'服务' as workname
union all
select 'w3' as workid,'安全' as workname
)
select username,ISNULL(W1,0) AS W1,ISNULL(W2,0) AS W2,
ISNULL(W3,0) AS W3
from (select username,tablec.workid,a.counts from
(select userid,workid,COUNT(*)as counts from tablea
group by userid,workid) as a RIGHT join tableb
on a.userid=tableb.userid LEFT join tablec
on a.workid=tablec.workid)AS T
pivot (sum(counts) for workid in (W1,W2,W3))
AS P
--结果
username W1 W2 W3
-------- ----------- ----------- -----------
李二 0 2 0
王一 2 1 0
赵三 0 0 0(3 行受影响)
稍作修改declare @sql varchar(500)
declare @courseStr varchar(300)
set @sql='select username'
set @courseStr=''
select @courseStr=@courseStr+',sum(case workid when '''+workid+''' then 1 then 0 end) as '+workid+' '
from (select distinct workid from TableC) C
set @sql=@sql+@courseStr+' from (select B.userId,B.Username,A.workid from TableB as B left join TableA as A on B.UserID=A.UserID) D group by username with rollup'
exec(@sql)