bestlong 怕失憶論壇

註 冊

找回密碼

 

 

搜索
bestlong 怕失憶論壇 › 論壇 › Database - 資料庫 › MS SQL Server › 計算時間間隔
查看: 6877|回復: 1
 go

計算時間間隔 [複製鏈接]

bestlong

Rank: 9Rank: 9Rank: 9
1#
發表於 2006-8-8 12:19 |只看該作者 |倒序瀏覽 |打印
登入時間紀錄
  1. ID         LoginTime
  2. -----------------------------------
  3. a1006      2004-07-29 12:24:15.000
  4. a1006      2004-07-29 13:53:43.000
  5. a1006      2004-07-29 22:42:15.000
  6. a1006      2004-07-30 10:23:12.000
  7. a1006      2004-07-30 19:03:23.000
  8. a1006      2004-07-30 19:42:56.000
  9. a1006      2004-07-30 20:22:51.000
  10. a1006      2004-08-02 20:31:36.000
  11. freedom545 2004-07-30 02:30:29.000
  12. freedom545 2004-07-31 01:06:16.000
  13. freedom545 2004-08-01 00:29:41.000
  14. freedom545 2004-08-01 01:23:10.000
  15. freedom545 2004-08-03 14:50:45.000
  16. freedom545 2004-08-03 14:54:07.000
複製代碼

理想中的萃取結果為：
  1. ID           InterLoginTime
  2. ----------------------------
  3. a1006        1 -------------2004-07-29 (13) - 2004-07-29 (12)
  4. a1006        9 -------------2004-07-29 (22) - 2004-07-29 (13)
  5. a1006       12 -------------2004-07-30 (10) - 2004-07-29 (22)
  6. a1006        9 -------------2004-07-30 (19) - 2004-07-30 (10)
  7. a1006        1 -------------2004-07-30 (20) - 2004-07-30 (19)
  8. a1006       72 -------------2004-08-02 (20) - 2004-07-30 (20)
  9. freedom545  23 -------------2004-07-31 (01) - 2004-07-30 (02)
  10. freedom545  23 -------------2004-08-01 (00) - 2004-07-31 (01)
  11. freedom545   1 -------------2004-08-01 (01) - 2004-08-01 (00)
  12. freedom545  61 -------------2004-08-03 (14) - 2004-08-01 (01)
複製代碼


準備測試環境
  1. CREATE TABLE DemoA
  2. (
  3.          ID        VARCHAR(20)
  4.         ,Login        DATETIME
  5. )
  6. GO

  8. INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-29 12:24:15.000')
  9. INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-29 13:53:43.000')
  10. INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-29 22:42:15.000')
  11. INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-30 10:23:12.000')
  12. INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-30 19:03:23.000')
  13. INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-30 19:42:56.000')
  14. INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-30 20:22:51.000')
  15. INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-08-02 20:31:36.000')

  17. INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-07-30 02:30:29.000')
  18. INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-07-31 01:06:16.000')
  19. INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-08-01 00:29:41.000')
  20. INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-08-01 01:23:10.000')
  21. INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-08-03 14:50:45.000')
  22. INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-08-03 14:54:07.000')
  23. GO
複製代碼


萃取資料
  1. select distinct ID
  2. , LT=convert(datetime,convert(char(13),login,120) + ':00:00',110)
  3. into #T1
  4. from demoa
  5. select * from
  6. (select A.ID, A.LT
  7. ,Hr=(select top 1 datediff(hour,A.LT,B.LT) from #T1 B where A.ID=B.ID and B.LT > A.LT order by B.LT)
  8. from #T1 A) as VT1
  9. where Hr is not null
  10. order by ID, LT

  12. drop TABLE #T1
複製代碼


得到結果
  1. ID            LT                         Hr
  2. ------------- -------------------------- -----
  3. a1006         2004-07-29 12:00:00.000    1
  4. a1006         2004-07-29 13:00:00.000    9
  5. a1006         2004-07-29 22:00:00.000    12
  6. a1006         2004-07-30 10:00:00.000    9
  7. a1006         2004-07-30 19:00:00.000    1
  8. a1006         2004-07-30 20:00:00.000    72
  9. freedom545    2004-07-30 02:00:00.000    23
  10. freedom545    2004-07-31 01:00:00.000    23
  11. freedom545    2004-08-01 00:00:00.000    1
  12. freedom545    2004-08-01 01:00:00.000    61
複製代碼
收藏0 分享0
我是雪龍
http://blog.bestlong.idv.tw
http://www.bestlong.idv.tw
回復 引用

舉報 返回頂部

bestlong

Rank: 9Rank: 9Rank: 9
2#
發表於 2006-8-10 09:37 |只看該作者
若是希望將 24 小時區分為四大時段，並將查詢結果的時間校正為區間的開始時間

凌晨：00:00:00 ~ 05:59:59
上午：06:00:00 ~ 11:59:59
下午：12:00:00 ~ 17:59:59
夜晚：18:00:00 ~ 23:59:59

  1. SELECT ID,login
  2. , CASE
  3.     WHEN DATEPART(hour, LOGIN)>=18 THEN CONVERT(DATETIME, CONVERT(CHAR(10), LOGIN, 120) + ' 18:00:00.000')
  4.     WHEN DATEPART(hour, LOGIN)>=12 THEN CONVERT(DATETIME, CONVERT(CHAR(10), LOGIN, 120) + ' 12:00:00.000')
  5.     WHEN DATEPART(hour, LOGIN)>=6  THEN CONVERT(DATETIME, CONVERT(CHAR(10), LOGIN, 120) + ' 06:00:00.000')
  6.     ELSE CONVERT(DATETIME, CONVERT(CHAR(10), LOGIN, 120) + ' 00:00:00.000')
  7.   END as LT
  8. from demoa
複製代碼

得到結果
  1. ID           login                     LT
  2. ------------ ------------------------- -----------------------
  3. a1006        2004-07-29 12:24:15.000   2004-07-29 12:00:00.000
  4. a1006        2004-07-29 13:53:43.000   2004-07-29 12:00:00.000
  5. a1006        2004-07-29 22:42:15.000   2004-07-29 18:00:00.000
  6. a1006        2004-07-30 10:23:12.000   2004-07-30 06:00:00.000
  7. a1006        2004-07-30 19:03:23.000   2004-07-30 18:00:00.000
  8. a1006        2004-07-30 19:42:56.000   2004-07-30 18:00:00.000
  9. a1006        2004-07-30 20:22:51.000   2004-07-30 18:00:00.000
  10. a1006        2004-08-02 20:31:36.000   2004-08-02 18:00:00.000
  11. freedom545   2004-07-30 02:30:29.000   2004-07-30 00:00:00.000
  12. freedom545   2004-07-31 01:06:16.000   2004-07-31 00:00:00.000
  13. freedom545   2004-08-01 00:29:41.000   2004-08-01 00:00:00.000
  14. freedom545   2004-08-01 01:23:10.000   2004-08-01 00:00:00.000
  15. freedom545   2004-08-03 14:50:45.000   2004-08-03 12:00:00.000
  16. freedom545   2004-08-03 14:54:07.000   2004-08-03 12:00:00.000
複製代碼
我是雪龍
http://blog.bestlong.idv.tw
http://www.bestlong.idv.tw
點評回復 引用

舉報 返回頂部

‹ 上一主題|下一主題 ›
返回列表 發新帖 回復

Archiver|怕失憶論壇

GMT+8, 2025-5-2 05:30 , Processed in 0.015887 second(s), 11 queries .

Powered by Discuz! X1.5

© 2001-2010 Comsenz Inc.