请教各位:
数据记录是这样的
1
2
3
4
5
12
17
18
19
20
25
请问sqlserver2000如何显示成这样1-5,12,17-20,25 /*
declare @t table(num int)
insert into @t select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 12
union all select 17
union all select 18
union all select 19
union all select 20
union all select 25 select
rtrim(a.num)+(case when min(b.num)!=a.num then '-'+rtrim(min(b.num)) else '' end)
from
(select t.num from @t t where not exists(select 1 from @t where num=t.num-1)) a,
(select t.num from @t t where not exists(select 1 from @t where num=t.num+1)) b
where
a.num<=b.num
group by a.num
*/
/*
DECLARE @t TABLE(num INT)
INSERT INTO @t
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 12 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20 UNION ALL
SELECT 25
SELECT CASE
WHEN MIN(num) = MAX(num) THEN CAST(MIN(num) AS VARCHAR)
ELSE CAST(MIN(num) AS VARCHAR) + '-' + CAST(MAX(num) AS VARCHAR)
END
FROM (
SELECT *, (num -id) AS iid
FROM (
SELECT (SELECT COUNT(1) FROM @t t1 WHERE t1.num<=t2.num) AS id,
*
FROM @t t2
) t3
) t4
GROUP BY
iid
*/
我在网上看到这个题目,然后有两个高手作了解答,但是代码的第二部分我都看不懂,谁能帮我一行一行的解释注释一下啊,在学校虚度了四年,大家不要笑话越详细越好,领导看到我这个样子,估计要解聘我了,什么都不会
数据记录是这样的
1
2
3
4
5
12
17
18
19
20
25
请问sqlserver2000如何显示成这样1-5,12,17-20,25 /*
declare @t table(num int)
insert into @t select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 12
union all select 17
union all select 18
union all select 19
union all select 20
union all select 25 select
rtrim(a.num)+(case when min(b.num)!=a.num then '-'+rtrim(min(b.num)) else '' end)
from
(select t.num from @t t where not exists(select 1 from @t where num=t.num-1)) a,
(select t.num from @t t where not exists(select 1 from @t where num=t.num+1)) b
where
a.num<=b.num
group by a.num
*/
/*
DECLARE @t TABLE(num INT)
INSERT INTO @t
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 12 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20 UNION ALL
SELECT 25
SELECT CASE
WHEN MIN(num) = MAX(num) THEN CAST(MIN(num) AS VARCHAR)
ELSE CAST(MIN(num) AS VARCHAR) + '-' + CAST(MAX(num) AS VARCHAR)
END
FROM (
SELECT *, (num -id) AS iid
FROM (
SELECT (SELECT COUNT(1) FROM @t t1 WHERE t1.num<=t2.num) AS id,
*
FROM @t t2
) t3
) t4
GROUP BY
iid
*/
我在网上看到这个题目,然后有两个高手作了解答,但是代码的第二部分我都看不懂,谁能帮我一行一行的解释注释一下啊,在学校虚度了四年,大家不要笑话越详细越好,领导看到我这个样子,估计要解聘我了,什么都不会
declare @t table(num int)
insert into @t select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 12
union all select 17
union all select 18
union all select 19
union all select 20
union all select 25select (case when max(num)=min(num) then ltrim(max(num))
else ltrim(min(num))+'-'+ltrim(min(num)) end) as num
from @t
group by (num-1)/5/***************
num
-------------------------
1-1
12
17-17
25(4 行受影响)
declare @t table(num int)
insert into @t select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 12
union all select 17
union all select 18
union all select 19
union all select 20
union all select 25select (case when max(num)=min(num) then ltrim(max(num))
else ltrim(max(num))+'-'+ltrim(min(num)) end) as num
from @t
group by (num-1)/5/********************num
-------------------------
5-1
12
20-17
25(4 行受影响)
取到非连续开始的那个值,单独取出来SELECT (SELECT COUNT(1) FROM @t t1 WHERE t1.num<=t2.num) ---计算位置
t2 为id num
1 1
2 1
3 1
4 1
5 1
12 7
17 11
18 11
19 11
20 11
25 15然后取出min(num)=max(num)最大值等于最小值的组
SELECT CASE
WHEN MIN(num) = MAX(num) THEN CAST(MIN(num) AS VARCHAR)
ELSE CAST(MIN(num) AS VARCHAR) + '-' + CAST(MAX(num) AS VARCHAR)
END
FROM (
SELECT *, (num -id) AS iid ---通过子查询,增加一列iid,iid为num-id即num的值减去(小于num值的个数),小于num值的个数通过子查询得到即count(1)
FROM (
SELECT (SELECT COUNT(1) FROM @t t1 WHERE t1.num<=t2.num) AS id, ---子查询,得出id即对应id
*
FROM @t t2
) t3
) t4
GROUP BYiid
t2 为num id
1 1
2 1
3 1
4 1
5 1
12 7
17 11
18 11
19 11
20 11
25 15相对于id,然后取出min(num)是否相等于max(num)判断组
WHEN MIN(num) = MAX(num) THEN CAST(MIN(num) AS VARCHAR)
ELSE CAST(MIN(num) AS VARCHAR) + '-' + CAST(MAX(num) AS VARCHAR)
END
FROM这个select语句是干什么用啊?,想检索出什么东西啊,then后面一个是函数一个是语句啊2.t1 ,t2不都是@t表的别名吗,怎么有id列啊3.SELECT *, (num -id) AS iid
FROM (
SELECT (SELECT COUNT(1) FROM @t t1 WHERE t1.num<=t2.num) AS id,
*
FROM @t t2
) t3这个
SELECT (SELECT COUNT(1) FROM @t t1 WHERE t1.num<=t2.num) AS id,
*
FROM @t t2子句做子查询,里面的第一个select检索出来的,不是一个int值吗,不是行数吗,怎么给个id的别名啊,里面第二个是返回t2表的所有记录,这整个select语句查出来的是什么东西啊,给了个别名t3,难道是个表吗
SELECT (SELECT COUNT(1) FROM @t t1 WHERE t1.num<=t2.num) AS id, ---子查询,得出id即对应id
*
FROM @t t23、是表,检索出来时
id num
1 1
2 2
3 3
4 4
5 5
6 12
7 17
8 18
9 19
10 20
11 25你可以每步拆分开来执行