求助求助如何查询数据库里某一数值连续出现的最大次数

woshishi_456 其他  2015-07-24 04:20:18
例如:
数据库表名:sa 日期字段:da 需查询的字段:rs

rs字段中,rs=0,以及rs=999.9 都表示机器能正常运行。

现在需要查询机器最长连续正常运行了多少天要怎么查询?
...全文
330 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
woshishi_456 2015-07-30
引用 6 楼 Cherise_huang 的回复:

declare @tb table
(
    da datetime,
    RS NUMERIC(10,1)
)

INSERT INTO @tb(rs)
select 0 union all
select 0union all
select 0 union all
select 999.9 union all
select 999.9 union all
select 0.3 union all
select 999.9 union all
select 2.9 union all
select 1.2 union all
select 0 union all
select 0 union all
select 25.9 union all
select 0.5 union all
select 0 union all
select 0 union all
select 0 union all
select 0 union all
select 0.3 union all
select 9.6 union all
select 7.2 union all
select 13.5 union all
select 999.9 


;WITH cte AS(
SELECT ROW_NUMBER() over(order by getdate()) AS num,
       case 
        WHEN RS=0 OR RS=999.9  THEN RS
        ELSE NULL
    end  AS RS
from @tb
),cte1 AS (
	SELECT num-ROW_NUMBER() over(order by getdate()) AS nums,
			*
	FROM cte
	WHERE RS IS NOT NULL
),cte2 AS(
SELECT COUNT(nums) OVER(partition BY nums) AS counts,*
FROM cte1
)

SELECT MAX(counts)AS 最大次数 FROM cte2
抱歉抱歉,前几天去大理了,十分感谢,费心了
回复
woshishi_456 2015-07-30
引用 8 楼 Tiger_Zhao 的回复:
DECLARE @tb TABLE
(
    da datetime,
    RS numeric(10,1)
)

INSERT INTO @tb
SELECT '1960-03-04', 0.0 UNION ALL
SELECT '1960-03-05', 13.9 UNION ALL
SELECT '1960-03-06', 49.1 UNION ALL
SELECT '1960-03-07', 6.4 UNION ALL
SELECT '1960-03-08', 3.4 UNION ALL
SELECT '1960-03-09', 0.3 UNION ALL
SELECT '1960-03-10', 999.9 UNION ALL
SELECT '1960-03-11', 2.9 UNION ALL
SELECT '1960-03-12', 1.2 UNION ALL
SELECT '1960-03-13', 0.0 UNION ALL
SELECT '1960-03-14', 0.0 UNION ALL
SELECT '1960-03-15', 25.9 UNION ALL
SELECT '1960-03-16', 0.5 UNION ALL
SELECT '1960-03-17', 0.0 UNION ALL
SELECT '1960-03-18', 0.0 UNION ALL
SELECT '1960-03-19', 0.0 UNION ALL
SELECT '1960-03-20', 0.0 UNION ALL
SELECT '1960-03-21', 0.3 UNION ALL
SELECT '1960-03-22', 9.6 UNION ALL
SELECT '1960-03-23', 7.2 UNION ALL
SELECT '1960-03-24', 13.5 UNION ALL
SELECT '1960-03-25', 999.9

;WITH a AS (
    SELECT da,
           ROW_NUMBER() OVER(order by da) AS rn
      FROM @tb
)
,b AS (
    SELECT da,
           ROW_NUMBER() OVER(order by da) AS rn
      FROM @tb
     WHERE rs <> 0.0
       AND rs <> 999.9
)
,c AS (
    SELECT a.da,
           a.rn - b.rn AS g
      FROM a
      JOIN b
        ON a.da = b.da
)
,d AS (
    SELECT g,
           COUNT(*) 连续天数,
           MIN(da) 开始日期,
           MAX(da) 结束日期
      FROM c
  GROUP BY g
)
--SELECT * FROM d
SELECT MAX(连续天数) 最长连续天数 FROM d
中间结果d
    g    连续天数 开始日期   结束日期
----- ----------- ---------- ----------
    1           5 1960-03-05 1960-03-09
    2           2 1960-03-11 1960-03-12
    4           2 1960-03-15 1960-03-16
    8           4 1960-03-21 1960-03-24
最长连续天数
------------
           5
抱歉抱歉,这几天去大理了,十分感谢,您的回答完美的解决了我的问题
回复
Tiger_Zhao 2015-07-27
DECLARE @tb TABLE
(
da datetime,
RS numeric(10,1)
)

INSERT INTO @tb
SELECT '1960-03-04', 0.0 UNION ALL
SELECT '1960-03-05', 13.9 UNION ALL
SELECT '1960-03-06', 49.1 UNION ALL
SELECT '1960-03-07', 6.4 UNION ALL
SELECT '1960-03-08', 3.4 UNION ALL
SELECT '1960-03-09', 0.3 UNION ALL
SELECT '1960-03-10', 999.9 UNION ALL
SELECT '1960-03-11', 2.9 UNION ALL
SELECT '1960-03-12', 1.2 UNION ALL
SELECT '1960-03-13', 0.0 UNION ALL
SELECT '1960-03-14', 0.0 UNION ALL
SELECT '1960-03-15', 25.9 UNION ALL
SELECT '1960-03-16', 0.5 UNION ALL
SELECT '1960-03-17', 0.0 UNION ALL
SELECT '1960-03-18', 0.0 UNION ALL
SELECT '1960-03-19', 0.0 UNION ALL
SELECT '1960-03-20', 0.0 UNION ALL
SELECT '1960-03-21', 0.3 UNION ALL
SELECT '1960-03-22', 9.6 UNION ALL
SELECT '1960-03-23', 7.2 UNION ALL
SELECT '1960-03-24', 13.5 UNION ALL
SELECT '1960-03-25', 999.9

;WITH a AS (
SELECT da,
ROW_NUMBER() OVER(order by da) AS rn
FROM @tb
)
,b AS (
SELECT da,
ROW_NUMBER() OVER(order by da) AS rn
FROM @tb
WHERE rs <> 0.0
AND rs <> 999.9
)
,c AS (
SELECT a.da,
a.rn - b.rn AS g
FROM a
JOIN b
ON a.da = b.da
)
,d AS (
SELECT g,
COUNT(*) 连续天数,
MIN(da) 开始日期,
MAX(da) 结束日期
FROM c
GROUP BY g
)
--SELECT * FROM d
SELECT MAX(连续天数) 最长连续天数 FROM d

中间结果d
    g    连续天数 开始日期   结束日期
----- ----------- ---------- ----------
1 5 1960-03-05 1960-03-09
2 2 1960-03-11 1960-03-12
4 2 1960-03-15 1960-03-16
8 4 1960-03-21 1960-03-24

最长连续天数
------------
5
回复
Cherise_huang 2015-07-24
回复
Cherise_huang 2015-07-24

declare @tb table
(
    da datetime,
    RS NUMERIC(10,1)
)

INSERT INTO @tb(rs)
select 0 union all
select 0union all
select 0 union all
select 999.9 union all
select 999.9 union all
select 0.3 union all
select 999.9 union all
select 2.9 union all
select 1.2 union all
select 0 union all
select 0 union all
select 25.9 union all
select 0.5 union all
select 0 union all
select 0 union all
select 0 union all
select 0 union all
select 0.3 union all
select 9.6 union all
select 7.2 union all
select 13.5 union all
select 999.9 


;WITH cte AS(
SELECT ROW_NUMBER() over(order by getdate()) AS num,
       case 
        WHEN RS=0 OR RS=999.9  THEN RS
        ELSE NULL
    end  AS RS
from @tb
),cte1 AS (
	SELECT num-ROW_NUMBER() over(order by getdate()) AS nums,
			*
	FROM cte
	WHERE RS IS NOT NULL
),cte2 AS(
SELECT COUNT(nums) OVER(partition BY nums) AS counts,*
FROM cte1
)

SELECT MAX(counts)AS 最大次数 FROM cte2
回复
woshishi_456 2015-07-24
引用 1 楼 Cherise_huang 的回复:
是要查询0或者999.9,或者0,999.9出现连续的次数吗
求0和999.9连续出现的最大次数
回复
woshishi_456 2015-07-24
引用 2 楼 Tiger_Zhao 的回复:
不要重复提问
或者有什么不对的,贴测试数据出来啊。


抱歉啊,因为上一个贴中问题没解决我就结了贴,您看代码是这样的
with cte as(
select da-ROW_NUMBER() over(order by getdate()) as row,da,RS
from
(select da,
case RS
WHEN 0.0 THEN NULL
WHEN 999.9 THEN NULL
ELSE RS
end AS RS
from chuxiong) a
where RS is null and da between '1960-03-04' and '1964-05-04'
),cte2 as(
select
COUNT(*) over(partition by row) as [连续总计],
CONVERT(CHAR(10),da,121) as [日期],RS as 降雨
from cte)
select *
from cte2
where [连续总计]=(select MAX([连续总计]) from cte2)


运行之后的结果是这样的

日期出现了不连续,
请问要怎么解决这个问题呢
回复
woshishi_456 2015-07-24
引用 1 楼 Cherise_huang 的回复:
是要查询0或者999.9,或者0,999.9出现连续的次数吗
0和999.9都代表正常运行,比如今天是0,明天是999.9,后天是0,大后天是0,那么就表示机器正常运行了4天。0.1到999.8其中的任意一个数字表示机器不正常。有时候连续出现0,有时候连续出现999.9 ,有时候0和999.9混合出现(这三种都是连续正常运行),在某个时间段里只要其中没有出现0或者999.9以外的数字,就表示机器是正常运行的。
回复
Tiger_Zhao 2015-07-24
不要重复提问
或者有什么不对的,贴测试数据出来啊。
回复
Cherise_huang 2015-07-24
是要查询0或者999.9,或者0,999.9出现连续的次数吗
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-07-24 04:20
社区公告
暂无公告