求先进先出查询语句的写法

[zjl8008]

比如有如下客户预付款表，查询截止到任意时间为止，客户消费XX元（另有表记录明细（rq,je，表结构类似预付款，但消费没有付款方式)，汇总得到xx）后，预付款表的余额（没有这一列，按时间来计算） 先进先出，按付款方式汇总。
select ..... where rq <'???????'
查询的结果类似如下
A 现金 50
A微信 80
B 支付宝 10
B 现金 150
。。。

CREATE TABLE [dbo].[test_yj](
	[rq] [datetime] NOT NULL, --日期不会重复
	[khbh] [varchar](50) NULL, --客户编号
	[fkfs] [varchar](50) NULL, --付款方式
	[je] [money] NULL,  --金额
 CONSTRAINT [PK_test_yj] PRIMARY KEY CLUSTERED 
(
	[rq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-01 08:20:00.000','B','支付宝',100.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-01 15:20:00.000','A','现金',100.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-02 05:20:00.000','A','微信',200.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-02 08:20:00.000','B','微信',100.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-03 08:20:00.000','B','微信',200.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-03 15:20:00.000','A','支付宝',300.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-04 08:20:00.000','B','支付宝',100.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-04 15:20:00.000','A','现金',150.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-05 08:20:00.000','B','现金',200.00)

[zjl8008]

[ljl_5085]

你是想实现这种效果么？

[Yole]

参考库存先进先出： https://blog.csdn.net/yole_grise/article/details/38371705

[zjl8008]

引用 2 楼 ljl_5085 的回复:

你是想实现这种效果么？

是的，是按先进先出减去消费的余额后的

[jereco]

CREATE TABLE [dbo].[test_yj](
 [rq] [datetime] NOT NULL, --日期不会重复
 [khbh] [varchar](50) NULL, --客户编号
 [fkfs] [varchar](50) NULL, --付款方式
 [je] [money] NULL,  --金额
 CONSTRAINT [PK_test_yj] PRIMARY KEY CLUSTERED 
(
 [rq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-01 08:20:00.000','B','支付宝',100.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-01 15:20:00.000','A','现金',1200.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-02 05:20:00.000','A','微信',200.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-02 08:20:00.000','B','微信',100.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-03 08:20:00.000','B','微信',200.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-03 15:20:00.000','A','支付宝',300.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-04 08:20:00.000','B','支付宝',100.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-04 15:20:00.000','A','现金',150.00)
insert   test_yj   (rq,khbh,fkfs,je)      values   (   '2020-06-05 08:20:00.000','B','现金',200.00)

create table #xf(
[khbh] varchar(13),
[xfje] numeric(16,6)) 
 
insert into #xf select 'A',20
insert into #xf select 'B',150
insert into #xf select 'C',180

select *,row_number() over(partition by khbh order by rq) as num
into #yj2
from test_yj

select * from #yj2

select a.rq,khbh,fkfs,case when 排序=1 then 余额 else je end as je from
 (select rq,khbh,fkfs,row_number()over(partition by khbh  order by rq)排序,je,余额  from (
  select *,累计-xfje as 余额 from (
   select a.*,sum(je)over(partition by a.khbh order by num)累计,b.xfje from #yj2 a left join #xf b on a.khbh=b.khbh )a where 累计>=xfje)a )a

用户名分组时间排序，累加统计用户预付款表累积余额，再通过筛选找出最后一笔抵扣消费款的记录，再排序及运算得出现在预付款的余额。

[唐诗三百首]

请先考虑这样的系统设计的实际运行性能是否ok? 每次查询均需查询并计算客户的所有流水账,随着数据量和并发量的持续增大,是否真能承受这样的磁盘IO和CPU运算压力.

[冰思雨]

1. 提取出 用户编号的列表，里面的 khbh不重复。 2. 编写程序循环遍历每一个用户（可以另外写程序处理，或者，编写存储过程也行，推荐另外用程序来实现）。 3. 针对每一个用户： A. 查询出用户在某个时间范围内的消费总额，用于抵消预存款；B. 按照时间先后列出当前用户的预存款记录，然后，遍历预存款记录来抵消消费总额；C. 如果上述操作是每日都会（或者周期性）进行的，那么要创建一个结果表来存放处理结果，如果不是周期性任务，那么想办法变成周期性的任务。 4. 如果全部都采用SQL语句来处理这个事情，一来语句会写的很复杂，二来执行效率也并不见得多么搞。使用第三方程序的好处是：1.可以方便的编写定时任务；2.可以同时处理多个用户的数据（多线程技术）；3. 通过两个嵌套的循环结构，可以很方便的看出业务逻辑，代码易读性较高，后期好维护。