27,578
社区成员
发帖
与我相关
我的任务
分享
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='tb5')
DROP TABLE dbo.tb5
CREATE TABLE tb5(EmpId INT, total_money MONEY, total_num INT, date INT)
go
INSERT INTO dbo.tb5(EmpId, total_money, total_num, date)
SELECT 1, 12.05, 3, 201001 UNION ALL
SELECT 1, 41.00, 5, 201001 UNION ALL
SELECT 1, 12.00, 4, 201002 UNION ALL
SELECT 1, 41.00, 5, 201002 UNION ALL
SELECT 3, 19.00, 4, 201001 UNION ALL
SELECT 3, 11.00, 1, 201001 UNION ALL
SELECT 3, 12.00, 1, 201002 UNION ALL
SELECT 4, 80.00, 2, 201001 UNION ALL
SELECT 4, 80.00, 2, 201002 UNION ALL
SELECT 5, 12.00, 3, 201002
select m.EmpId, [201001_m], [201001_n], [201002_m], [201002_n] from
(
SELECT EmpId, [201001] '201001_m', [201002] '201002_m'
FROM (select EmpId, date, total_money FROM dbo.tb5) a
PIVOT (SUM(total_money) FOR date IN([201001], [201002])) pvt
) m
inner join (
SELECT EmpId, [201001] '201001_n', [201002] '201002_n'
FROM (select EmpId, date, total_num FROM dbo.tb5) a
PIVOT (SUM(total_num) FOR date IN([201001], [201002])) pvt) n
on m.EmpId = n.EmpId
/*
EmpId 201001_m 201001_n 201002_m 201002_n
----------- --------------------- ----------- --------------------- -----------
1 53.05 8 53.00 9
3 30.00 5 12.00 1
4 80.00 2 80.00 2
5 NULL NULL 12.00 3
*/
CREATE PROCEDURE report
@year CHAR(4),
@month CHAR(2) = ''
AS
BEGIN
IF(@month <> '')
BEGIN
SELECT
EmpId,
[money] = SUM(total_money),
[num] = SUM(total_num)
FROM tb5
WHERE [date] = (@year + @month)
GROUP BY EmpId
END
ELSE
BEGIN
DECLARE @sql VARCHAR(8000)
SELECT
@sql = ISNULL(@sql + ',', '')
+ 'SUM(CASE WHEN [date] = '''+ [date] +''' THEN [total_money] ELSE 0 END) AS ['+ [date] +'_m],'
+ 'SUM(CASE WHEN [date] = '''+ [date] +''' THEN [total_num] ELSE 0 END) AS ['+ [date] +'_n]'
FROM
(SELECT DISTINCT [date] = CAST([date] AS VARCHAR(10)) FROM [tb5] WHERE SUBSTRING(CAST([date] AS VARCHAR(10)),1, 4) = @year) A
IF (ISNULL(@sql, '') <> '')
EXEC ('SELECT [EmpId], ' + @sql + ' FROM [tb5] GROUP BY [EmpId]')
ELSE
SELECT 'INFO' = '不存在此年份的记录'
END
END
--TEST
EXEC report '2010', '01'
EXEC report '2010', '02'
EXEC report '2010', ''
CREATE TABLE tb5(EmpId INT, total_money MONEY, total_num INT, date INT)
go
INSERT INTO tb5(EmpId, total_money, total_num, date)
SELECT 1, 12.05, 3, 201001 UNION ALL
SELECT 1, 41.00, 5, 201001 UNION ALL
SELECT 1, 12.00, 4, 201002 UNION ALL
SELECT 1, 41.00, 5, 201002 UNION ALL
SELECT 3, 19.00, 4, 201001 UNION ALL
SELECT 3, 11.00, 1, 201001 UNION ALL
SELECT 3, 12.00, 1, 201002 UNION ALL
SELECT 4, 80.00, 2, 201001 UNION ALL
SELECT 4, 80.00, 2, 201002 UNION ALL
SELECT 5, 12.00, 3, 201002
declare @date1 as int
declare @date2 as int
set @date1 = 201001 --这里的值你可以自己变动
set @date2 = 201002 --这里的值你可以自己变动
select EmpId ,
sum(case date when @date1 then total_money else null end) 第一个月份_m,
sum(case date when @date1 then total_num else null end) 第一个月份_n,
sum(case date when @date2 then total_money else null end) 第二个月份_m,
sum(case date when @date2 then total_num else null end) 第二个月份_n
from tb5 group by empid
drop table tb5
/*
EmpId 第一个月份_m 第一个月份_n 第二个月份_m 第二个月份_n
----------- --------------------- ----------- --------------------- -----------
1 53.0500 8 53.0000 9
3 30.0000 5 12.0000 1
4 80.0000 2 80.0000 2
5 NULL NULL 12.0000 3
(所影响的行数为 4 行)
警告: 聚合或其它 SET 操作消除了空值。
*/
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='tb5')
DROP TABLE dbo.tb5
CREATE TABLE tb5(EmpId INT, total_money MONEY, total_num INT, date INT)
go
INSERT INTO dbo.tb5(EmpId, total_money, total_num, date)
SELECT 1, 12.05, 3, 201001 UNION ALL
SELECT 1, 41.00, 5, 201001 UNION ALL
SELECT 1, 12.00, 4, 201002 UNION ALL
SELECT 1, 41.00, 5, 201002 UNION ALL
SELECT 3, 19.00, 4, 201001 UNION ALL
SELECT 3, 11.00, 1, 201001 UNION ALL
SELECT 3, 12.00, 1, 201002 UNION ALL
SELECT 4, 80.00, 2, 201001 UNION ALL
SELECT 4, 80.00, 2, 201002 UNION ALL
SELECT 5, 12.00, 3, 201002
SELECT *from tb5
declare @s varchar(1000)
select @s=ISNULL(@s+',','')+quotename(cast(date as varchar(10))+'_m')+'=sum(case when date='''+cast(date as varchar(10))+''' then total_money else 0 end)'+','+quotename(cast(date as varchar(10))+'_n')+'=sum(case when date='''+cast(date as varchar(10))+''' then total_num else 0 end)' from tb5 group by date
exec ('select EmpId,'+@s+' from tb5 group by EmpId')
select EmpId,sum(case when date=201001 then total_money else 0 end) as [201001_m],
sum(case when date=201001 then total_num else 0 end) as [201001_n],
sum(case when date=201002 then total_money else 0 end) as [201002_m],
sum(case when date=201002 then total_num else 0 end) as [201002_n]
from tb5
group by EmpId
CREATE TABLE tb5(EmpId INT, total_money MONEY, total_num INT, date INT)
go
INSERT INTO tb5(EmpId, total_money, total_num, date)
SELECT 1, 12.05, 3, 201001 UNION ALL
SELECT 1, 41.00, 5, 201001 UNION ALL
SELECT 1, 12.00, 4, 201002 UNION ALL
SELECT 1, 41.00, 5, 201002 UNION ALL
SELECT 3, 19.00, 4, 201001 UNION ALL
SELECT 3, 11.00, 1, 201001 UNION ALL
SELECT 3, 12.00, 1, 201002 UNION ALL
SELECT 4, 80.00, 2, 201001 UNION ALL
SELECT 4, 80.00, 2, 201002 UNION ALL
SELECT 5, 12.00, 3, 201002
declare @sql varchar(8000)
set @sql = 'select EmpId '
select @sql = @sql + ' , sum(case date when ''' + cast(date as varchar) + ''' then total_money else null end) [' + cast(date as varchar) + '_m]'
+ ' , sum(case date when ''' + cast(date as varchar) + ''' then total_num else null end) [' + cast(date as varchar) + '_n]'
from (select distinct date from tb5) as a
set @sql = @sql + ' from tb5 group by EmpId'
exec(@sql)
drop table tb5
/*
EmpId 201001_m 201001_n 201002_m 201002_n
----------- --------------------- ----------- --------------------- -----------
1 53.0500 8 53.0000 9
3 30.0000 5 12.0000 1
4 80.0000 2 80.0000 2
5 NULL NULL 12.0000 3
警告: 聚合或其它 SET 操作消除了空值。
*/