大家來幫我看下這個SQL怎么寫??? (二)請魚兄來看看
CREATE TABLE T
(accou_no varchar(10),
item_code varchar(25),
currency varchar(10),
price numeric(8,2),
date1 datetime
)
insert into t
select '10017 ', 'E01XC6371A501PR ', 'HKD ',1.8500000000000001, '2006-10-19 00:00:00.000 ' union all
select '10017 ', 'E01XC6371A501PR ', 'HKD ', 1.75 , '2007-03-13 00:00:00.000 ' union all
select '10017 ', 'E01XC6371A501PR ', 'HKD ',1.6499999999999999, '2007-07-04 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.0 , '2006-09-08 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.0 , '2006-09-08 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.112 , '2006-09-16 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.112 , '2006-09-16 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.112 , '2006-09-16 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.112 , '2006-09-16 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.112 , '2007-07-13 00:00:00.000 ' union all
select '10443 ', 'E06S1623A-T1B-L7-A ', 'HKD ',6.6000000000000003E-2, '2006-10-27 00:00:00.000 ' union all
select '10443 ', 'E06S1623A-T1B-L7-A ', 'HKD ',5.8000000000000003E-2, '2007-06-20 00:00:00.000 ' union all
select '10443 ', 'E06S1623A-T1B-L7-A ', 'HKD ',5.9499999999999997E-2, '2007-07-05 00:00:00.000 ' union all
select '10638 ', 'E11234BCSN-01 ' , 'RMB ',0.29999999999999999, '2007-06-14 00:00:00.000 ' union all
select '10638 ', 'E11234BCSN-01 ' , 'RMB ',0.32000000000000001, '2007-07-04 00:00:00.000 ' union all
select '10654 ', 'E06S1623A-T1B-L7-A ', 'RMB ',6.4000000000000001E-2, '2007-01-06 00:00:00.000 ' union all
select '10654 ', 'E06S1623A-T1B-L7-A ', 'RMB ',0.062 , '2007-04-17 00:00:00.000 ' union all
select '10654 ', 'E06S1623A-T1B-L7-A ', 'RMB ',5.9999999999999998E-2, '2007-04-26 00:00:00.000 '
Select
Distinct
A.ITEM_CODE,
A.DATE1 As LAST_DATE,
A.PRICE As LAST_PRICE,
IsNull(C.DATE1, A.DATE1) As PRIOR_DATE,
IsNull(C.PRICE, A.PRICE) As PRIOR_PRICE,
A.PRICE - IsNull(C.PRICE, A.PRICE) As 相差
From
T A
Inner Join
(Select ITEM_CODE, Max(DATE1) As DATE1 From T Group By ITEM_CODE) B
On A.ITEM_CODE = B.ITEM_CODE And A.DATE1 = B.DATE1
Left Join T C
On A.ITEM_CODE = C.ITEM_CODE And C.DATE1 = (Select TOP 1 DATE1 From T Where ITEM_CODE = C.ITEM_CODE And DATE1 < A.DATE1)
Order By A.ITEM_CODE
結果：
ITEM_CODE LAST_DATE LAST_PRICE PRIOR_DATE PRIOR_PRICE 相差
E01C1001B 2007-07-13 00:00:00.000 .11 2006-09-08 00:00:00.000 .00 .11
E01XC6371A501PR 2007-07-04 00:00:00.000 1.65 2006-10-19 00:00:00.000 1.85 -.20
E06S1623A-T1B-L7-A 2007-07-05 00:00:00.000 .06 2006-10-27 00:00:00.000 .07 -.01
E11234BCSN-01 2007-07-04 00:00:00.000 .32 2007-06-14 00:00:00.000 .30 .02
(4 row(s) affected)
實際上應是:
ITEM_CODE LAST_DATE LAST_PRICE PRIOR_DATE PRIOR_PRICE 相差
E01C1001B 2007-07-13 00:00:00.000 .11 2006-09-16 00:00:00.000 .11 0
E01XC6371A501PR 2007-07-04 00:00:00.000 1.65 2007-03-13 00:00:00.000 1.75 -.10
E06S1623A-T1B-L7-A 2007-07-05 00:00:00.000 .06 2007-06-20 00:00:00.000 .20 0
E11234BCSN-01 2007-07-05 00:00:00.000 .32 2007-06-14 00:00:00.000 .30 .02
不知為什么啊，第一個貼 測試都可以的為什么實際中又不行了，
只有E11234BCSN-01是正確的，其它三個沒有求到第二個最大日期
[解决办法]
--不好意思，發現致命失誤，在那個帖子中完全是巧合。
CREATE TABLE T
(accou_no varchar(10),
item_code varchar(25),
currency varchar(10),
price numeric(8,2),
date1 datetime
)
insert into t
select '10017 ', 'E01XC6371A501PR ', 'HKD ',1.8500000000000001, '2006-10-19 00:00:00.000 ' union all
select '10017 ', 'E01XC6371A501PR ', 'HKD ', 1.75 , '2007-03-13 00:00:00.000 ' union all
select '10017 ', 'E01XC6371A501PR ', 'HKD ',1.6499999999999999, '2007-07-04 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.0 , '2006-09-08 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.0 , '2006-09-08 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.112 , '2006-09-16 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.112 , '2006-09-16 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.112 , '2006-09-16 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.112 , '2006-09-16 00:00:00.000 ' union all
select '10410 ', 'E01C1001B ' , 'HKD ',0.112 , '2007-07-13 00:00:00.000 ' union all
select '10443 ', 'E06S1623A-T1B-L7-A ', 'HKD ',6.6000000000000003E-2, '2006-10-27 00:00:00.000 ' union all
select '10443 ', 'E06S1623A-T1B-L7-A ', 'HKD ',5.8000000000000003E-2, '2007-06-20 00:00:00.000 ' union all
select '10443 ', 'E06S1623A-T1B-L7-A ', 'HKD ',5.9499999999999997E-2, '2007-07-05 00:00:00.000 ' union all
select '10638 ', 'E11234BCSN-01 ' , 'RMB ',0.29999999999999999, '2007-06-14 00:00:00.000 ' union all
select '10638 ', 'E11234BCSN-01 ' , 'RMB ',0.32000000000000001, '2007-07-04 00:00:00.000 ' union all
select '10654 ', 'E06S1623A-T1B-L7-A ', 'RMB ',6.4000000000000001E-2, '2007-01-06 00:00:00.000 ' union all
select '10654 ', 'E06S1623A-T1B-L7-A ', 'RMB ',0.062 , '2007-04-17 00:00:00.000 ' union all
select '10654 ', 'E06S1623A-T1B-L7-A ', 'RMB ',5.9999999999999998E-2, '2007-04-26 00:00:00.000 '
GO
Select
Distinct
A.ITEM_CODE,
A.DATE1 As LAST_DATE,
A.PRICE As LAST_PRICE,
IsNull(C.DATE1, A.DATE1) As PRIOR_DATE,
IsNull(C.PRICE, A.PRICE) As PRIOR_PRICE,
A.PRICE - IsNull(C.PRICE, A.PRICE) As 相差
From
T A
Inner Join
(Select ITEM_CODE, Max(DATE1) As DATE1 From T Group By ITEM_CODE) B
On A.ITEM_CODE = B.ITEM_CODE And A.DATE1 = B.DATE1
Left Join T C
On A.ITEM_CODE = C.ITEM_CODE And C.DATE1 = (Select TOP 1 DATE1 From T Where ITEM_CODE = C.ITEM_CODE And DATE1 < A.DATE1 Order By DATE1 Desc)--少了排序
Order By A.ITEM_CODE
GO
Drop Table T
