34,576
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2015-12-02 12:00:38
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([收费类型] varchar(6),[排序] int)
insert [T1]
select '管理费',0 union all
select '工资费',1 union all
select '劳务费',2
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([单据号] varchar(5),[日期] datetime,[收费类型] varchar(6),[金额] int)
insert [T2]
select 'sk001','2015-11-15','管理费',100 union all
select 'sk002','2015-10-7','劳务费',50 union all
select 'sk002','2015-10-7','工资费',300
--------------开始查询--------------------------
SELECT
t2.单据号,CONVERT(VARCHAR(10),t2.日期 ,120) AS 日期,
SUM(CASE WHEN t2.收费类型='管理费' THEN 金额 ELSE 0 END) AS '管理费',
SUM(CASE WHEN t2.收费类型='劳务费' THEN 金额 ELSE 0 END) AS '劳务费',
SUM(CASE WHEN t2.收费类型='工资费' THEN 金额 ELSE 0 END) AS '工资费'
FROM t1 INNER JOIN t2 ON T1.收费类型 = T2.收费类型
GROUP BY
t2.单据号,CONVERT(VARCHAR(10),t2.日期 ,120)
----------------结果----------------------------
/* 单据号 日期 管理费 劳务费 工资费
----- ---------- ----------- ----------- -----------
sk001 2015-11-15 100 0 0
sk002 2015-10-07 0 50 300
(2 行受影响)
*/
--测试数据
create table #Table(
单据号 varchar(15),
日期 varchar(15),
收费类型 varchar(15),
金额 int
)
insert into #Table
select 'sk001', '2015-11-15' , '管理费' , 100 union all
select 'sk002' , '2015-10-7', '劳务费' , 50 union all
select 'sk002', '2015-10-7' , '工资费', 300
create table #Table1(
收费类型 varchar(15),
排序 int
)
insert into #Table1
select'管理费', 0 union all
select'工资费' , 1 union all
select'劳务费' , 2
--查询
select a.*,b.排序 into #tab from #Table a left join #Table1 b on a.收费类型=b.收费类型
declare @sql varchar(max)
set @sql=''
select @sql=@sql + ','+rtrim(收费类型)+'=sum(case 收费类型 when '''+rtrim(收费类型)+''' then 金额 else 0 end)' from #tab group by 收费类型,排序 order by 排序
exec('select 单据号,日期'+@sql+'from #tab group by 单据号,日期 order by 单据号' )
DROP TABLE #Table
DROP TABLE #Table1
--结果
/*
单据号 日期 管理费 工资费 劳务费
--------------- --------------- ----------- ----------- -----------
sk001 2015-11-15 100 0 0
sk002 2015-10-7 0 300 50
(2 行受影响)
*/
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]