CREATE TABLE #tbTest (a bigint ,b varchar(100))
INSERT #tbTest
SELECT 1,'sa' UNION all
SELECT 3,'sa' UNION ALL
SELECT 6,'sa' UNION ALL
SELECT 9,'b' UNION ALL
SELECT 13,'c' UNION ALL
SELECT 16,'c' UNION ALL
SELECT 19,'sa'SELECT * FROM #tbTest1 sa
3 sa
6 sa
9 b
13 c
16 c
19 sa已经 排好序的了结果:
1-6 sa
9-9 b
12-16 c
19-19 sa连续区间 相同值 的区间
INSERT #tbTest
SELECT 1,'sa' UNION all
SELECT 3,'sa' UNION ALL
SELECT 6,'sa' UNION ALL
SELECT 9,'b' UNION ALL
SELECT 13,'c' UNION ALL
SELECT 16,'c' UNION ALL
SELECT 19,'sa'SELECT * FROM #tbTest1 sa
3 sa
6 sa
9 b
13 c
16 c
19 sa已经 排好序的了结果:
1-6 sa
9-9 b
12-16 c
19-19 sa连续区间 相同值 的区间
解决方案 »
- 数据库
- 求两句SQL语句······跪求···速度
- 建议一个:【别人帮你把问题解决完了,为何不结贴了。忘了?光说谢不接帖(:】
- 在数据库里有个负数乘以正数怎样才能得到一个正数?
- 是不是查询包含两个或以上就一定存在连接查询
- 求教:SQL2000客户端无法连接服务器
- 如何在查询所有列的同时修改某一列的值?sql语句怎么写?
- 怎么设计家族谱式的数据库表
- 怎样在没有安装SQL Server的机器上检测出局域网上所有当前活动的SQL Server服务器?
- 一个问题 请问如何能准确的实现上下篇文章查询?
- sql server 2012 express localDB
- 对表1按课程编号分组,找出最小星期的那条记录,请前辈指导!先感谢前辈了!
goCREATE TABLE #tbTest (a bigint ,b varchar(100))
INSERT #tbTest
SELECT 1,'sa' UNION all
SELECT 3,'sa' UNION ALL
SELECT 6,'sa' UNION ALL
SELECT 9,'b' UNION ALL
SELECT 13,'c' UNION ALL
SELECT 16,'c' UNION ALL
SELECT 19,'sa';WITH CTE AS
(
SELECT *,rowid = ROW_NUMBER() OVER(ORDER BY a) FROM #tbTest
),
cte1 AS
(
SELECT *, gp = rowid-ROW_NUMBER() OVER(PARTITION BY b ORDER BY a) FROM cte
)
SELECT
[range]=LTRIM(MIN(a))+'-'+LTRIM(MAX(a)), b
FROM cte1
GROUP BY gp, b
ORDER BY MIN(rowid)/*
range b
1-6 sa
9-9 b
13-16 c
19-19 sa
*/
FROM #tbTest AS t1
LEFT JOIN #tbTest t2
ON t2.a = (
SELECT MIN(a)
FROM #tbTest
WHERE t1.b <> b
AND a > t1.a)
GROUP BY t1.b,t2.b
ORDER BY mi
------------------
1 6 sa
9 9 b
13 16 c
19 19 sa