兩條經典SQL語句(2)_Mssql數據庫教程

      編輯Tag賺U幣
      教程Tag:暫無Tag,歡迎添加,賺取U幣!

      推薦:淺談SQL server2000中char和varchar字符型數據類型的區別
      char和varchar的長度都在1到8000之間,它們的區別在于char是定長字符數據,而varchar是變長字符數據。所謂定長就是長度是固定的,當輸入的數據長度沒有達到指定的長度時將自動以英文空格在其后面填充,使長度達到相應的長度;而變長字符數據則不會以空格填充


        2004-03-16 00:00:00 NULL NULL 30
        2004-03-17 00:00:00 NULL NULL 30
        2004-03-18 00:00:00 NULL NULL 30
        2004-03-19 00:00:00 NULL NULL 30
        2004-03-20 00:00:00 NULL NULL 30
        2004-03-21 00:00:00 NULL NULL 30
        2004-03-22 00:00:00 NULL NULL 30
        2004-03-23 00:00:00 NULL NULL 30
        2004-03-24 00:00:00 NULL NULL 30
        2004-03-25 00:00:00 NULL NULL 30
        2004-03-26 00:00:00 NULL NULL 30
        2004-03-27 00:00:00 NULL NULL 30
        2004-03-28 00:00:00 NULL NULL 30
        2004-03-29 00:00:00 NULL NULL 30
        2004-03-30 00:00:00 NULL NULL 30
        2004-03-31 00:00:00 NULL NULL 30

       

        答案:


       
        SELECT Y.[日期], tbl.[收入], tbl.[支出], (
        SELECT SUM(ISNULL(tbl.[收入], 0)-ISNULL(tbl.[支出], 0)) FROM tbl WHERE [日期]<=Y.[日期]) AS [余額]
        FROM tbl RIGHT JOIN (
        SELECT DATEADD(dd, N.i, DATEADD(dd, 1-DAY(m.MinDay), m.MinDay)) AS [日期]
        FROM (
        SELECT 0 AS i




       
        UNION ALL SELECT 1
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
        UNION ALL SELECT 6
        UNION ALL SELECT 7
        UNION ALL SELECT 8
        UNION ALL SELECT 9
        UNION ALL SELECT 10
        UNION ALL SELECT 11
        UNION ALL SELECT 12
        UNION ALL SELECT 13
        UNION ALL SELECT 14
        UNION ALL SELECT 15
        UNION ALL SELECT 16
        UNION ALL SELECT 17
        UNION ALL SELECT 18
        UNION ALL SELECT 19
        UNION ALL SELECT 20
        UNION ALL SELECT 21
        UNION ALL SELECT 22
        UNION ALL SELECT 23
        UNION ALL SELECT 24
        UNION ALL SELECT 25
        UNION ALL SELECT 26
        UNION ALL SELECT 27
        UNION ALL SELECT 28
        UNION ALL SELECT 29
        UNION ALL SELECT 30
        UNION ALL SELECT 31
        ) N,
        (
        SELECT MIN(日期) AS MinDay
        FROM tbl
        GROUP BY DATEDIFF(month, 0, 日期)
        ) M
        WHERE DATEDIFF(mm, DATEADD(dd, N.i, DATEADD(dd, 1-DAY(m.MinDay), m.MinDay)), M.MinDay)=0) AS Y
        ON tbl.[日期]=Y.日期

      分享:淺析SQL server存儲過程
      一直用ASP+ACCESS來編寫網頁和公司的內部應用系統,內部應用系統也就是大家說的OA吧,這個我也不知道,公司又叫它ERP,反正不管什么,它是用來幫助公司處理日常工作的,簡化勞動的.從來沒用過存儲過程,所有的添加,刪除,修改都是在ASP里面寫程序執行,代碼很多.數據

      共2頁上一頁12下一頁
      來源:模板無憂//所屬分類:Mssql數據庫教程/更新時間:2010-04-13
      相關Mssql數據庫教程