SQL T型结构的累加方式 over
来源:互联网 发布:flash软件的利弊 编辑:程序博客网 时间:2024/06/05 12:42
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 allSELECT 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,排列标志
- SQL T型结构的累加方式 over
- 累加sql的书写
- t-sql row_number()over(partition by 使用说明
- SQL OVER的运用。
- sql Over的用法
- SQL累加
- sql语句分页多种方式ROW_NUMBER()OVER
- sql语句分页多种方式ROW_NUMBER()OVER
- SQL Server 2005对T-SQL的增强之在聚合函数的后面使用over关键字
- over语句的使用--sql
- SQL: OVER子句的应用
- SQL中over的用法
- T-SQL语法结构应用
- 取表结构 t-sql
- T-SQL获取表结构
- sql 树形累加函数的实现
- 实现累加的经典sql方法
- SQL使用递归实现数据的累加
- CorePlot学习八---如何更新数据
- windows下手动配置ipv6地址
- Material Design
- POJ3669 Meteor Shower 广度优先搜索
- 联想ThinkPad 官网驱动下载 对应你的电脑的型号
- SQL T型结构的累加方式 over
- 1020. 月饼 (25)
- Windows Phone Style样式的四种使用
- 《老男孩》上映 “娱乐宝”投资电影进入回收期
- 文件数据如何实现定期自动备份
- Qt一步一步实现插件调用(附源码)、Qt一步一步实现插件通信(附源码)
- STARTUP.A51详解
- android缓存数据到本地放在哪儿最好?
- ACM-快速排序