SQL T型结构的累加方式 over

select shipper.trans_no,
       shipper_line.part_id as customer_id,
       shipper_line.quantity,
       shipper.trans_date,
       shipper_line.unit_price,
       shipper_line.line_no,
       shipper_line.all_amt,
       sum(shipper_line.all_amt) over (partition by shipper_line.part_id order by shipper.trans_date
                                       rows between unbounded preceding and current row) as shipper_allamt        
from  
       shipper join shipper_line on (shipper.trans_no=shipper_line.trans_no)
where  shipper.trans_date between '2014-07-01' and '2014-07-30'

 --

ASA   写法：财务对帐单据格式

 

 

select 客户代号,客户简称,标志,sum(sum(购货金额)-sum(收款金额)) over (partition by 客户代号,标志 order by 标志 --,单据日期 
  rows between unbounded preceding and current row) as T型余额 from (

select customer.customer_id as 客户代号,
       customer.short_name as 客户简称,
       case when isnull(act_recv_line.user_2,'')  like '%送%' then '专用款项' else '普通类' end as 标志,
       left(act_receivable.trans_no,2) as 排列方式,
       right(act_receivable.trans_no,12) as 排列标志,
       act_receivable.trans_no as 单号,
       act_receivable.trans_date as 单据日期,
       '应收单' as 应收单,
       isnull(act_receivable.remark,'')    as 摘要,
       ''       as 收款方式,            
       sum(isnull(ar_amt,0)) as 购货金额,
        0   as 收款金额,act_recv_line.user_2 as 发货标注
from act_receivable left join act_recv_line on act_receivable.trans_no=act_recv_line.trans_no left join customer on act_receivable.customer_id=customer.customer_id 
where  act_receivable.approved='Y'
group by 客户代号,客户简称,标志,排列方式,排列标志,单号,单据日期,应收单,收款方式,摘要,收款金额,发货标注      --应收单据数据
union all
SELECT customer.customer_id                  AS 客户代号,
       customer.short_name                   AS 客户名称,
      case when Isnull(cash.trans_type, '')='' then '普通类' else '专用款项'  end  as 单据类型 ,
       LEFT (cash.trans_no, 2)               AS 排列方式,
       RIGHT(cash.trans_no, 12)              AS 排列标志,
       cash.trans_no                         AS 单号,
       cash.trans_date                       AS 单据日期,
       '收款单'                                 AS 收款单,
       Isnull(cash.remark, '')               AS 摘要,
       Isnull(accept_save_type.describe, '') AS 收款方式,
       0,
       Isnull(cash.sys_act_amt, 0)          AS 汇入金额,
      case when Isnull(cash.trans_type, '')='' then '普通类' else '专用款项'  end as 收款标注
FROM   cash
        LEFT JOIN customer
         ON customer.customer_id = cash.customer_id
       LEFT JOIN accept_save_type
         ON accept_save_type.id = cash.accept_type 
where cash.approved='Y' and cash.cash_type<>'R'
--收款单据数据
)a  
--where 客户代号='SY-GF' 
group by 客户代号,客户简称,标志  order by 客户代号,标志 

 

1133384xc6x69h49du9f9k.jpg

 

________________________________________________________________________________

自己按上面做出来的报表

select *,sum(购货金额-收款金额) over (partition by 客户代号,标志 order by 标志,单据日期   rows between unbounded preceding and current row) as T型余额,
dense_rank() over( order by a.客户代号) as rank,sum(购货金额-收款金额) over (partition by 客户代号,标志) as 分组金额,sum(购货金额-收款金额) over (partition by 客户代号) as 总计欠款金额,row_number() over (partition by 客户代号 order by 客户代号,标志,排列标志) as 行数,(select chn_name from entity) as 公司名称,(select  telephone from entity) as 联系电话
 from (
 
SELECT Isnull(rm.客户代号, cm.c客户代号) AS 客户代号,
       Isnull(rm.客户简称, cm.c客户简称) AS 客户简称,
       Isnull(rm.标志, cm.c标志)     AS 标志,
       '0'                       AS 排列方式,
       '0'                       AS 排列标志,
       null                       AS 单号,
       null                       as 源单据号,
       NULL                      AS 单据日期,
       '期初余额'                    AS 单据类型,
       ''                        AS 摘要,
       ''                        AS 收款方式,
       Isnull(rm.购货金额, 0)        AS 购货金额,
       Isnull(cm.c收款金额, 0)       AS 收款金额,
       ''                        AS 发货标记
FROM   (SELECT customer.customer_id                 AS 客户代号,
               customer.short_name                  AS 客户简称,
               CASE
                 WHEN Isnull(act_recv_line.user_2, '') LIKE '%送%' THEN '专用款项'
                 ELSE '普通类'
               END                                  AS 标志,
               '0'                                  AS 排列方式,
               '0'                                  AS 排列标志,
               null                                  单号,
               null                                  源单据号,
               NULL                                 AS 单据日期,
               '期初余额'                               AS 应收单,
               ''                                   AS 摘要,
               ''                                   AS 收款方式,
               Sum(Isnull(act_recv_line.ar_amt, 0)) AS 购货金额,
               0                                    AS 收款金额,
               ''                                   AS 发货标注
        FROM   act_receivable
               LEFT JOIN act_recv_line
                 ON act_receivable.trans_no = act_recv_line.trans_no
               LEFT JOIN customer
                 ON customer.customer_id = act_receivable.customer_id
        WHERE  act_receivable.trans_date < :开始日期
               AND ACT_RECEIVABLE.approved = 'Y'
        GROUP  BY 客户代号,
                  客户简称,
                  标志,
                  排列方式,
                  排列标志,
                  单号,
                  单据日期,
                  应收单,
                  摘要,
                  收款金额,
                  发货标注 --小于指定日期的收款单据
       )RM
       FULL JOIN (SELECT customer.customer_id  AS C客户代号,
                         customer.short_name   AS C客户简称,
                         Sum(cash.sys_act_amt) AS C收款金额,
                         CASE
                           WHEN Isnull(cash.trans_type, '') = '' THEN '普通类'
                           ELSE '专用款项'
                         END                   AS C标志
                  FROM   cash,
                         customer
                  WHERE  cash.customer_id = customer.customer_id
                         AND cash.trans_date < :开始日期
                         AND cash.cash_type <> 'R'
                         AND cash.approved = 'Y'
                  GROUP  BY c客户代号,
                            c客户简称,
                            c标志)CM
         ON rm.客户代号 = cm.c客户代号
            AND rm.标志 = cm.c标志
--取出录入的期初数据原理是丛应收单据表身汇总应收款金额-收款单据表头的已收款金额（并且不包含应收冲预收单据)
 

union all
SELECT customer.customer_id               AS 客户代号,
       customer.short_name                AS 客户简称,
       CASE
         WHEN Isnull(act_recv_line.user_2, '') LIKE '%送%' THEN '专用款项'
         ELSE '普通类'
       END                                AS 标志,
       LEFT(act_receivable.trans_no, 2)   AS 排列方式,
       RIGHT(act_receivable.trans_no, 12) AS 排列标志,
       act_receivable.trans_no            AS 单号,
       act_recv_line.shipper_no            as 源单据号,
       act_receivable.trans_date          AS 单据日期,
       '应收单'                              AS 应收单,
       Isnull(act_receivable.remark, '')  AS 摘要,
       ''                                 AS 收款方式,
       Sum(Isnull(ar_amt, 0))             AS 购货金额,
       0                                  AS 收款金额,
       act_recv_line.user_2               AS 发货标注
FROM   act_receivable
       LEFT JOIN act_recv_line
         ON act_receivable.trans_no = act_recv_line.trans_no
       LEFT JOIN customer
         ON act_receivable.customer_id = customer.customer_id
WHERE  act_receivable.approved = 'Y'
       AND act_receivable.trans_date between  :开始日期 and :结束日期
GROUP  BY 客户代号,
          客户简称,
          标志,
          排列方式,
          排列标志,
          单号,
          源单据号,
          单据日期,
          应收单,
          收款方式,
          摘要,
          收款金额,
          发货标注
--应收单据数据明细

union all
SELECT customer.customer_id                  AS 客户代号,
       customer.short_name                   AS 客户名称,
       CASE
         WHEN Isnull(cash.trans_type, '') = '' THEN '普通类'
         ELSE '专用款项'
       END                                   AS 单据类型,
       LEFT (cash.trans_no, 2)               AS 排列方式,
       RIGHT(cash.trans_no, 12)              AS 排列标志,
       cash.trans_no                         AS 单号,
       ''                                    as 源单据号,
       cash.trans_date                       AS 单据日期,
       '收款单'                                 AS 收款单,
       Isnull(cash.remark, '')               AS 摘要,
       Isnull(accept_save_type.describe, '') AS 收款方式,
       0,
       Isnull(cash.sys_act_amt, 0)           AS 汇入金额,
       CASE
         WHEN Isnull(cash.trans_type, '') = '' THEN '普通类'
         ELSE '专用款项'
       END                                   AS 收款标注
FROM   cash
       LEFT JOIN customer
         ON customer.customer_id = cash.customer_id
       LEFT JOIN accept_save_type
         ON accept_save_type.id = cash.accept_type
WHERE  cash.approved = 'Y'
       AND cash.cash_type <> 'R'
       AND cash.trans_date between  :开始日期 and :结束日期
--and 客户名称 like '%郭芳%'
--收款单据数据

)a   -- where 客户代号 ='GN0149'
  order by rank,标志 desc,排列标志

 

 ——————————————————————————————————————————————————————

MSSQL 子查询写法

有多组数据,分别是收入,支出,余额,它们的关系是:
本期余额=上次余额+收入-支出

 

/*
测试数据：
Create Table tbl([日期] smalldatetime,[收入] int ,[支出] int) 

Insert Into tbl
SELECT '2004-02-11', 60, 45
union SELECT '2004-10-01',60, 45
union SELECT '2004-10-02',40, 50
union SELECT '2004-10-15',50, 40
union SELECT '2004-10-16',10, null
union SELECT '2004-10-19',10, 0
union SELECT '2004-10-30',0, 10
*/

--select *,(select sum(收入-支出) from 表 where 你的表的主键<=tem.你的表的主键) 本次余额 from 表 tem 

select t.日期,t.收入,t.支出,(select sum(ISNULL(收入, 0))-sum(ISNULL(支出, 0)) from tbl where 日期 <= t.日期) as 本期余额 
from tbl t order by 日期 desc

例二：http://www.cnblogs.com/zhaoguan_wang/p/4634759.html

            

       SQL集合运算参考及案例(二)：树形节点数量逐级累计汇总

WITH tmp AS (
    SELECT t.Id tm, * FROM tMaterial t
    UNION ALL
    SELECT t2.tm tm, t1.* FROM tMaterial t1 JOIN tmp t2 ON t1.ParentId = t2.Id
)
SELECT  tm,  sum(Qty) 
FROM    tmp 
GROUP BY tm

例三  需要是SQL2010或以上支持的窗口函数

    select qian.*,sum(欠款金额) over(order by 组织ID,会计日期) from qian

OVER 函数说明