求一SQL语句

zsyok 2015-01-13 09:13:34
查出最前面,重复的记录数总和
结果 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
...全文
107 7 打赏 收藏 举报
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
这个是ID减行号RN,用于判断连续用的~ 我这边用了,类似连续区间的技巧 具体你可以看下这个 http://www.cnblogs.com/Joetao/p/3842242.html 你可以单独执行每个CTE 看效果,体会一下
  • 打赏
  • 举报
回复
zsyok 2015-01-13
引用 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 是什么意思?
  • 打赏
  • 举报
回复
;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 2015-01-13
引用 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 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
  • 打赏
  • 举报
回复
myangelo 2015-01-13
可能要使用WHILE每一个查找,然后返回结果
  • 打赏
  • 举报
回复
发帖
疑难问题

2.2w+

社区成员

MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
加入社区
帖子事件
创建了帖子
2015-01-13 09:13
社区公告
暂无公告