山寨机中的战斗机！	程序优化工程师到底对IT界有没有贡献

大家看一下，这种SQL该怎么写啊，拜托了。

楼主orange_y（）2006-06-29 08:29:10 在 MS-SQL Server / 基础类提问

昨天问的一个SQL，大家说我写的不清楚，而且也没给分，其实真的是没法给分，分0-0，可能是因为我乱发帖子吧，被惩罚的，现在只好换一个ID，重新问了，拜托帮忙了。

要求是这样，
基本的 Table ：
Brand Area InvoiceDate DailySalesValue
AA North 2006/06/28 3000
AA North 2006/06/03 2000
AA North 2005/06/03 1000
AA North 2005/06/05 1000
BB East 2006/03/05 2000
BB East 2006/03/08 4000
BB East 2005/03/01 1000

想实现的查询是
除显示 Brand Area InvoiceDate SalesValue 之外，还要显示一个MTD05和 MTD06
MTD05是取InvoiceDate中，比如InvocieDate是2006/6/28，那么MTD05就是取2005年的，6/1到/6月28日的 sum（Salesvaue）
MTD06是取 InvoiceDate中，取06年的6/1到/6月28日的 sum（Salesvaue）
所以上面的table要得出的值是：

Brand Area InvoiceDate DailySalesValue MTD05 MTD06
AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 1000 0
AA North 2005/06/05 1000 2000 0
BB East 2006/03/05 2000 1000 2000
BB East 2006/03/08 4000 1000 6000
BB East 2005/03/01 1000 1000 0

问题点数：100、回复次数：26Top

1 楼zhangxl319（sunny）回复于 2006-06-29 08:41:20 得分 0

没看明白Top

2 楼thordon（索尔的吼声）回复于 2006-06-29 08:47:27 得分 0

select Brand,Area,InvoiceDate,DailySalesValue,MTD05=
case
when InvoiceDate>=cast('2005-01-01' as datetime) and InvoiceDate<cast('2006-01-01' as datetime) then DailySalesValue
else 0
end
,
MTD06=
case
when InvoiceDate>=cast('2006-01-01' as datetime) and InvoiceDate<cast('2007-01-01' as datetime) then DailySalesValue
else 0
end

from tableTop

3 楼orange_y（）回复于 2006-06-29 08:47:34 得分 0

就是MTD05是sum(DailysalesValue）不过sum的条件是取根据
InvoiceDate来的，就是InvoiceDate如果是2006/6/28，那么MTD05就是显示2005/06/01到2005/06/28区间内的DailysalesValue的sum值。
MTD06是06年的显示2006/06/01到2006/06/28区间内的DailysalesValue的sum值。
这个功能主要是SAles部门想要了解今年的这个月这个产品的销售value，和去年这个月区间的比较。

Top

4 楼netcup（茶杯）回复于 2006-06-29 08:47:38 得分 0

select brand,area,invoicedate,dailysalesvalue,mtd05=select sum(dailysalevalue) from table where invoicedate>=convert(char(8),dateadd(year,-1,invoicedate),120)+'01' and invoicedate<=dateadd(year,-1,invoicedate),mtd06=select sum(dailyvalue) from table where invoicedate>=convert(char(8),invoicedate,120)+'01' and invoicedate<=invoicedate
from table

Top

5 楼jwt1982（叛逆者）回复于 2006-06-29 08:48:13 得分 0

AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 1000 0
AA North 2005/06/05 1000 2000 0
======================================================================
这个数据和楼主描述的不对啊
是这样的吧？

AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 3000
AA North 2005/06/03 1000 1000 3000
AA North 2005/06/05 1000 2000 4000

看起来好混乱Top

6 楼thordon（索尔的吼声）回复于 2006-06-29 08:49:29 得分 0

跟据我的那个改一下时间应该就行了吧. 可能是我这个意思Top

7 楼netcup（茶杯）回复于 2006-06-29 08:49:54 得分 0

哦,把CONVERT里的120改掉,我按照YYYY-MM-DD的格式写的,忘记了Top

8 楼LouisXIV（夜游神）回复于 2006-06-29 08:54:29 得分 0

--lz给的数据有矛盾

declare @t table
(
Brand varchar(2),
Area varchar(5),
InvoiceDate varchar(10),
DailySalesValue int
)
insert into @t
select 'AA','North','2006/06/28',3000 union all
select 'AA','North','2006/06/03',2000 union all
select 'AA','North','2005/06/03',1000 union all
select 'AA','North','2005/06/05',1000 union all
select 'BB','Ease','2006/03/05',2000 union all
select 'BB','Ease','2006/03/08',4000 union all
select 'BB','Ease','2005/03/01',1000
select
*,
(select sum(DailySalesValue) from @t where InvoiceDate between '2005/01/01' and '2005/'+right(a.InvoiceDate,5) and Brand=a.Brand and Area=a.Area) as [MTD05],
isnull((select sum(DailySalesValue) from @t where InvoiceDate between '2006/01/01' and '2006/'+right(a.InvoiceDate,5) and Brand=a.Brand and Area=a.Area),0) as [MTD06]
from @t a

/*
Brand Area InvoiceDate DailySalesValue MTD05 MTD06
----- ----- ----------- --------------- ----------- -----------
AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 1000 2000
AA North 2005/06/05 1000 2000 2000
BB Ease 2006/03/05 2000 1000 2000
BB Ease 2006/03/08 4000 1000 6000
BB Ease 2005/03/01 1000 1000 0
*/Top

9 楼paoluo（一天到晚游泳的鱼）回复于 2006-06-29 08:54:30 得分 0

InvoiceDate是字符類型還是DateTime型？？Top

10 楼LouisXIV（夜游神）回复于 2006-06-29 08:55:33 得分 0

lz给的 MTD06的示例有问题^^;Top

11 楼thordon（索尔的吼声）回复于 2006-06-29 09:00:34 得分 0

汗好象我看错了. 数据也有点问题Top

12 楼orange_y（）回复于 2006-06-29 09:24:58 得分 0

InvoideDate是dAteime型的，不一定是固定的格式2005/03/01Top

13 楼sun_power（网络）回复于 2006-06-29 09:25:06 得分 0

lz数据是有问题，你确定就有2005，和2006 这两个时间码、？Top

14 楼LouisXIV（夜游神）回复于 2006-06-29 09:37:08 得分 0

declare @t table
(
Brand varchar(2),
Area varchar(5),
InvoiceDate datetime,
DailySalesValue int
)
insert into @t
select 'AA','North','2006/06/28',3000 union all
select 'AA','North','2006/06/03',2000 union all
select 'AA','North','2005/06/03',1000 union all
select 'AA','North','2005/06/05',1000 union all
select 'BB','Ease','2006/03/05',2000 union all
select 'BB','Ease','2006/03/08',4000 union all
select 'BB','Ease','2005/03/01',1000
select
*,
isnull((select sum(DailySalesValue) from @t where InvoiceDate between '2005/01/01' and '2005/'+right(convert(varchar(10),a.InvoiceDate,111),5) and Brand=a.Brand and Area=a.Area),0) as [MTD05],
(case when InvoiceDate>='2006/1/1' then
isnull((select sum(DailySalesValue) from @t where InvoiceDate between '2006/01/01' and '2006/'+right(convert(varchar(10),a.InvoiceDate,111),5) and Brand=a.Brand and Area=a.Area),0)
else 0 end) as [MTD06]
from @t a

/*
Brand Area InvoiceDate DailySalesValue MTD05 MTD06
----- ----- ------------------------------------------------------ --------------- ----------- -----------
AA North 2006-06-28 00:00:00.000 3000 2000 5000
AA North 2006-06-03 00:00:00.000 2000 1000 2000
AA North 2005-06-03 00:00:00.000 1000 1000 0
AA North 2005-06-05 00:00:00.000 1000 2000 0
BB Ease 2006-03-05 00:00:00.000 2000 1000 2000
BB Ease 2006-03-08 00:00:00.000 4000 1000 6000
BB Ease 2005-03-01 00:00:00.000 1000 1000 0

*/Top

15 楼playwarcraft（时间就像乳沟,挤挤还是有的）回复于 2006-06-29 09:45:10 得分 0

declare @t table
(
Brand varchar(2),
Area varchar(5),
InvoiceDate varchar(10),
DailySalesValue int
)
insert into @t
select 'AA','North','2006/06/28',3000 union all
select 'AA','North','2006/06/03',2000 union all
select 'AA','North','2005/06/03',1000 union all
select 'AA','North','2005/06/05',1000 union all
select 'BB','Ease','2006/03/05',2000 union all
select 'BB','Ease','2006/03/08',4000 union all
select 'BB','Ease','2005/03/01',1000

select * into #temp from (select Brand,Area,InvoiceDate,DailySalesValue,InvoiceDate as M1,InvoiceDate as M2 from @t ) T

select C.*,isnull(D.MTD05,0) as MTD05 , isnull(E.MTD06,0) as MTD06 from @t C
left join (select A.InvoiceDate,sum(B.DailySalesValue) as MTD05 from @t A,#temp B where convert(datetime,M1)<=dateadd(yy,-1,convert(datetime,A.InvoiceDate))
and convert(datetime,M1)>=convert(datetime,(convert(char(06),dateadd(yy,-1,convert
(datetime,A.InvoiceDate)),112)+'01')) group by A.InvoiceDate) D
on C.InvoiceDate=D.InvoiceDate
left join (select F.InvoiceDate,sum(G.DailySalesValue) as MTD06 from @t F,#temp G where convert(char(06),convert(datetime,F.InvoiceDate),112)=convert(char(06),convert(datetime,M2),112) and convert(datetime,M2)<=convert(datetime,F.InvoiceDate) group by F.InvoiceDate ) E
on C.InvoiceDate=E.InvoiceDate

drop table #temp

/*The result:*/
--MTD06取當年當月小於當日的和
--MTD05取去年當月小於當日的和

Brand Area InvoiceDate DailySalesValue MTD05 MTD06
----- ----- ----------- --------------- ----------- -----------
AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 0 1000
AA North 2005/06/05 1000 0 2000
BB Ease 2006/03/05 2000 1000 2000
BB Ease 2006/03/08 4000 1000 6000
BB Ease 2005/03/01 1000 0 1000Top

16 楼paoluo（一天到晚游泳的鱼）回复于 2006-06-29 09:46:27 得分 100

Create Table TEST
(Brand Varchar(10),
Area Varchar(10),
InvoiceDate DateTime,
DailySalesValue Int)
Insert TEST Select 'AA', 'North', '2006/06/28', 3000
Union All Select 'AA', 'North', '2006/06/03', 2000
Union All Select 'AA', 'North', '2005/06/03', 1000
Union All Select 'AA', 'North', '2005/06/05', 1000
Union All Select 'BB', 'East', '2006/03/05', 2000
Union All Select 'BB', 'East', '2006/03/08', 4000
Union All Select 'BB', 'East', '2005/03/01', 1000
GO
Select
Brand,
Area,
Convert(Varchar,InvoiceDate,111) As InvoiceDate,
DailySalesValue,
(Select SUM(DailySalesValue) From TEST Where Brand=A.Brand And InvoiceDate Between '2005/'+Rtrim(Month(A.InvoiceDate))+'/01' And '2005/'+Convert(Varchar(5),A.InvoiceDate,101)) As MTD05,
(Case Year(InvoiceDate) When 2006 Then (Select SUM(DailySalesValue) From TEST Where Brand=A.Brand And InvoiceDate Between '2006/'+Rtrim(Month(A.InvoiceDate))+'/01' And '2006/'+Convert(Varchar(5),A.InvoiceDate,101)) Else 0 End) As MTD06
From TEST A
GO
Drop Table TEST
--Result
/*
Brand Area InvoiceDate DailySalesValue MTD05 MTD06
AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 1000 0
AA North 2005/06/05 1000 2000 0
BB East 2006/03/05 2000 1000 2000
BB East 2006/03/08 4000 1000 6000
BB East 2005/03/01 1000 1000 0
*/Top

17 楼playwarcraft（时间就像乳沟,挤挤还是有的）回复于 2006-06-29 09:47:21 得分 0

昏,好像我的結果跟樓主要的不一樣.....
樓主要的MTD05不管怎樣都是05年的,MTD06是06年的??Top

18 楼paoluo（一天到晚游泳的鱼）回复于 2006-06-29 09:49:38 得分 0

LouisXIV(夜游神)

'2005/01/01' and '2005/'+right(convert(varchar(10),a.InvoiceDate,111),5) and Brand=a.Brand and

這麼寫肯定有問題的，他是要統計當月的，你卻是從1月開始統計的。

另外得到"03/08"這種格式，可以直接用101，不需要用111然後再用right函數取Top

19 楼paoluo（一天到晚游泳的鱼）回复于 2006-06-29 09:50:25 得分 0

netcup(茶杯) 的語句，語法都通不過的Top

20 楼playwarcraft（时间就像乳沟,挤挤还是有的）回复于 2006-06-29 09:53:03 得分 0

我的理解錯了,我以為是今年和去年的關係,
樓主要的好像只是05和06年...Top

21 楼LouisXIV（夜游神）回复于 2006-06-29 10:01:29 得分 0

看错了^^;Top

22 楼jetdw（空间商务）回复于 2006-06-29 11:40:09 得分 0

不知道这个写法符合楼主的要求不？
SELECT brand,area,invoicedate,dailysalesvalue,
mtd05=SELECT SUM(dailysalevalue) FROM tb
WHERE invoicedate BETWEEN CONVERT(DATETIME,(CONVERT(CHAR(8),DATEADD(YEAR,-1,invoicedate))+'01')) AND DATEADD(YY,-1,invoicedate),
mtd06=SELECT SI,(dailysalevalue) FROM tb
WHERE invoicedate BETWEEN CONVERT(DATETIME,(CONVERT(CHAR(8),DATEADD(YEAR,-1,invoicedate))+'01')) AND invoicedate
FROM tbTop

23 楼jetdw（空间商务）回复于 2006-06-29 11:47:55 得分 0

不好意思，写错了点，现在改过来了
SELECT brand,area,invoicedate,dailysalesvalue,
mtd05=SELECT SUM(dailysalevalue) FROM tb
WHERE invoicedate BETWEEN CONVERT(DATETIME,(YEAR(DATEADD(YEAR,-1,invoicedate))*10000+MONTH(DATEADD(YEAR,-1,invoicedate))*100+1)) AND DATEADD(YY,-1,invoicedate),
mtd06=SELECT SUM(dailysalevalue) FROM tb
WHERE invoicedate BETWEEN CONVERT(DATETIME,(YEAR(invoicedate)*10000+MONTH(invoicedate)*100+1)) AND invoicedate
FROM tbTop

24 楼orange_y（）回复于 2006-06-29 15:03:13 得分 0

试验过了，paoluo(一天到晚游泳的鱼)大哥的可以实现。各位，都感谢了啊。Top

25 楼zjdyzwx（十一月猪）回复于 2006-06-29 19:56:59 得分 0

create table #
(
brand varchar(2) ,
area varchar(20),
invoice datetime,
dailysalesvalue int
)

insert into #
select 'AA', 'North', '2006/06/28' , 3000 union
select 'AA', 'North', '2006/06/03' , 2000 union
select 'AA', 'North', '2005/06/03' , 1000 union
select 'AA', 'North', '2005/06/05' , 1000 union
select 'BB', 'East' , '2006/03/05' , 2000 union
select 'BB', 'East' , '2006/03/08' , 4000 union
select 'BB', 'East' , '2005/03/01' , 1000

select * from #

select cast('2006-1-1' as datetime)
select cast('2006-01-01' as datetime)
select cast('2006-01-1' as datetime)
select cast('2006-1-01' as datetime)

select cast( '2005-'+ cast(month(a.invoice) as varchar(20))+'-01' as datetime)
from # a

select a.* ,
(select sum(isnull(DailySalesValue,0))
from # where
invoice
between cast( '2005-'+ cast(month(a.invoice) as varchar(20))+'-01' as datetime)
and cast( '2005-'+ cast(month(a.invoice) as varchar(20)) +'-' +cast(day(a.invoice) as varchar(20)) as datetime) ) as MTD05 ,

(case year(invoice) when 2005 then 0
when 2006 then
(select sum(isnull(DailySalesValue,0))
from # where
invoice
between cast( '2006-'+ cast(month(a.invoice) as varchar(20))+'-01' as datetime)
and cast( '2006-'+ cast(month(a.invoice) as varchar(20)) + '-'+cast(day(a.invoice) as varchar(20)) as datetime) ) end) as MTD06

from # a

Top

26 楼lengxiaowei（小伟）回复于 2006-06-30 10:22:21 得分 0

create table #t
(
Brand varchar(2),
Area varchar(5),
InvoiceDate varchar(10),
DailySalesValue int
)
insert into #t
select 'AA','North','2006/06/28',3000 union all
select 'AA','North','2006/06/03',2000 union all
select 'AA','North','2005/06/03',1000 union all
select 'AA','North','2005/06/05',1000 union all
select 'BB','Ease','2006/03/05',2000 union all
select 'BB','Ease','2006/03/08',4000 union all
select 'BB','Ease','2005/03/01',1000

select *,
(select isnull(sum(DailySalesValue),0) from #t where InvoiceDate between cast('2005/'+cast(month(invoicedate) as varchar)+'/01' as datetime)
and cast('2005/'+right(a.InvoiceDate,5) as datetime) and Brand=a.Brand and Area=a.Area ) as [MTD05],
[MTD06]=isnull(case when year(invoicedate)=2006 then isnull((select sum(DailySalesValue) from #t where InvoiceDate between cast('2006/'+cast(month(invoicedate) as varchar)+'/01' as datetime) and
cast('2006/'+right(a.InvoiceDate,5) as datetime) and Brand=a.Brand and Area=a.Area),0) end ,0)
from #t aTop