有A 一张表A表如下
iD customer date price weight moneys
01 张三 2009-07-01 10 20 200
02 张三 2009-07-02 20 20 400实现功能为一个日报表
我要查询的结果是显示今天的产量sum(weight)以及销售金额sum(moneys),并且显示昨天的产量、金额结果如下:
customer 昨天weight 今天weight 昨天金额 今天金额
张三 20 20 200 400
iD customer date price weight moneys
01 张三 2009-07-01 10 20 200
02 张三 2009-07-02 20 20 400实现功能为一个日报表
我要查询的结果是显示今天的产量sum(weight)以及销售金额sum(moneys),并且显示昨天的产量、金额结果如下:
customer 昨天weight 今天weight 昨天金额 今天金额
张三 20 20 200 400
customer,
昨天weight=MAX(case when DATEDIFF(dd,date,getdate())=1 then weight else 0 end),
今天weight=MAX(case when DATEDIFF(dd,date,getdate())=0 then weight else 0 end),
昨天金额=MAX(case when DATEDIFF(dd,date,getdate())=1 then moneys else 0 end),
今天金额=MAX(case when DATEDIFF(dd,date,getdate())=0 then moneys else 0 end)
from A
group by customer
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(iD int,customer varchar(10), date datetime, price int, weight int, moneys int)
go
insert tb SELECT
01 , '张三' , '2009-7-31' , 10 , 20 , 200 UNION ALL SELECT
02 , '张三' , '2009-08-01' , 20 , 20 , 400
go
select
customer,
昨天weight=MAX(case when DATEDIFF(dd,date,getdate())=1 then weight else 0 end),
今天weight=MAX(case when DATEDIFF(dd,date,getdate())=0 then weight else 0 end),
昨天金额=MAX(case when DATEDIFF(dd,date,getdate())=1 then moneys else 0 end),
今天金额=MAX(case when DATEDIFF(dd,date,getdate())=0 then moneys else 0 end)
from tb
group by customer
go
/*------------
ustomer 昨天weight 今天weight 昨天金额 今天金额
---------- ----------- ----------- ----------- -----------
张三 20 20 200 400
-------*/
/******************************************************************************************************************************************************
以学生成绩为例子,比较形象易懂整理人:中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
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]GO
动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/------------------------------------------------------------------------------------------
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+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态: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),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78(8 行受影响)
*/
create table a(id varchar(10),customer varchar(10),date smalldatetime,price money,weight float,moneys money)
go
insert into a select '01','张三','2009-07-31',10,20,200
insert into a select '02','张三','2009-08-01',20,20,400
insert into a select '02','李四','2009-07-31',20,20,400
insert into a select '02','李四','2009-08-01',20,30,600
--select * from aselect customer
,(select sum(weight) from a where a.customer=a1.customer and datediff(dd,[date],getdate())=1 ) as 昨天weight
,(select sum(weight) from a where a.customer=a1.customer and datediff(dd,[date],getdate())=0 ) as 今天weight
,(select sum(200) from a where a.customer=a1.customer and datediff(dd,[date],getdate())=1 ) as 昨天金额
,(select sum(200) from a where a.customer=a1.customer and datediff(dd,[date],getdate())=0 ) as 今天金额
from a a1
group by customer
你连八一都不过,就说不过去了吧
--上面忘改数字了
create table a(id varchar(10),customer varchar(10),date smalldatetime,price money,weight float,moneys money)
go
insert into a select '01','张三','2009-07-31',10,20,200
insert into a select '02','张三','2009-08-01',20,20,400
insert into a select '02','李四','2009-07-31',20,20,400
insert into a select '02','李四','2009-08-01',20,30,600
select * from aselect customer
,(select sum(weight) from a where a.customer=a1.customer and datediff(dd,[date],getdate())=1 ) as 昨天weight
,(select sum(weight) from a where a.customer=a1.customer and datediff(dd,[date],getdate())=0 ) as 今天weight
,(select sum(moneys) from a where a.customer=a1.customer and datediff(dd,[date],getdate())=1 ) as 昨天金额
,(select sum(moneys) from a where a.customer=a1.customer and datediff(dd,[date],getdate())=0 ) as 今天金额
from a a1
group by customer
DECLARE @A TABLE(id varchar(4),customer varchar(12),date datetime,price decimal(8,2),weight int,moneys decimal(10,2) )insert @a select
'01' , '张三' , '2009-07-31' , 10 , 20 , 200 union all select
'02' , '张三' , '2009-08-01' , 20 , 20 , 400 select customer,
[昨天weight]=sum(case when dateadd(day,1,date)=convert(varchar(10),getdate(),112) then weight else 0 end),
[今天weight]=sum(case when date=convert(varchar(10),getdate(),112) then weight else 0 end) ,
[昨天金额]=sum(case when dateadd(day,1,date)=convert(varchar(10),getdate(),112) then moneys else 0 end),
[今天金额]=sum(case when date=convert(varchar(10),getdate(),112) then moneys else 0 end)
from @a group by customercustomer 昨天weight 今天weight 昨天金额 今天金额
------------ ----------- ----------- ---------------------------------------- ----------------------------------------
张三 20 20 200.00 400.00(所影响的行数为 1 行)
insert @tb select '02' , N'张三' , '2009-08-01' , 20, 20 , 400 select customer,
昨天weight=(select weight from @tb where customer=a.customer and datediff(dd,[date],getdate())=1),
今天weight=(select weight from @tb where customer=a.customer and datediff(dd,[date],getdate())=0),
昨天金额=(select moneys from @tb where customer=a.customer and datediff(dd,[date],getdate())=1),
今天金额=(select moneys from @tb where customer=a.customer and datediff(dd,[date],getdate())=0)
from @tb a
group by a.customer
/*customer 昨天weight 今天weight 昨天金额 今天金额
---------- ----------- ----------- ----------- -----------
张三 20 20 200 400(所影响的行数为 1 行)
*/
insert @A
select 01,'张三','2009-07-01',10,20,200 union all
select 02,'张三','2009-07-02',20,20,400
select * from @Aselect A.customer,A.date,isnull(sum(B.weight),0) as 昨天weight,sum(A.weight) as 今天weight,
isnull(sum(B.moneys),0) as 昨天金额,sum(A.moneys) as 今天金额
from @A as A left join @A as B
on A.customer=B.customer and A.date=B.date+1
group by A.customer,A.date
customer date 昨天weight 今天weight 昨天金额 今天金额
-------- ----------------------- ----------- ----------- --------------------- ---------------------
张三 2009-07-01 00:00:00.000 0 20 0.00 200.00
张三 2009-07-02 00:00:00.000 20 20 200.00 400.00
declare @A table(ID int,customer nvarchar(5),date datetime,price money,weight int,moneys money)
insert @A
select 01,'张三','2009-07-01',10,20,200 union all
select 02,'张三','2009-07-02',20,20,400
select * from @Aselect A.customer,A.date,isnull(sum(B.weight),0) as 昨天weight,sum(A.weight) as 今天weight,
isnull(sum(B.moneys),0) as 昨天金额,sum(A.moneys) as 今天金额
from @A as A left join @A as B
on A.customer=B.customer and A.date=B.date+1
group by A.customer,A.date