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