SQL求解

songguozhi 2010-12-30 03:18:09
我希望在一个雇员、销售金额、销售数量的月报表,自己尝试做了一个,但觉得不是最好的办法,
希望有大侠能帮着改进下,同时最好实现月的动态展现


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
*/


...全文
142 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
Shawn 2011-01-01
  • 打赏
  • 举报
回复
写个存储过程,按年,月来生成报表统计信息:
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', ''
王向飞 2010-12-30
  • 打赏
  • 举报
回复
写成存储过程也好,效率当然还是你的那种好些
dawugui 2010-12-30
  • 打赏
  • 举报
回复
一个是sql 2000的,一个是sql 2005的,估计差不多.自己试试.
songguozhi 2010-12-30
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 dawugui 的回复:]

引用 5 楼 dawugui 的回复:
这是你的需求:
同时最好实现月的动态展现

如果你的月份不确定,就只能用动态SQL来做.

如果固定就很简单.
除非你每次只统计固定的月份个数,如两个月,然后把月份值传入.
SQL code
CREATE TABLE tb5(EmpId INT, total_money MONEY, total_num INT, date INT)
go……
[/Quote]

sum(case.....)与两次PIVOT方式内联 两种方式,哪个效率更高???
dawugui 2010-12-30
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 dawugui 的回复:]
这是你的需求:
同时最好实现月的动态展现

如果你的月份不确定,就只能用动态SQL来做.

如果固定就很简单.[/Quote]
除非你每次只统计固定的月份个数,如两个月,然后把月份值传入.
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 操作消除了空值。
*/
飘零一叶 2010-12-30
  • 打赏
  • 举报
回复
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')
dawugui 2010-12-30
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 songguozhi 的回复:]
其实我的目的是想用一个sql语句完成,不使用动态sql,这样我才可以在必要的时候转换为视图[/Quote]

这是你的需求:
同时最好实现月的动态展现

如果你的月份不确定,就只能用动态SQL来做.

如果固定就很简单.
fengyun142415 2010-12-30
  • 打赏
  • 举报
回复

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
songguozhi 2010-12-30
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 dawugui 的回复:]

--sql 2000
SQL code
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, 4……
[/Quote]
其实我的目的是想用一个sql语句完成,不使用动态sql,这样我才可以在必要的时候转换为视图
dawugui 2010-12-30
  • 打赏
  • 举报
回复
--sql 2000
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 操作消除了空值。
*/

27,578

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧