22,209
社区成员
发帖
与我相关
我的任务
分享
/*
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone
*/
-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(NAME VARCHAR(10),itemdatemonth int,[money] int)
Go
Insert into TB
SELECT '技术费',1,10000 union all
SELECT '技术费',1,20000 union all
SELECT '技术费',2,20000 union all
SELECT '技术费',3,50000 union all
SELECT '业务费',1,20000 union all
SELECT '业务费',2,30000 union all
SELECT '业务费',3,50000 union all
SELECT '业务费',3,20000
Go
--Start
Select NAME,
SUM(CASE WHEN itemdatemonth=1 THEN MONEY ELSE 0 END)AS '1',
SUM(CASE WHEN itemdatemonth=2 THEN MONEY ELSE 0 END)AS '2',
SUM(CASE WHEN itemdatemonth=3 THEN MONEY ELSE 0 END)AS '3',
(SELECT SUM(MONEY)/COUNT(DISTINCT itemdatemonth) FROM TB WHERE NAME=T.NAME )AS AVGMONEY
from TB T
GROUP BY NAME
--Result:
/*
(所影响的行数为 8 行)
NAME 1 2 3 AVGMONEY
---------- ----------- ----------- ----------- -----------
技术费 30000 20000 50000 33333
业务费 20000 30000 70000 40000
(所影响的行数为 2 行)
*/
--End
/*
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone
*/
-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(NAME VARCHAR(10),itemdatemonth int,[money] int)
Go
Insert into TB
SELECT '技术费',1,10000 union all
SELECT '技术费',1,20000 union all
SELECT '技术费',2,20000 union all
SELECT '技术费',3,50000 union all
SELECT '业务费',1,20000 union all
SELECT '业务费',2,30000 union all
SELECT '业务费',3,50000 union all
SELECT '业务费',3,20000
Go
--Start
Select NAME,
SUM(CASE WHEN itemdatemonth=1 THEN MONEY ELSE 0 END)AS '1',
SUM(CASE WHEN itemdatemonth=2 THEN MONEY ELSE 0 END)AS '2',
SUM(CASE WHEN itemdatemonth=3 THEN MONEY ELSE 0 END)AS '3',
AVG(MONEY)AS AVGMONEY
from TB
GROUP BY NAME
--Result:
/*
(所影响的行数为 8 行)
NAME 1 2 3 AVGMONEY
---------- ----------- ----------- ----------- -----------
技术费 30000 20000 50000 25000
业务费 20000 30000 70000 30000
(所影响的行数为 2 行)
*/
--End
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([item] varchar(6),[datemonth] varchar(3),[money] int)
insert [tb]
select '技术费','1月',10000 union all
select '技术费','1月',20000 union all
select '技术费','2月',20000 union all
select '技术费','3月',50000 union all
select '业务费','1月',20000 union all
select '业务费','2月',30000 union all
select '业务费','3月',50000 union all
select '业务费','3月',20000
--sql2005静态
select *,[avg]=([1月]+[2月]+[3月])/3 from [tb]
pivot
(
sum(money) for [datemonth] in([1月],[2月],[3月])
) p
---sql2000
select item,
[1月]=sum(case when datemonth='1月' then [money] else 0 end),
[2月]=sum(case when datemonth='2月' then [money] else 0 end),
[3月]=sum(case when datemonth='3月' then [money] else 0 end),
[avg]=sum(money)/(select count(distinct [datemonth]) from tb where item=t.item)
from tb t group by item
/*
item 1月 2月 3月 avg
------ ----------- ----------- ----------- -----------
技术费 30000 20000 50000 33333
业务费 20000 30000 70000 40000
*/
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([item] varchar(6),[datemonth] varchar(3),[money] int)
insert [tb1]
select '技術費','1月',10000 union all
select '技術費','1月',20000 union all
select '技術費','2月',20000 union all
select '技術費','3月',50000 union all
select '業務費','1月',20000 union all
select '業務費','2月',30000 union all
select '業務費','3月',50000 union all
select '業務費','3月',20000
select item,
sum(case when datemonth='1月' then [money] else 0 end) '1月',
sum(case when datemonth='2月' then [money] else 0 end) '2月',
sum(case when datemonth='3月' then [money] else 0 end) '3月',
avg([money])
from tb1 group by item
---动态的
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-07 14:41:47
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([item] varchar(6),[datemonth] varchar(3),[money] int)
insert [tb]
select '技术费','1月',10000 union all
select '技术费','1月',20000 union all
select '技术费','2月',20000 union all
select '技术费','3月',50000 union all
select '业务费','1月',20000 union all
select '业务费','2月',30000 union all
select '业务费','3月',50000 union all
select '业务费','3月',20000
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select item '
select @sql = @sql + ' , sum(case datemonth when ''' + datemonth + ''' then money else 0 end) [' + datemonth + ']'
from (select distinct datemonth from tb) as a
set @sql = @sql + ' , cast(avg(money*1.0) as decimal(18,2)) 平均 from tb group by item'
exec(@sql)
----------------结果----------------------------
/* item 1月 2月 3月 平均
------ ----------- ----------- ----------- ---------------------------------------
技术费 30000 20000 50000 25000.00
业务费 20000 30000 70000 30000.00
(2 行受影响)
*/