Sql2005 以下如何分组

[蓝宝石工作室]

表结构数据是这样的
日期 制单号 工序 生产日期 流水线 数量
2011-03-18 00:00:00 P1103049CC 80 2011-03-18 08:16:53.897 306 1097
2011-03-21 00:00:00 P1103049CD 80 2011-03-21 08:14:02.647 306 1006
2011-03-23 00:00:00 P1103049EB 80 2011-03-23 08:20:29.640 306 143
2011-03-23 00:00:00 P1103049GB 80 2011-03-23 18:17:03.517 308 217
2011-03-24 00:00:00 P1103049EA 80 2011-03-24 11:54:22.317 308 262
2011-03-24 00:00:00 P1103049GB 80 2011-03-24 08:14:17.460 308 1195
2011-03-26 00:00:00 P1103049ED 80 2011-03-26 08:33:38.997 308 959
2011-03-28 00:00:00 P1103049GB 80 2011-03-28 08:17:59.790 308 1322
2011-04-01 00:00:00 P1103049GC 80 2011-04-01 11:08:51.080 308 723
2011-04-01 00:00:00 P1103049GF 80 2011-04-01 08:19:03.163 308 710
2011-04-02 00:00:00 P1103049GF 80 2011-04-02 08:16:43.080 308 1214

按制单号分组，当制单号连续的时候才分，比如
A
A
B
C
C
A
我想实现这样的
A
B
C
A

[Shawn]

CREATE TABLE #temp
(
	[name] CHAR(1)
)
INSERT #temp
SELECT 'A' UNION ALL SELECT 'A' UNION ALL
SELECT 'B' UNION ALL SELECT 'C' UNION ALL
SELECT 'C' UNION ALL SELECT 'A'
GO
--SQL:
SELECT [name] FROM
(
	SELECT 
		rowno=ROW_NUMBER() OVER(ORDER BY GETDATE()), 
		groupno=ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY GETDATE()),
		*
	FROM #temp
) T
GROUP BY [name], rowno-groupno
ORDER BY MIN(rowno)
/*
name
A
B
C
A
*/

[蓝宝石工作室]

日期 制单号 工序 生产日期 流水线 数量
2011-03-18 00:00:00 P1103049CC 80 2011-03-18 08:16:53.897 306 1097
2011-03-21 00:00:00 P1103049CD 80 2011-03-21 08:14:02.647 306 1006
2011-03-23 00:00:00 P1103049EB 80 2011-03-23 08:20:29.640 306 143
2011-03-23 00:00:00 P1103049GB 80 2011-03-23 18:17:03.517 308 217
2011-03-24 00:00:00 P1103049EA 80 2011-03-24 11:54:22.317 308 262
2011-03-24 00:00:00 P1103049GB 80 2011-03-24 08:14:17.460 308 1195
2011-03-26 00:00:00 P1103049ED 80 2011-03-26 08:33:38.997 308 959
2011-03-28 00:00:00 P1103049GB 80 2011-03-28 08:17:59.790 308 1322
2011-04-01 00:00:00 P1103049GC 80 2011-04-01 11:08:51.080 308 723
怎么筛选呢，小弟我很菜

[Shawn]

仅供参考:

;WITH cte AS
(
    SELECT 
        rowno=ROW_NUMBER() OVER(ORDER BY GETDATE()), 
        groupno=ROW_NUMBER() OVER(PARTITION BY 制单号 ORDER BY GETDATE()),
        *
    FROM tb
)
SELECT * FROM
(SELECT 制单号, diffno=rowno-groupno, rowno=MIN(rowno) FROM cte GROUP BY 制单号, rowno-groupno) a --或用max(rowno)
CROSS APPLY
(SELECT TOP(1) * FROM cte WHERE rowno=a.rowno) b
ORDER BY a.rowno

[蓝宝石工作室]

谢谢，现在就是想把日期加上
P1103049CC 80 2011-03-14 08:18:46.740 306 5710
P1103049CD 80 2011-03-19 11:46:21.593 306 2311
P1103049EB 80 2011-03-22 16:02:37.037 306 364
P1103049GB 80 2011-03-23 09:42:25.020 306 594
P1103049EB 80 2011-03-23 08:20:29.640 306 143
P1103049GB 80 2011-03-23 18:17:03.517 308 217
P1103049EA 80 2011-03-24 11:54:22.317 308 262
P1103049GB 80 2011-03-24 08:14:17.460 308 1195
P1103049EC 80 2011-03-25 11:31:42.947 308 534
P1103049ED 80 2011-03-25 20:45:55.860 308 28
P1103049GB 80 2011-03-25 08:18:22.637 308 1302
P1103049ED 80 2011-03-26 08:33:38.997 308 959

[lddlq0810]

[Quote=引用 3 楼 wwwwgou 的回复:]

仅供参考:
SQL code
;WITH cte AS
(
SELECT
rowno=ROW_NUMBER() OVER(ORDER BY GETDATE()),
groupno=ROW_NUMBER() OVER(PARTITION BY 制单号 ORDER BY GETDATE()),
*
FROM tb
)
SELEC……
[/Quote]
+1