1 CREATE TABLE [dbo].[user]( 2 [id] [int] IDENTITY(1,1) NOT NULL, 3 [name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL 4 ) 5 6 7 CREATE TABLE [dbo].[score]( 8 [user_id] [int] NULL, 9 [kemu] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, 10 [fengshu] [float] NULL 11 ) 12 13 14方法一:pivot
SELECT name,[yuwen],[shuxue],isnull([yingyu],0) FROM dbo.score inner join dbo.[user] on id=user_id PIVOT ( 15 SUM(fengshu) FOR [kemu] IN ([yuwen],[shuxue],[yingyu]) 16 ) AS T
方法二:case when
select name, sum(case kemu when 'yuwen' then (fengshu) else 0 end ) as 'yuwen', sum(case kemu when 'shuxue' then (fengshu) else 0 end ) as 'shuxue', sum(case kemu when 'yingyu' then (fengshu) else 0 end ) as 'yingyu' from score inner join dbo.[user] on id=user_id group by name
