ÇëÎÊÒ»ÌõsqlÓï¾ä - MS-SQL Server / »ù´¡Àà

±íÈçÏÂ:

ProduceID Date
1 2010-2-3
1 2010-3-4
1 2010-5-6
1 2010-7-10

ÎÒÒªµÃµ½ÕâÑùµÄ½á¹û

ProduceID Date_1 Date_2
1 2010-2-3 2010-3-4
1 2010-5-6 2010-7-10

Ò²¾ÍÊÇProduceIDÏàͬ,Dateʱ¼äÏàÁڵĽá³ÉÒ»Ìõ¼Ç¼,Èç¹û×îºóÒ»¸öDate³Éµ¥ÁË£¬ ¸ÕDate_2Ϊnull

ÇëÎÊ´ó¼ÒÒªÈçºÎʵÏÖÄØ£¿

---

SQL code:
DECLARE @a table(ProduceID INT, Date varchar(20))
insert @a select 1 ,'2010-2-3'
union all select 1 ,'2010-3-4'
union all select 1 ,'2010-5-6'
union all select 1 ,'2010-7-10'
union all select 1 ,'2010-9-10'
union all select 2 ,'2010-10-10'

DECLARE @b table(id INT,ProduceID INT,Date varchar(20))
INSERT @b
SELECT id=(SELECT count(1) from @a WHERE ProduceID=a.produceid AND date<=a.date),
*from @a a

SELECT aa.ProduceID,aa.date Date1,bb.date date2
from (SELECT * from @b WHERE id %2=1) aa
LEFT JOIN
(SELECT * from @b WHERE id %2=0 ) bb
ON aa.ProduceID=bb.ProduceID and aa.id=bb.id-1

--result
/*

ProduceID Date1 date2
----------- -------------------- --------------------
1 2010-2-3 2010-3-4
1 2010-5-6 2010-7-10
1 2010-9-10 NULL
2 2010-10-10 NULL

£¨ËùÓ°ÏìµÄÐÐÊýΪ 4 ÐУ©

*/

---

ÿÌìÒ»´Î²»¶®°ï¶¥Ñ§Ï°²