a表(主表)主要字段为
userID ftypeID(类别id) fvalue(值)
1 1 100
2 3 50
2 2 30
1 1 20
b表为类型表 主要字段为
ftypeID ftypeName
1 类型1
2 类型2
3 类型3
我想得到下面的查询效果 请问存储过程怎么写userID 类型1值 类型2值 类型3值
1 120 0 0
2 0 30 50
userID ftypeID(类别id) fvalue(值)
1 1 100
2 3 50
2 2 30
1 1 20
b表为类型表 主要字段为
ftypeID ftypeName
1 类型1
2 类型2
3 类型3
我想得到下面的查询效果 请问存储过程怎么写userID 类型1值 类型2值 类型3值
1 120 0 0
2 0 30 50
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-27 15:10:59
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([userID] int,[ftypeID] int,[fvalue] int)
insert [A]
select 1,1,100 union all
select 2,3,50 union all
select 2,2,30 union all
select 1,1,20--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ftypeID] int,[ftypeName] varchar(5))
insert [B]
select 1,'类型1' union all
select 2,'类型2' union all
select 3,'类型3'--------------开始查询--------------------------
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME(ftypename+'值') + '=sum(case when [ftypename]='
+ QUOTENAME(ftypename, '''') + ' then [fvalue] else 0 end)'
FROM (select a.userid,a.fvalue,b.ftypename
from [A] LEFT JOIN [B] ON a.ftypeid=b.ftypeid) a
GROUP BY ftypename
EXEC('select [userid]'+@s+' from ( (select a.userid,a.fvalue,b.ftypename
from [A] LEFT JOIN [B] ON a.ftypeid=b.ftypeid) )a group by [userid]')
----------------结果----------------------------
/*
userid 类型1值 类型2值 类型3值
----------- ----------- ----------- -----------
1 120 0 0
2 0 30 50
*/
select userid,sum(case when ftypename='类型1' then v else 0 end) 类型1,
sum(case when ftypename='类型2' then v else 0 end) 类型2,
sum(case when ftypename='类型3' then v else 0 end) 类型3
from(
select userid,bc.ftypename,sum(fvalue) v from ac,bc where ac.ftypeid=bc.ftypeid group by userid,bc.ftypename) a
group by userid
sum(case when ftypename='类型2' then v else 0 end) 类型2,
sum(case when ftypename='类型3' then v else 0 end) 类型3
from(
select userid,b.ftypename,sum(fvalue) v from a,b where a.ftypeid=b.ftypeid group by userid,b.ftypename) d
group by userid