引用 1 楼 wxg22526451 的回复:
SQL code--> Test Data: [tb1]
if object_id('[tb1]') is not null drop table [tb1]
create table [tb1] ([Type] int,[Num] int)
insert into [tb1]
select 1,1000 union all
select 1,1001 union all
select 1,1002 union all
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 2,12 union all
select 2,13 union all
select 2,14 --select * from [tb1]
--Code
select distinct Type,…
就这个sql 还可以,但是当票段是 1-200, 260 -300时,算出来的结果却是1-300
而且在几千条数据的情况下效率很差..
SQL code--> Test Data: [tb1]
if object_id('[tb1]') is not null drop table [tb1]
create table [tb1] ([Type] int,[Num] int)
insert into [tb1]
select 1,1000 union all
select 1,1001 union all
select 1,1002 union all
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 2,12 union all
select 2,13 union all
select 2,14 --select * from [tb1]
--Code
select distinct Type,…
就这个sql 还可以,但是当票段是 1-200, 260 -300时,算出来的结果却是1-300
而且在几千条数据的情况下效率很差..
解决方案 »
- 这样建表好不好
- 更新字符拼音码...?
- 谁能告诉我SQL 2005 EXPRESS的服务启动的完整路径,主要是想要那个exe文件名
- 如何删除新闻表的重复记录?
- 子陌哥哥,进来这个帖子
- 关于向存储过程传递包含字符串的字符串问题,请高手指教
- 图象
- 数据库主键int型不设自增,用max+1的方式如何克服并发的问题
- 一个非常简单的问题
- 简单问题:如何删除以某字符串为文件名开头的一类表?
- 后半部分查找不正常,5555555555555555555
- errorCode=-1073451000 description=该包中包含两个具有重复名称“输出列“ErrorCode”(64)”和“输出列“ErrorCode”(14)”的对象
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (Type INT,Num INT)
INSERT INTO @T
SELECT 1,1000 UNION ALL
SELECT 1,1001 UNION ALL
SELECT 1,1002 UNION ALL
SELECT 2,21 UNION ALL
SELECT 2,22 UNION ALL
SELECT 2,23 UNION ALL
SELECT 2,12 UNION ALL
SELECT 2,13 UNION ALL
SELECT 2,14--SQL查询如下:SELECT *,CAST(NULL AS INT) AS flag INTO # FROM @T;DECLARE @type INT,@num INT,@flag INT;
SELECT @flag = 0;UPDATE # SET
@flag = CASE WHEN type=@type AND @num = num - 1 THEN @flag ELSE @flag + 1 END,
@type = type,
@num = num,
flag = @flag;SELECT type,MIN(num) AS startNum,MAX(num) AS endNum
FROM #
GROUP BY TYPE,flagDROP TABLE #;/*
type startNum endNum
----------- ----------- -----------
1 1000 1002
2 21 23
2 12 14(3 row(s) affected)
*/
INSERT @TB
SELECT 1, 1000 UNION ALL
SELECT 1, 1001 UNION ALL
SELECT 1, 1002 UNION ALL
SELECT 2, 21 UNION ALL
SELECT 2, 22 UNION ALL
SELECT 2, 23 UNION ALL
SELECT 2, 12 UNION ALL
SELECT 2, 13 UNION ALL
SELECT 2, 14
SELECT Type,MAX(CASE WHEN SEQ=1 THEN Num END) AS StartNum,MAX(CASE WHEN SEQ=3 THEN Num END) AS EndNum
FROM (
SELECT *,SEQ=ROW_NUMBER() OVER (PARTITION BY Type,GRP ORDER BY Num)
FROM (
SELECT *,GRP=(ID-1)/3
FROM (
SELECT *,ID=ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Num)
FROM @TB) T
) T
) T
GROUP BY Type,GRP
/*
Type StartNum EndNum
----------- ----------- -----------
1 1000 1002
2 12 14
2 21 23
*/
INSERT @TB
SELECT 1, 1000 UNION ALL
SELECT 1, 1001 UNION ALL
SELECT 1, 1002 UNION ALL
SELECT 2, 21 UNION ALL
SELECT 2, 22 UNION ALL
SELECT 2, 23 UNION ALL
SELECT 2, 12 UNION ALL
SELECT 2, 13 UNION ALL
SELECT 2, 14SELECT *,ID=IDENTITY(int,1,1) INTO # FROM @TB
SELECT *,SEQ=ID-(SELECT COUNT(*) FROM # WHERE Type<T.Type) INTO #T FROM # TSELECT Type,min(Num) as Num,max(Num) as NUm
FROM #T
GROUP BY Type,(SEQ-1)/3DROP TABLE #,#T
/*
Type Num NUm
----------- ----------- -----------
1 1000 1002
2 21 23
2 12 14
*/
insert into @t select 1,1000
union select 1,1001
union select 1,1002
union select 2,21
union select 2,22
union select 2,23
union select 2,12
union select 2,13
union select 2,14 select
a.Type,a.Num as StrNum,min(b.num) as EndNum
from
(select t.* from @t t where not exists(select 1 from @t where Type=t.Type and Num=t.Num-1)) a,
(select t.* from @t t where not exists(select 1 from @t where Type=t.Type and Num=t.Num+1)) b
where
a.Type=b.Type
and
a.Num<=b.Num
group by
a.Type,a.Num
order by
a.Type,a.StrNum/*
Type StrNum EndNum
----------- ----------- -----------
1 1000 1002
2 12 14
2 21 23
*/
StartNum=(select min(num) from tb1 where Type=t.Type and len(num)=len(t.Num)),
EndNum=(select max(num) from tb1 where Type=t.Type and len(num)=len(t.Num))
from tb1 t
这句有点问题,当票段是 1-200, 260 -300时,算出来的结果却是1-300
而且在几千条数据的情况下效率很差..
哪位给调优一下
试试这个:declare @t table(Type int,Num int)
insert into @t select 1,1000
union select 1,1001
union select 1,1002
union select 2,21
union select 2,22
union select 2,23
union select 2,12
union select 2,13
union select 2,14 select
a.Type,a.Num as StrNum,min(b.num) as EndNum
from
(select m.* from @t m left join @t n on m.Type=n.Type and m.Num=n.Num+1 where n.Num is null) a,
(select m.* from @t m left join @t n on m.Type=n.Type and m.Num=n.Num-1 where n.Num is null) b
where
a.Type=b.Type
and
a.Num<=b.Num
group by
a.Type,a.Num
order by
a.Type,a.StrNum
insert into @t select 1,1000
union select 1,1001
union select 1,1002
union select 2,21
union select 2,22
union select 2,23
union select 2,12
union select 2,13
union select 2,14 select Type,min(convert(varchar(4),Num)),max(convert(varchar(4),Num))
from @t
group by type,substring(convert(varchar(4),Num),1,1)