sqlserver 序列



DROP TABLE #T
CREATE TABLE #T (K1 SMALLDATETIME, C1 INT)
INSERT #T (K1,C1) VALUES('19990901',28)
INSERT #T (K1,C1) VALUES('19991001',25)
INSERT #T (K1,C1) VALUES('19991101',13)
INSERT #T (K1,C1) VALUES('19991201',15)
INSERT #T (K1,C1) VALUES('20000101',35)
INSERT #T (K1,C1) VALUES('20000301',16)
INSERT #T (K1,C1) VALUES('20000201',38)

--SELECT * FROM #T;
--时间序列
SELECT T1.K1,T1.C1
  FROM #T T1
  JOIN #T T2
    ON (T1.C1>=T2.C1) AND (T1.K1=DATEADD(MM,1,T2.K1))
    OR (T1.C1<=T2.C1) AND (T1.K1=DATEADD(MM,-1,T2.K1))
GROUP BY T1.K1,T1.C1

SELECT *
  FROM #T T1
 WHERE EXISTS
 (SELECT 1 FROM #T T2
   WHERE (T1.C1>=T2.C1) AND (T1.K1=DATEADD(MM,1,T2.K1))
    OR (T1.C1<=T2.C1) AND (T1.K1=DATEADD(MM,-1,T2.K1))
  )

--SIGN 取符号, ABS取绝对值
SELECT * FROM #T;
SELECT STARTTIME = CAST(A.K1 AS CHAR(12)),
       ENDTIME = CAST(V.K1 AS CHAR(12)),
       STARTVAL = A.C1,
       ENDVAL = V.C1,
       CHANGE = SUBSTRING('- +', SIGN(A.C1 - V.C1) + 2, 1) +
       CAST(ABS(A.C1 - V.C1) AS VARCHAR)
  FROM (SELECT K1,
               C1,
               RANKING =
               (SELECT COUNT(DISTINCT K1) FROM #T U WHERE U.K1 <= L.K1)
          FROM #T L) V
  LEFT JOIN (SELECT K1,
                    C1,
                    RANKING =
                    (SELECT COUNT(DISTINCT K1) FROM #T U WHERE U.K1 <= L.K1)
               FROM #T L) A
    ON V.RANKING = A.RANKING + 1
 WHERE A.K1 IS NOT NULL
   AND V.RANKING%3=0

 (select *,ROW_NUMBER() OVER(ORDER BY K1) X from #T) A
 left join (select *,ROW_NUMBER() OVER(ORDER BY K1) X from #T) B
 on A.X=B.X-1
 ;
 
--OUTER APPLY/CROSS APPLY
SELECT A.*,C.K1,C.C1
  FROM #T A
 CROSS APPLY (SELECT TOP 1 B.K1,B.C1
       FROM #T B
      WHERE A.K1 < B.K1
      ORDER BY B.K1 ) C
 ORDER BY A.K1;
 
SELECT A.*,
(SELECT TOP 1 B.K1--,B.C1
       FROM #T B
      WHERE A.K1 < B.K1
      ORDER BY B.K1)
  FROM #T A
ORDER BY A.K1

SELECT A.*,
(SELECT TOP 2 B.K1--,B.C1
       FROM #T B
      WHERE A.K1 < B.K1
      ORDER BY B.K1)
  FROM #T A
ORDER BY A.K1

 
--区域
/*1.确定区域中的第一个位置，2.确定特定的区域*/
DROP TABLE #T
CREATE TABLE #T (K1 INT IDENTITY, C1 INT)
INSERT #T (C1) VALUES (20)
INSERT #T (C1) VALUES (30)
INSERT #T (C1) VALUES (0)
INSERT #T (C1) VALUES (0)
INSERT #T (C1) VALUES (0)
INSERT #T (C1) VALUES (41)
INSERT #T (C1) VALUES (0)
INSERT #T (C1) VALUES (32)
INSERT #T (C1) VALUES (42)

SELECT V.K1
  FROM #T V
  JOIN #T A
    ON (V.C1=0) AND (A.C1=0)
   AND ABS(V.K1-A.K1)=1
GROUP BY V.K1;

DROP TABLE #T
CREATE TABLE #T (K1 INT, C1 INT)
INSERT #T (K1,C1) VALUES (300,15)
INSERT #T (K1,C1) VALUES (340,25)
INSERT #T (K1,C1) VALUES (344,13)
INSERT #T (K1,C1) VALUES (345,14)
INSERT #T (K1,C1) VALUES (346,15)
INSERT #T (K1,C1) VALUES (347,38)
INSERT #T (K1,C1) VALUES (348,16)

SELECT V.K1,V.C1
  FROM #T V
  JOIN #T A
    ON (A.C1=V.C1+1 AND A.K1=V.K1+1)
    OR (A.C1=V.C1-1 AND A.K1=V.K1-1)
GROUP BY V.K1,V.C1;

DROP TABLE #T
CREATE TABLE #T (K1 INT IDENTITY, C1 INT)
INSERT #T (C1) VALUES (20)
INSERT #T (C1) VALUES (30)
INSERT #T (C1) VALUES (32)
INSERT #T (C1) VALUES (34)
INSERT #T (C1) VALUES (36)
INSERT #T (C1) VALUES (0)
INSERT #T (C1) VALUES (0)
INSERT #T (C1) VALUES (41)
INSERT #T (C1) VALUES (0)
INSERT #T (C1) VALUES (0)
INSERT #T (C1) VALUES (0)
INSERT #T (C1) VALUES (42)

--限制区域大小
SELECT V.K1
  FROM #T V
  JOIN #T A
    ON V.C1=0
GROUP BY V.K1
HAVING
  (ISNULL(MIN(CASE WHEN A.K1>V.K1 AND A.C1 !=0 THEN A.K1 ELSE NULL END)-1,
   MAX(CASE WHEN A.K1 >V.K1 THEN A.K1 ELSE V.K1 END)))       --区域内第一个0的位置
  -
  (ISNULL(MAX(CASE WHEN A.K1<V.K1 AND A.C1 !=0 THEN A.K1 ELSE NULL END)+1,
   MAX(CASE WHEN A.K1 <V.K1 THEN A.K1 ELSE V.K1 END)))+1      --区域内最后一个0的位置
   >=3
SELECT * FROM #T;

--区域边界
SELECT REGSTART=V.K1,
    REGEN=(ISNULL(MIN(CASE WHEN A.K1>V.K1 AND A.C1 !=0 THEN A.K1 ELSE NULL END)-1,
            MAX(CASE WHEN A.K1 >V.K1 THEN A.K1 ELSE V.K1 END)))
  FROM #T V
  JOIN #T A
    ON V.C1=0
GROUP BY V.K1
HAVING
  (ISNULL(MIN(CASE WHEN A.K1>V.K1 AND A.C1 !=0 THEN A.K1 ELSE NULL END)-1,
   MAX(CASE WHEN A.K1 >V.K1 THEN A.K1 ELSE V.K1 END))) > V.K1
 AND
  (ISNULL(MAX(CASE WHEN A.K1<V.K1 AND A.C1 !=0 THEN A.K1 ELSE NULL END)+1,
   MAX(CASE WHEN A.K1 <V.K1 THEN A.K1 ELSE V.K1 END))) = V.K1

SELECT * FROM #T;