SQL知識點(+)

風靈使發表於2018-11-08
SQL

兩個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

在這裡插入圖片描述

用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

相關文章