在SQL查询中查找数据范围 [英] Finding Data Range in SQL Query

问题描述

你好朋友.
我有下表:

ID序列状态
1 10001 1
2 10002 1
3 10003 1
4 10004 0
5 10005 0
6 10006 1
7 10007 1
8 10008 0
9 10009 1
10 10010 1

我需要一个查询，其中我将具有状态1的串行范围.像上面的表数据一样.我需要将输出结果设置为 10001至10003,10006至10007,10009至10010 .
如何使用SQL来做到这一点.
任何帮助将不胜感激.

Hello friends.
I have the following table:

ID SERIAL STATUS
1 10001 1
2 10002 1
3 10003 1
4 10004 0
5 10005 0
6 10006 1
7 10007 1
8 10008 0
9 10009 1
10 10010 1

I need a query where i will have the serial range with status 1. Like as per the above table data..I need the out put to be 10001 to 10003,10006 to 10007,10009 to 10010.
How can I do that in using SQL.
Any help would be highly appreciated.

推荐答案

看看SQL Server 2008中新的ROLLUP选项. 它应该允许您按范围对数据进行分组.

此处 [

Have a look at the new ROLLUP option in SQL Server 2008.
It should allow you to group data by range.

Here[^] is a good example.

它可能已经过优化，但是如果您使用的是Oracle 10g或更高版本，则此查询可以解决问题:

It could probably be optimized, but this query will do the trick, if you have Oracle version 10g or later:

WITH prv_nxt AS (
    SELECT  serial
           ,Lag(status,1,0) OVER (ORDER BY serial) previous
           ,status
           ,Lead(status,1,0) OVER (ORDER BY serial) nxt
    FROM    temp
    )
,CHANGE AS ( 
    SELECT  serial
           ,1 CHANGE
    FROM    prv_nxt
    WHERE   previous = 0
    AND     status = 1
    UNION
    SELECT  serial
           ,0 CHANGE
    FROM    prv_nxt
    WHERE   status = 1
    AND     nxt = 0
    )
, start_stop AS (
    SELECT  serial startserial
           ,Lead(serial,1,NULL) OVER (ORDER BY serial) stopserial
           ,change
    FROM    CHANGE
    )
SELECT  startserial || ' to ' || stopserial range
FROM    start_stop
WHERE   CHANGE = 1

选择ID， [表格]的状态，串行状态
其中[状态] = 1

-或


从[表格]中选择ID，序列号，状态
其中[STATUS] ="1"




--OutPut


ID序列状态
1 10001 1
2 10002 1
3 10003 1
6 10006 1
7 10007 1
9 10009 1
10 10010 1



[表格]-表格名称

[STATUS] ="1"-如果[STATUS]是String或Varchar类型

Select ID, SERIAL, STATUS from [Table]
Where [STATUS] = 1

--or


Select ID, SERIAL, STATUS from [Table]
Where [STATUS] = ''1''




--OutPut


ID SERIAL STATUS
1 10001 1
2 10002 1
3 10003 1
6 10006 1
7 10007 1
9 10009 1
10 10010 1



[Table] -- name of your table

[STATUS] = ''1'' --if the [STATUS] is String or Varchar type

这篇关于在SQL查询中查找数据范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！