1.数据库中a表有4个字段,ID, 日期(rq),产量(cl),不良数(bls)
2.现在要查询“不良数”字段连续为0的最长的天数以及它的启始时间,终止时间(字段 不良数为0则表示当天无不良)
3.日期不是连续的。
例如:
表a
ID rq cl bls
1 2013.05.01 200 1
2 2013.05.02 200 0
3 2013.05.03 200 3
4 2013.05.06 200 2
5 2013.05.07 200 0
6 2013.05.08 200 0
7 2013.05.10 200 0
8 2013.05.11 200 1 如上表中字段bls连续为0的最长的天数是3(即ID为5,6,7的三天)
最终结果应该是
开始时间 终止时间 天数
2013.05.07 2013.05.10 3
谢谢啦!
2.现在要查询“不良数”字段连续为0的最长的天数以及它的启始时间,终止时间(字段 不良数为0则表示当天无不良)
3.日期不是连续的。
例如:
表a
ID rq cl bls
1 2013.05.01 200 1
2 2013.05.02 200 0
3 2013.05.03 200 3
4 2013.05.06 200 2
5 2013.05.07 200 0
6 2013.05.08 200 0
7 2013.05.10 200 0
8 2013.05.11 200 1 如上表中字段bls连续为0的最长的天数是3(即ID为5,6,7的三天)
最终结果应该是
开始时间 终止时间 天数
2013.05.07 2013.05.10 3
谢谢啦!
select row_number() over (order by uploadtime) as aID,
uploadtime, measurementVal into Test_A from
(
select a.*
from
(
select uploadtime,
Cast(measurementVal as numeric(18,4)) as measurementVal
from T_011001_0001
) a)
b
order by uploadtime
--插入test_B表
select row_number() over (order by uploadtime) as aID,
uploadtime, measurementVal into Test_B from
(
select '1980-01-01' as uploadtime ,0 measurementVal
union
select a.*
from
(
select uploadtime,
Cast(measurementVal as numeric(18,4)) as measurementVal
from T_011001_0001
) a)
b
order by uploadtime
order by uploadtime
Create View Test_V_A
as
select * from
(
select Test_A.aid ,Test_B.uploadTime,
test_A.measurementVal as measurementVal_P,
test_B.measurementVal as measurementVal_T
from Test_A
inner join
Test_B
on Test_A.aid=Test_B.aid)
t select * from Test_V_A --where measurementVal_P-measurementVal_T<0
order by Test_A.aid
这个脚本 是以前别人给我写的 名字叫相邻2行之差计算脚本不知道对你有没有帮助.感觉跟你的意思差不多 你看下
BEGIN
DROP TABLE T_CSDN_ONE;
END
GO
CREATE TABLE T_CSDN_ONE
(
ID INT PRIMARY KEY,
RQ VARCHAR(12),
CL INT,
BLS INT
)INSERT INTO T_CSDN_ONE
SELECT 1,'2013.05.01',200,1
UNION ALL
SELECT 2,'2013.05.02',200,0
UNION ALL
SELECT 3,'2013.05.03',200,3
UNION ALL
SELECT 4,'2013.05.06',200,2
UNION ALL
SELECT 5,'2013.05.07',200,0
UNION ALL
SELECT 6,'2013.05.08',200,0
UNION ALL
SELECT 7,'2013.05.10',200,0
UNION ALL
SELECT 8,'2013.05.11',200,1
ALTER FUNCTION dbo.F_MAXParentId(@ID INT)
RETURNS INT
AS
BEGIN
DECLARE @PARENTID INT
SET @PARENTID=-1;
SELECT @PARENTID=ISNULL(PARENTID,-1) FROM (
SELECT AA.ID,BB.ID AS PARENTID,AA.RQ,BB.RQ AS ENDRQ FROM T_CSDN_ONE AA LEFT JOIN T_CSDN_ONE BB
ON AA.ID<>BB.ID AND BB.BLS=0 AND AA.ID<BB.ID
WHERE AA.BLS=0 AND BB.ID-AA.ID=1
) A WHERE ID=@ID;
WHILE @PARENTID<>-1
BEGIN
RETURN DBO.F_MAXParentId(@PARENTID);
END
RETURN @ID;
END
SELECT B.PARENTID-A.ID+1,A.RQ,B.ENDRQ FROM T_CSDN_ONE A CROSS JOIN (
SELECT AA.ID,BB.ID AS PARENTID,AA.RQ,BB.RQ AS ENDRQ FROM T_CSDN_ONE AA LEFT JOIN T_CSDN_ONE BB
ON AA.ID<>BB.ID AND BB.BLS=0 AND AA.ID<BB.ID
WHERE AA.BLS=0 AND BB.ID-AA.ID=1
) B
WHERE A.BLS=0 AND DBO.F_MAXParentId(A.ID)=B.PARENTID
AND B.ID-A.ID=1