- 爱易网页
-
MSSQL教程
- 大家來幫小弟我看下這個SQL如何寫?
日期:2014-05-19 浏览次数:20551 次
大家來幫我看下這個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