CREATE TABLE #temp (ID int,NAME1 int,NAME2 CHAR(1))INSERT INTO #temp SELECT 1, 1000,'A' UNION ALL SELECT 2, 1000,'B' UNION ALL SELECT 3, 1000,'C' UNION ALL SELECT 4, 1001,'D' UNION ALL SELECT 5, 1002,'E' UNION ALL SELECT 6, 1003,'F' UNION ALL SELECT 7, 1004,'G' UNION ALL SELECT 8, 1005,'H' UNION ALL SELECT 9, 1006,'I' UNION ALL SELECT 10, 1007,'J' UNION ALL SELECT 11, 1008,'K' UNION ALL SELECT 12, 1009,'L' UNION ALL SELECT 13, 1000,'M' UNION ALL SELECT 14, 1001,'N' UNION ALL SELECT 15, 1001,'O' UNION ALL SELECT 16, 1008,'P' UNION ALL SELECT 17, 1009,'Q' UNION ALL SELECT 18, 1005,'R' UNION ALL SELECT 19, 1005,'S' UNION ALL SELECT 20, 1005,'T' UNION ALL SELECT 21, 1008,'U' UNION ALL SELECT 22, 1009,'V' UNION ALL SELECT 23, 1000,'W' UNION ALL SELECT 24, 1000,'X' UNION ALL SELECT 25, 1000,'Y' UNION ALL SELECT 26, 1000,'Z' SELECT a.* ,CASE WHEN a.id=a.id2 THEN '' ELSE CAST(b.id AS VARCHAR(10)) END id ,CASE WHEN a.id=a.id2 THEN '' ELSE CAST(b.name1 AS VARCHAR(10)) END name1 ,CASE WHEN a.id=a.id2 THEN '' ELSE b.name2 END name2 FROM ( SELECT a.*,B.id2 FROM #temp a JOIN ( select name1,MIN(id) id1,MAX(id) id2 from #temp t where id IN (select TOP 2 id from #temp where name1=t.name1 ORDER BY id desc) GROUP BY name1 ) b ON a.id=b.id1 ) a JOIN #temp b ON a.id2=b.id ORDER BY 2
--2000没有ROW_NUMBER(),只能用临时表的自增列来求最大、次大。 CREATE TABLE #temp1 ( i int identity, id int, name1 int, name2 varchar(5) ) GO INSERT INTO #temp1 (id,name1,name2) SELECT * FROM table1 ORDER BY name1,name2 DESC,id SELECT name1, MIN(i) i INTO #temp2 FROM #temp1 GROUP BY name1 SELECT t1.id, t1.name1, t1.name2, t2.id, t2.name1, t2.name2 FROM #temp2 t0 JOIN #temp1 t1 ON t1.i = t0.i LEFT JOIN #temp1 t2 ON t2.i = t0.i+1 AND t2.name1 = t0.name1
DROP TABLE #temp1 DROP TABLE #temp2 id name1 name2 id name1 name2 ----------- ----------- ----- ----------- ----------- ----- 26 1000 Z 27 1000 Z 15 1001 O 14 1001 N 5 1002 E NULL NULL NULL 6 1003 F NULL NULL NULL 7 1004 G NULL NULL NULL 20 1005 T 19 1005 S 9 1006 I NULL NULL NULL 10 1007 J NULL NULL NULL 21 1008 U 16 1008 P 22 1009 V 17 1009 Q
CREATE TABLE #temp (ID int,NAME1 int,NAME2 CHAR(1))INSERT INTO #temp
SELECT 1, 1000,'A' UNION ALL
SELECT 2, 1000,'B' UNION ALL
SELECT 3, 1000,'C' UNION ALL
SELECT 4, 1001,'D' UNION ALL
SELECT 5, 1002,'E' UNION ALL
SELECT 6, 1003,'F' UNION ALL
SELECT 7, 1004,'G' UNION ALL
SELECT 8, 1005,'H' UNION ALL
SELECT 9, 1006,'I' UNION ALL
SELECT 10, 1007,'J' UNION ALL
SELECT 11, 1008,'K' UNION ALL
SELECT 12, 1009,'L' UNION ALL
SELECT 13, 1000,'M' UNION ALL
SELECT 14, 1001,'N' UNION ALL
SELECT 15, 1001,'O' UNION ALL
SELECT 16, 1008,'P' UNION ALL
SELECT 17, 1009,'Q' UNION ALL
SELECT 18, 1005,'R' UNION ALL
SELECT 19, 1005,'S' UNION ALL
SELECT 20, 1005,'T' UNION ALL
SELECT 21, 1008,'U' UNION ALL
SELECT 22, 1009,'V' UNION ALL
SELECT 23, 1000,'W' UNION ALL
SELECT 24, 1000,'X' UNION ALL
SELECT 25, 1000,'Y' UNION ALL
SELECT 26, 1000,'Z'
SELECT a.*
,CASE WHEN a.id=a.id2 THEN '' ELSE CAST(b.id AS VARCHAR(10)) END id
,CASE WHEN a.id=a.id2 THEN '' ELSE CAST(b.name1 AS VARCHAR(10)) END name1
,CASE WHEN a.id=a.id2 THEN '' ELSE b.name2 END name2
FROM
(
SELECT a.*,B.id2
FROM #temp a
JOIN
(
select name1,MIN(id) id1,MAX(id) id2 from #temp t
where id IN (select TOP 2 id from #temp where name1=t.name1 ORDER BY id desc)
GROUP BY name1
) b ON a.id=b.id1
) a
JOIN #temp b ON a.id2=b.id
ORDER BY 2
CREATE TABLE #temp1 (
i int identity,
id int,
name1 int,
name2 varchar(5)
)
GO INSERT INTO #temp1 (id,name1,name2)
SELECT *
FROM table1
ORDER BY name1,name2 DESC,id SELECT name1, MIN(i) i
INTO #temp2
FROM #temp1
GROUP BY name1 SELECT t1.id, t1.name1, t1.name2,
t2.id, t2.name1, t2.name2
FROM #temp2 t0
JOIN #temp1 t1
ON t1.i = t0.i
LEFT JOIN #temp1 t2
ON t2.i = t0.i+1
AND t2.name1 = t0.name1
DROP TABLE #temp1
DROP TABLE #temp2
id name1 name2 id name1 name2
----------- ----------- ----- ----------- ----------- -----
26 1000 Z 27 1000 Z
15 1001 O 14 1001 N
5 1002 E NULL NULL NULL
6 1003 F NULL NULL NULL
7 1004 G NULL NULL NULL
20 1005 T 19 1005 S
9 1006 I NULL NULL NULL
10 1007 J NULL NULL NULL
21 1008 U 16 1008 P
22 1009 V 17 1009 Q