求一SQL语句

[zsyok]

查出最前面，重复的记录数总和
结果 2，3，1，2 （即字段1的 A为2，字段2的C为3，字段3的D为1，字段4的A为2）

ID 字段1 字段2 字段3 字段4
1 A C D A
2 A C A A
3 B C E E
4 C E C E
5 B F C F
6 B G E F
7 C A C F
8 B A C F
9 A E E G

[myangelo]

可能要使用WHILE每一个查找,然后返回结果

[还在加载中灬]

;WITH CTE AS(
	SELECT ID
		,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY ID)RN1
		,ROW_NUMBER()OVER(PARTITION BY 字段2 ORDER BY ID)RN2
		,ROW_NUMBER()OVER(PARTITION BY 字段3 ORDER BY ID)RN3
		,ROW_NUMBER()OVER(PARTITION BY 字段4 ORDER BY ID)RN4
	FROM TB
)
,CTE2 AS(
	SELECT ID
		,COUNT(1)OVER(PARTITION BY ID-RN1)C1
		,COUNT(1)OVER(PARTITION BY ID-RN2)C2
		,COUNT(1)OVER(PARTITION BY ID-RN3)C3
		,COUNT(1)OVER(PARTITION BY ID-RN4)C4
	FROM CTE
)
SELECT * FROM CTE2
WHERE ID=1

[zsyok]

引用 2 楼 ky_min 的回复:

;WITH CTE AS(
	SELECT ID
		,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY ID)RN1
		,ROW_NUMBER()OVER(PARTITION BY 字段2 ORDER BY ID)RN2
		,ROW_NUMBER()OVER(PARTITION BY 字段3 ORDER BY ID)RN3
		,ROW_NUMBER()OVER(PARTITION BY 字段4 ORDER BY ID)RN4
	FROM TB
)
,CTE2 AS(
	SELECT ID
		,COUNT(1)OVER(PARTITION BY ID-RN1)C1
		,COUNT(1)OVER(PARTITION BY ID-RN2)C2
		,COUNT(1)OVER(PARTITION BY ID-RN3)C3
		,COUNT(1)OVER(PARTITION BY ID-RN4)C4
	FROM CTE
)
SELECT * FROM CTE2
WHERE ID=1

如果ID按倒叙 呢？查出 1，1，1，1

[还在加载中灬]

;WITH CTE AS(
	SELECT *
		,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY ID)RN1
		,ROW_NUMBER()OVER(PARTITION BY 字段2 ORDER BY ID)RN2
		,ROW_NUMBER()OVER(PARTITION BY 字段3 ORDER BY ID)RN3
		,ROW_NUMBER()OVER(PARTITION BY 字段4 ORDER BY ID)RN4
	FROM TB
)
,CTE2 AS(
	SELECT ID
		,COUNT(1)OVER(PARTITION BY ID-RN1,字段1)C1
		,COUNT(1)OVER(PARTITION BY ID-RN2,字段2)C2
		,COUNT(1)OVER(PARTITION BY ID-RN3,字段3)C3
		,COUNT(1)OVER(PARTITION BY ID-RN4,字段4)C4
	FROM CTE
)
SELECT TOP 1 * FROM CTE2
ORDER BY ID DESC

修复之前的， 然后，按倒的就用DESC TOP1 ~~

[zsyok]

引用 4 楼 ky_min 的回复:

;WITH CTE AS(
	SELECT *
		,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY ID)RN1
		,ROW_NUMBER()OVER(PARTITION BY 字段2 ORDER BY ID)RN2
		,ROW_NUMBER()OVER(PARTITION BY 字段3 ORDER BY ID)RN3
		,ROW_NUMBER()OVER(PARTITION BY 字段4 ORDER BY ID)RN4
	FROM TB
)
,CTE2 AS(
	SELECT ID
		,COUNT(1)OVER(PARTITION BY ID-RN1,字段1)C1
		,COUNT(1)OVER(PARTITION BY ID-RN2,字段2)C2
		,COUNT(1)OVER(PARTITION BY ID-RN3,字段3)C3
		,COUNT(1)OVER(PARTITION BY ID-RN4,字段4)C4
	FROM CTE
)
SELECT TOP 1 * FROM CTE2
ORDER BY ID DESC

修复之前的， 然后，按倒的就用DESC TOP1 ~~

还想问下 ID-RN 是什么意思？

[还在加载中灬]

这个是ID减行号RN，用于判断连续用的~ 我这边用了，类似连续区间的技巧 具体你可以看下这个 http://www.cnblogs.com/Joetao/p/3842242.html 你可以单独执行每个CTE 看效果，体会一下