大家來幫我看下這個SQL怎么寫???
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE= 'U ' AND NAME= 'T ')
DROP TABLE T
CREATE TABLE T
(
ITEM_CODE VARCHAR(20),
PRICE NUMERIC(10,2),
DATE1 VARCHAR(10)
)
INSERT INTO T
SELECT '1001 ',2.3, '2007-07-06 ' UNION ALL
SELECT '1001 ',1.1, '2007-05-02 ' UNION ALL
SELECT '1002 ',3.6, '2007-07-07 ' UNION ALL
SELECT '1003 ',1.5, '2007-07-13 ' UNION ALL
SELECT '1003 ',1.5, '2007-06-01 ' UNION ALL
SELECT '1003 ',1.5, '2007-06-01 ' UNION ALL
SELECT '1003 ',1.5, '2007-05-08 ' UNION ALL
SELECT '1004 ',2.5, '2007-06-30 ' UNION ALL
SELECT '1004 ',2.5, '2007-06-30 ' UNION ALL
SELECT '1004 ',3.6, '2007-07-01 ' UNION ALL
SELECT '1004 ',3.0, '2007-05-01 '
我想得到以下結果:
ITEM_CODE LAST_DATE LAST_PRICE PRIOR_DATE PRIOR_PRICE 相差
1001 2007-07-06 2.3 2007-05-02 1.1 1.2
1002 2007-07-07 3.6 2007-07-07 3.6 0
1003 2007-07-13 1.5 2007-06-01 1.5 0
1004 2007-07-01 3.6 2007-06-30 2.5 1.1
註：
通過結果，大家都看出來了,last_date 是每個料號的最大日期,prior_price 是第二大日期
如果 只有一條記錄如1002 prior_date 就取最大日期
如果有多條相同的記錄如 1003,1004,我在這里只列了三個字段,其實在表中還有好多不同字段,所以
不能用distinct去重復,對於這樣的記錄，隨便取一條好了
這個表比較大，我自己寫了個，總覺得效果不太好，不知大家有什么好的寫法
謝謝!
[解决办法]
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE= 'U ' AND NAME= 'T ')
DROP TABLE T
CREATE TABLE T
(
ITEM_CODE VARCHAR(20),
PRICE NUMERIC(10,2),
DATE1 VARCHAR(10)
)
INSERT INTO T
SELECT '1001 ',2.3, '2007-07-06 ' UNION ALL
SELECT '1001 ',1.1, '2007-05-02 ' UNION ALL
SELECT '1002 ',3.6, '2007-07-07 ' UNION ALL
SELECT '1003 ',1.5, '2007-07-13 ' UNION ALL
SELECT '1003 ',1.5, '2007-06-01 ' UNION ALL
SELECT '1003 ',1.5, '2007-06-01 ' UNION ALL
SELECT '1003 ',1.5, '2007-05-08 ' UNION ALL
SELECT '1004 ',2.5, '2007-06-30 ' UNION ALL
SELECT '1004 ',2.5, '2007-06-30 ' UNION ALL
SELECT '1004 ',3.6, '2007-07-01 ' UNION ALL
SELECT '1004 ',3.0, '2007-05-01 '
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 A.ITEM_CODE
GO
Drop Table T
--Result
/*
ITEM_CODELAST_DATELAST_PRICEPRIOR_DATEPRIOR_PRICE相差
10012007-07-062.302007-05-021.101.20
10022007-07-073.602007-07-073.60.00
10032007-07-131.502007-06-011.50.00
10042007-07-013.602007-06-302.501.10
*/
