2.1w+
社区成员
如何动态的汇总日期字段当天以前的数量汇总
fnumber fdate finqty foutqty
1.2.00239 NULL 0.0000000000 0.0000000000
1.2.00239 2015-05-01 00:00:00.000 0.0000000000 42.0000000000
1.2.00239 2015-05-04 00:00:00.000 400.0000000000 0.0000000000
1.2.00239 2015-05-07 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-11 00:00:00.000 0.0000000000 1.8000000000
1.2.00239 2015-05-12 00:00:00.000 0.0000000000 0.9000000000
1.2.00239 2015-05-13 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-15 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-20 00:00:00.000 0.0000000000 31.5000000000
1.2.00239 2015-05-26 00:00:00.000 0.0000000000 22.8000000000
1.2.00239 2015-05-28 00:00:00.000 0.0000000000 3.0000000000
1.2.00239 2015-05-28 00:00:00.000 0.0000000000 0.1300000000
1.2.00239 2015-06-01 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-01 00:00:00.000 400.0000000000 0.0000000000
1.2.00239 2015-06-01 00:00:00.000 -400.0000000000 0.0000000000
1.2.00239 2015-06-02 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-08 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-12 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-15 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-18 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-24 00:00:00.000 0.0000000000 24.0000000000
1.2.00239 2015-06-26 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-30 00:00:00.000 0.3500000000 0.0000000000
1.2.00239B NULL 0.0000000000 0.000000000
如何动态根据fdate值 ，小于fdate 值汇总finqty,foutqty?
1.2.00239 NULL 0.0000000000 0.0000000000
1.2.00239 2015-05-01 00:00:00.000 0.0000000000 42.0000000000
1.2.00239 2015-05-04 00:00:00.000 400.0000000000 0.0000000000
1.2.00239 2015-05-07 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-11 00:00:00.000 0.0000000000 1.8000000000
1.2.00239 2015-05-12 00:00:00.000 0.0000000000 0.9000000000
1.2.00239 2015-05-13 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-15 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-20 00:00:00.000 0.0000000000 31.5000000000
1.2.00239 2015-05-26 00:00:00.000 0.0000000000 22.8000000000
1.2.00239 2015-05-28 00:00:00.000 0.0000000000 3.0000000000
1.2.00239 2015-05-28 00:00:00.000 0.0000000000 0.1300000000
1.2.00239 2015-06-01 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-01 00:00:00.000 400.0000000000 0.0000000000
1.2.00239 2015-06-01 00:00:00.000 -400.0000000000 0.0000000000
1.2.00239 2015-06-02 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-08 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-12 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-15 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-18 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-24 00:00:00.000 0.0000000000 24.0000000000
1.2.00239 2015-06-26 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-06-30 00:00:00.000 0.3500000000 0.0000000000
1.2.00239B NULL 0.0000000000 0.000000000
如何动态根据fdate值 ，小于fdate 值汇总finqty,foutqty?
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
songlinshu 2015-12-07
例：1.2.00239 NULL 0.0000000000 0.0000000000
1.2.00239 2015-05-01 00:00:00.000 0.0000000000 42.0000000000
1.2.00239 2015-05-04 00:00:00.000 400.0000000000 0.0000000000
1.2.00239 2015-05-07 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-11 00:00:00.000 0.0000000000 1.8000000000
1.2.00239 2015-05-12 00:00:00.000 0.0000000000 0.9000000000
1.2.00239 2015-05-13 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-15 00:00:00.000 0.0000000000 21.0000000000
1.2.00239 2015-05-20 00:00:00.000 0.0000000000 31.5000000000
当日期=2015-05-15 时汇总包含2015-05-15以前的finqty,foutqty,
当日期=2015-05-20 时汇总包含2015-05-20以前的finqty,foutqty,
中国风 2015-12-07
SELECT a.*
,(SELECT SUM(finqty) FROM TabName WHERE fnumber=a.fnumber AND ISNULL(fdate,'1900-01-01')<ISNULL(a.fdate,'1900-01-01')) AS finqty
,(SELECT SUM(foutqty) FROM TabName WHERE fnumber=a.fnumber AND ISNULL(fdate,'1900-01-01')<ISNULL(a.fdate,'1900-01-01')) AS foutqty
FROM TabName AS aspiritofdragon 2015-12-07
其实你描述的一点都不清楚，但我试了下，看看是不是你要的效果，如果不是，那么请你看看发帖须知重新描述下你的需求。
with t(fnumber,fdate,finqty,foutqty) as (
select '1.2.00239',NULL,'0.0000000000','0.0000000000' union all
select '1.2.00239','2015-05-01 00:00:00.000','0.0000000000','42.0000000000' union all
select '1.2.00239','2015-05-04 00:00:00.000','400.0000000000','0.0000000000' union all
select '1.2.00239','2015-05-07 00:00:00.000','0.0000000000','21.0000000000' union all
select '1.2.00239','2015-05-11 00:00:00.000','0.0000000000','1.8000000000' union all
select '1.2.00239','2015-05-12 00:00:00.000','0.0000000000','0.9000000000' union all
select '1.2.00239','2015-05-13 00:00:00.000','0.0000000000','21.0000000000' union all
select '1.2.00239','2015-05-15 00:00:00.000','0.0000000000','21.0000000000' union all
select '1.2.00239','2015-05-20 00:00:00.000','0.0000000000','31.5000000000' union all
select '1.2.00239','2015-05-26 00:00:00.000','0.0000000000','22.8000000000' union all
select '1.2.00239','2015-05-28 00:00:00.000','0.0000000000','3.0000000000' union all
select '1.2.00239','2015-05-28 00:00:00.000','0.0000000000','0.1300000000' union all
select '1.2.00239','2015-06-01 00:00:00.000','0.0000000000','21.0000000000' union all
select '1.2.00239','2015-06-01 00:00:00.000','400.0000000000','0.0000000000' union all
select '1.2.00239','2015-06-01 00:00:00.000','-400.0000000000','0.0000000000' union all
select '1.2.00239','2015-06-02 00:00:00.000','0.0000000000','21.0000000000' union all
select '1.2.00239','2015-06-08 00:00:00.000','0.0000000000','21.0000000000' union all
select '1.2.00239','2015-06-12 00:00:00.000','0.0000000000','21.0000000000' union all
select '1.2.00239','2015-06-15 00:00:00.000','0.0000000000','21.0000000000' union all
select '1.2.00239','2015-06-18 00:00:00.000','0.0000000000','21.0000000000' union all
select '1.2.00239','2015-06-24 00:00:00.000','0.0000000000','24.0000000000' union all
select '1.2.00239','2015-06-26 00:00:00.000','0.0000000000','21.0000000000' union all
select '1.2.00239','2015-06-30 00:00:00.000','0.3500000000','0.0000000000' union all
select '1.2.00239B',NULL,'0.0000000000','0.000000000'
)
,tt as (
select t.fdate,SUM(CAST(finqty as money))finqty,SUM(CAST(foutqty as money))foutqty
from t
group by t.fdate
)
select t1.fdate,isnull(t2.finqty,0)finqty,isnull(t2.foutqty,0)foutqty
from tt t1
outer apply(select SUM(finqty)finqty,SUM(foutqty)foutqty from tt t2 where t2.fdate<t1.fdate) t2
order by t1.fdate
fdate finqty foutqty
NULL 0.00 0.00
2015-05-01 00:00:00.000 0.00 0.00
2015-05-04 00:00:00.000 0.00 42.00
2015-05-07 00:00:00.000 400.00 42.00
2015-05-11 00:00:00.000 400.00 63.00
2015-05-12 00:00:00.000 400.00 64.80
2015-05-13 00:00:00.000 400.00 65.70
2015-05-15 00:00:00.000 400.00 86.70
2015-05-20 00:00:00.000 400.00 107.70
2015-05-26 00:00:00.000 400.00 139.20
2015-05-28 00:00:00.000 400.00 162.00
2015-06-01 00:00:00.000 400.00 165.13
2015-06-02 00:00:00.000 400.00 186.13
2015-06-08 00:00:00.000 400.00 207.13
2015-06-12 00:00:00.000 400.00 228.13
2015-06-15 00:00:00.000 400.00 249.13
2015-06-18 00:00:00.000 400.00 270.13
2015-06-24 00:00:00.000 400.00 291.13
2015-06-26 00:00:00.000 400.00 315.13
2015-06-30 00:00:00.000 400.00 336.13中国风 2015-12-07
SELECT a.fnumber,a.fdate
,(SELECT SUM(finqty) FROM TabName WHERE fnumber=a.fnumber AND ISNULL(fdate,'1900-01-01')<=ISNULL(a.fdate,'1900-01-01')) AS finqty
,(SELECT SUM(foutqty) FROM TabName WHERE fnumber=a.fnumber AND ISNULL(fdate,'1900-01-01')<=ISNULL(a.fdate,'1900-01-01')) AS foutqty
FROM TabName AS a日期格式汇总 DateFormat 提供了很多类方法，以获得基于默认或给定语言环境和多种格式化风格的默认日期/时间 Formatter。格式化风格包括 FULL、LONG、MEDIUM 和 SHORT。方法描述中提供了使用这些风格的更多细节和示例。 ...
java日期格式汇总 日期格式汇总 转载2017年05月23日 17:22:25 DateFormat java.text.DateFormat public abstract class DateFormat extends Format DateFormat 是日期/时间格式化子类的抽象类，它以与语言无关的方式格式...
es 按照日期字段，统计每天总数 6、查询日期字段： 见【3.2 函数支持】，使用date_format函数，支持传递时区。 7、group by分组，详情见【3.3 聚合】： l group by 一个字段： SELECT COUNT(*) FROM mrp_userlabel GROUP BY CityName;...
MM物料主数据的字段解释 一、基础数据1、产品组 division1.1 字段说明对需销售的产品和服务进行分类。 SAP 的中文翻译有两种，一个是“部门” ，另一个是“产品组” （本手册使用） 。1.2 字段作用确定物料、产品或服务的销售范围和业务范围...
ORACLE中日期和时间函数汇总(转载) 在oracle中处理日期大全 TO_DATE格式 Day: dd number 12 dy abbreviated fri day spelled out friday ddspth spelled out, ordinal twelfth Month: mm number 03 mon abbreviated mar month spelled out ...
php面试题汇总（必会） 1、酒店预订怎么实现？怎么设计表 ...用户选中要入住的酒店进入酒店详情页面，查看酒店的介绍以及酒店的房型列表，用户根据他要入住的时间和离店的时间，检索出这个时间段内的所有可选房型（房间数量-当天
日期维度上不能向上汇总的度量 只是不知道该如何为他命名，差不多就是跟以前遇到的"通话用户数"问题类似，他这里是订购用户数。这个值，在不同时间粒度上，是需要去重计算的。例如下面一组数，想象这是一个用户订购表，每条记录表示用户发生了订购...
2.2.太极平台框架—组件的字段介绍与使用 1.字段介绍 一个组件对应一个数据库表，表有字段，那么组件也有字段。通过设置组件的字段，就可以对应到数据库表的字段。 2.通用设置项 2.1.字段标题 在数据列表页面，字段的标题就是表头名称；在添加编辑页面，...
数仓理论-汇总 处理主题相关的当天的行为，即按天分， dwt：data warehouse topic 基于dws， 按主题进行汇总，累计一定时间周期的结果。 2.8 拉链表 3. Hive函数 3.1 str_to_map函数 将字符串类型的转换为map格式。要求是必须得...
日期函数汇总 --***************** 多种日期格式： YYYY：四位表示的年份 YYY，YY，Y：年份的最后三位、两位或一位，缺省为当前世纪 MM：01~12的月份编号 MONTH：九个字符表示的月份，右边...
Hive3.1学习笔记汇总 comment] --指定表的描述 [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] --指定分区表的分区字段（分区字段可以是多个） [CLUSTERED BY (col_name, col_name, ...) --指定分桶表的分桶字段 ...
根据字段内容条件的统计需求处理 需求：对一组投票日志文件进行日汇总统计，统计出当天所有被投票人的各种类型票数。并将结果放到另外的一张表中。日志文件中有vote_type,vote_mode标识不同的数据类型。日志表名称：vote_logvote_type和vote_model的...
php面试题汇总（1） 判断商品在购物车中是否存在，如果不存在，数量为1,如果存在，数量在原基础上加1 c.将购物车重新装入到session中。 问题:我们的购物使用的是一个HashMap集合，key是唯一的，保证key唯一的方式是...
面试资料汇总 用户选中要入住的酒店进入酒店详情页面，查看酒店的介绍以及酒店的房型列表，用户根据他要入住的时间和离店的时间，检索出这个时间段内的所有可选房型（房间数量-当天的订单-当天未离店订单=剩余房间数量）显示给...
Python学习之路|目录下所有CSV文件汇总至EXCEL 遍历目录下所有CSV文件并汇总至EXCEL前言构思过程详细代码打包指令踩过的坑 前言 好久没有写点东西了，突发奇想把工作中每日需要发送的日报使用python完成，就不需要每天都重复相似的工作N遍。工作日报需要从多个csv...
