SQLServer知识点(+)

短命女 2021-06-26 16:06 572阅读 0赞

## SQLServer中GO的注意点 ##

如果只是执行一条语句，有没有`GO`都一样

如果多条语句之间用`GO`分隔开就不一样了

每个被`GO`分隔的语句都是一个单独的事务，一个语句执行失败不会影响其它语句执行。

例如：

首先同时执行下边的语句

select * from sysobjects where id=a
    
    select getdate()

你会发现会报错，并且不会显示任何结果集

而你再执行

select * from sysobjects where id=a
    
    go
    
    select getdate()
    
    go

你会发现尽管同样会报错，但结果集中包含`select getdate()`的结果。

## 两个SQL语句的差值 ##

一段时间之内，最早时间的值与最晚时间的值得差

select value,max(time)-min(time) from D_live_data where field_id=156 group by value

select t1.value-t2.value
    from
    (select top1 value from D_live_data where  field_id=156 order by time) t1,
    (select top1 value from D_live_data where  field_id=156 order by time desc) t2

## Sql如何数据库求相邻两行的差值？ ##

现有表table

----------------------------------------------------------
    年	      月   	日	   气温
    2009	  1	    1	     6.8
    2009	  1	    2	     6.1
    2009	  1	    3	     5.6
    2009	  1	   4	     4.7
    2009	  1	   5	     4
    2009	  1	   6	     3
    2009	  1	   7	     4.5
    2009	  1	   8	     8.9
    2009	  1	   9	     11.2
    2009	  1	  10	     8.7
    ---------------------------------------------------------
    通过运算求相邻两列行中“气温”的差值，即得到以下表：
    ----------------------------------------------------------
    年	      月   	日	   气温     与昨日温差
    2009	  1	    1	     6.8  
    2009	  1	    2	     6.1        -0.7
    2009	  1	    3	     5.6        -0.5
    2009	  1	   4	     4.7        -0.9
    2009	  1	   5	     4            -0.7
    2009	  1	   6	     3            -1
    2009	  1	   7	     4.5        1.5
    2009	  1	   8	     8.9         4.4
    2009	  1	   9	     11.2       2.3
    2009	  1	  10	     8.7        -2.5
    ---------------------------------------------------------

WITH test (年,月,日,气温) AS (
        SELECT 2009,1,1,6.8 UNION ALL
        SELECT 2009,1,2,6.1 UNION ALL
        SELECT 2009,1,3,5.6 UNION ALL
        SELECT 2009,1,4,4.7 UNION ALL
        SELECT 2009,1,5,4 UNION ALL
        SELECT 2009,1,6,3 UNION ALL
        SELECT 2009,1,7,4.5 UNION ALL
        SELECT 2009,1,8,8.9 UNION ALL
        SELECT 2009,1,9,11.2 UNION ALL
        SELECT 2009,1,10,8.7
    )
    SELECT t1.*,
           t1.气温 - t0.气温 与昨日温差
      FROM test t1
    LEFT JOIN test t0
           ON DATEDIFF(day, 
                       DateAdd(day,t0.日-1,DateAdd(month,t0.月-1,DateAdd(year,t0.年-1900,'1900-01-01'))),
                       DateAdd(day,t1.日-1,DateAdd(month,t1.月-1,DateAdd(year,t1.年-1900,'1900-01-01')))
                      ) = 1

WITH test (年,月,日,气温) AS (
        SELECT 2009,1,1,6.8 UNION ALL
        SELECT 2009,1,2,6.1 UNION ALL
        SELECT 2009,1,3,5.6 UNION ALL
        SELECT 2009,1,4,4.7 UNION ALL
        SELECT 2009,1,5,4 UNION ALL
        SELECT 2009,1,6,3 UNION ALL
        SELECT 2009,1,7,4.5 UNION ALL
        SELECT 2009,1,8,8.9 UNION ALL
        SELECT 2009,1,9,11.2 UNION ALL
        SELECT 2009,1,10,8.7
    )
    select  a.*,b.气温-a.气温 as 与昨日温差
    from
       (select *,row_number()over(order by getdate()) as id from test) as a
    inner join
       (select *,row_number()over(order by getdate()) as id from test) as b
    on
       a.id=b.id-1

![在这里插入图片描述][20181107104756874.png]

## 用SQL语句直接得出两个记录之间某字段的差值。 ##

现有如下记录：

序号 表号 购电次数 剩余电量 总购电量 总用电量 
      1  22      1        33      2345    2312 
      2  22      2        23      3453    3430 
      3  22      3        243     3998    3755  
      4  22      4        36      4896    4850 
      4  22      5        76      5476    5400

通过SQL语句 根据提供的购电次数信息 直接计算出某两次购电之间的用电量，例如第4次购电和第3次购电之间 用电量为 4850-3755 =1095度。

方法一

WITH test (序号,表号,购电次数,剩余电量,总购电量,总用电量) AS (
        SELECT 1,22,1,33,2345,2312 UNION ALL
        SELECT 2,22,2,23,3453,3430 UNION ALL
        SELECT 3,22,3,243,3998,3755 UNION ALL
        SELECT 4,22,4,36,4896,4850 UNION ALL
        SELECT 4,22,5,76,5476,5400
    ) 
      
    SELECT Sum([总用电量] * ( CASE [购电次数] 
                                    WHEN 3 THEN -1 
                                    WHEN 4 THEN 1 
                                  END )) 
    FROM   test 
    WHERE  [购电次数] = 3 OR [购电次数] = 4

方法二

WITH test (序号,表号,购电次数,剩余电量,总购电量,总用电量) AS (
        SELECT 1,22,1,33,2345,2312 UNION ALL
        SELECT 2,22,2,23,3453,3430 UNION ALL
        SELECT 3,22,3,243,3998,3755 UNION ALL
        SELECT 4,22,4,36,4896,4850 UNION ALL
        SELECT 4,22,5,76,5476,5400
    ) 
    select B.总用电量-A.总用电量 from 
    (select * from test where 购电次数=3) A,
    (select * from test where 购电次数=4) B
    where A.表号=B.表号

方法三

WITH test (序号,表号,购电次数,剩余电量,总购电量,总用电量) AS (
        SELECT 1,22,1,33,2345,2312 UNION ALL
        SELECT 2,22,2,23,3453,3430 UNION ALL
        SELECT 3,22,3,243,3998,3755 UNION ALL
        SELECT 4,22,4,36,4896,4850 UNION ALL
        SELECT 4,22,5,76,5476,5400
    ) 
    
    --全部数据
    --SELECT a.*,(a.总用电量 - b.总用电量 ) as 用电量 
    --from test a left join test b
    --on a.购电次数=b.购电次数+1 and a.表号=b.表号
    
    --第4次购电:
    SELECT a.*,(a.总用电量 - b.总用电量 ) as 用电量 
    from test a left join test b
    on a.购电次数=b.购电次数+1 and a.表号=b.表号
    where  a.购电次数=4 and a.表号=22

[20181107104756874.png]: /images/20210626/fb0911223aac4d7290b92a9bcca8082f.png