有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
解决方案 »
- 这样一个账户可以实现不?
- SQL安装问题
- 如何分别取一个小数的整数部分和小数部分呀,比如12.19,取出来整数为12,小数为19?
- 写触发器限制某个字段的更新
- 如何查看进行中执行的SQL语句! ! ???
- 子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
- 为什么在设计表是当指定字段类型为Text时,其长度本应该为16,可是我却在保存时,SQL Server 自动将其设为7000
- SQL两个不同结构的表,将一个表字段的值插入到另外一个表相同的字段中
- 存储过程中,事务的回滚问题(为什么向表中插入异常数据时,存储过程就不再往下执行了?即insert语句下面的语句都不再执行)
- 在广域网上,客户端和服务器端如何连接?
- 如何判断两个mirror数据库之间是存在有endpoint的
- 索引的使用问题
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