sql union 举例查询

[技术九重我再第几重]

有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

[renzhe02]

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 a

select 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

[黄_瓜]

t-MAC 不过假期也不过周末都不说你了，
你连八一都不过，就说不过去了吧

[renzhe02]

--上面忘改数字了
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 a

select 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

[feixianxxx]

[Quote=引用 5 楼 beirut 的回复:]
t-MAC 不过假期也不过周末都不说你了，
你连八一都不过，就说不过去了吧
[/Quote]
,....今年不过节

[SQL77]

[Quote=引用 7 楼 feixianxxx 的回复:]
引用 5 楼 beirut 的回复:
t-MAC 不过假期也不过周末都不说你了，
你连八一都不过，就说不过去了吧

,....今年不过节
[/Quote]
一样热情似火,呵呵,

[华夏小卒]

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 customer

customer     昨天weight    今天weight    昨天金额                                     今天金额                                     
------------ ----------- ----------- ---------------------------------------- ---------------------------------------- 
张三           20          20          200.00                                   400.00

（所影响的行数为 1 行）

[sdhdy]

declare @tb table(iD varchar(10),customer nvarchar(10),[date] datetime,price int,weight int,moneys int )

insert @tb select '01' ,   N'张三' ,     '2009-07-31',       10 ,   20,    200 
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 行）
*/

[xxmsuper]

可能这是楼主想要的答案：

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 @A

select 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

[feixianxxx]

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 A
group by customer 

[feixianxxx]

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

[feixianxxx]

--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂

整理人：中国风(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 行受影响)
*/

[kngzheng]

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 @A

select 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

[kngzheng]

只是试一试,对了,我发现我不应该用自己的照片做头像
好像比较土