请教这个查询怎么写~

Question

create table payrecord(
[voucher] [nvarchar](7)not null default'',
[billdate] [smalldatetime]not null default getdate(),
[supplier] [nvarchar](5)not null default'',
[fileno] [nvarchar](11)not null default'',
[director] [nvarchar](13)not null default'',
[amount] [decimal](9,2)not null default 0,
[pay] [decimal](9,2)not null default 0,
[termday] [smalldatetime]not null default getdate(),
[exchange] [nvarchar](10)not null default''
)
--数据如下
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,18.00,'2010-12-11 09:57:00',''
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,62.00,'2010-12-11 09:59:00',''
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,800.00,'2010-12-11 11:38:00',''  
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,100.00,'2010-12-12 11:34:00',''  
insert into payrecord select '0000002','2010-12-09 16:51:00','01030','20100808001','Sunny',915.00,5.00,'2010-12-13 11:30:00',''
insert into payrecord select '0000002','2010-12-09 16:51:00','01030','20100808001','Sunny',915.00,10.00,'2010-12-14 11:35:00',''   
insert into payrecord select '0000002','2010-12-09 16:51:00','01030','20100808001','Sunny',915.00,500.00,'2010-12-14 11:39:00',''   
insert into payrecord select '0000003','2011-01-09 10:01:00','DFG01','20110108001','Sammy',800.00,0,'',''   
--现在要生成一个结果如下的查询
--对voucher归类求和pay结果栏为finish,实际pay/termday/exchange 是termday离今最近的一次值,
voucher billdate                supplier fileno      director    amount     pay       termday               exchange   finish
------- ----------------------- -------- ----------- ---------- ---------- --------- --------------------- ---------- --------
0000001 2010-12-09 16:51:00     01030    20100808000 Salin       1880.00    800.00    2010-12-11 11:38:00              980.00
0000002 2010-12-09 16:51:00     01030    20100808001 Sunny       915.00     500.00    2010-12-14 11:39:00              515.00
0000003 2011-01-09 10:01:00     DFG01    20100808001 Sammy       800.00       0       2011-01-09 10:01:00              0
*/

Answer 1

select * from payrecord t
where not exists(select * from payrecord where voucher=t.voucher and termday>t.termday)

Answer 2

create table payrecord(
[voucher] [nvarchar](7)not null default'',
[billdate] [smalldatetime]not null default getdate(),
[supplier] [nvarchar](5)not null default'',
[fileno] [nvarchar](11)not null default'',
[director] [nvarchar](13)not null default'',
[amount] [decimal](9,2)not null default 0,
[pay] [decimal](9,2)not null default 0,
[termday] [smalldatetime]not null default getdate(),
[exchange] [nvarchar](10)not null default''
)
--数据如下
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,18.00,'2010-12-11 09:57:00',''
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,62.00,'2010-12-11 09:59:00',''
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,800.00,'2010-12-11 11:38:00',''   
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,100.00,'2010-12-12 11:34:00',''   
insert into payrecord select '0000002','2010-12-09 16:51:00','01030','20100808001','Sunny',915.00,5.00,'2010-12-13 11:30:00',''
insert into payrecord select '0000002','2010-12-09 16:51:00','01030','20100808001','Sunny',915.00,10.00,'2010-12-14 11:35:00',''   
insert into payrecord select '0000002','2010-12-09 16:51:00','01030','20100808001','Sunny',915.00,500.00,'2010-12-14 11:39:00',''   
insert into payrecord select '0000003','2011-01-09 10:01:00','DFG01','20110108001','Sammy',800.00,0,'',''   select t.* ,finish = case when t.pay <> 0 then t.amount - t.pay else 0 end
from payrecord t where termday = (select max(termday) from payrecord where voucher = t.voucher) order by t.voucher
/*
voucher billdate                                               supplier fileno      director      amount      pay         termday                                                exchange   finish       
------- ------------------------------------------------------ -------- ----------- ------------- ----------- ----------- ------------------------------------------------------ ---------- ------------ 
0000001 2010-12-09 16:51:00                                    01030    20100808000 Salin         1880.00     100.00      2010-12-12 11:34:00                                               1780.00
0000002 2010-12-09 16:51:00                                    01030    20100808001 Sunny         915.00      500.00      2010-12-14 11:39:00                                               415.00
0000003 2011-01-09 10:01:00                                    DFG01    20110108001 Sammy         800.00      .00         1900-01-01 00:00:00                                               .00（所影响的行数为 3 行）
*/select t.* ,finish = case when t.pay <> 0 then t.amount - t.pay else 0 end
from payrecord t where not exists (select 1 from payrecord where voucher = t.voucher and termday > t.termday) order by t.voucher
/*
voucher billdate                                               supplier fileno      director      amount      pay         termday                                                exchange   finish       
------- ------------------------------------------------------ -------- ----------- ------------- ----------- ----------- ------------------------------------------------------ ---------- ------------ 
0000001 2010-12-09 16:51:00                                    01030    20100808000 Salin         1880.00     100.00      2010-12-12 11:34:00                                               1780.00
0000002 2010-12-09 16:51:00                                    01030    20100808001 Sunny         915.00      500.00      2010-12-14 11:39:00                                               415.00
0000003 2011-01-09 10:01:00                                    DFG01    20110108001 Sammy         800.00      .00         1900-01-01 00:00:00                                               .00（所影响的行数为 3 行）
*/drop table payrecord

Answer 3

;with t as (
select *,row_number()over(partition by voucher order by pay Desc,(getdate()-termday) Asc,exchange ) as cnt from payrecord
)
select voucher, billdate, supplier, fileno, director, amount, pay, termday, exchange from t where cnt=1

Answer 4

;with t as (
select *,row_number()over(partition by voucher order by pay Desc,(getdate()-termday) Asc) as cnt,sum(pay)over(partition by voucher ) as sumall from payrecord
)
select voucher, billdate, supplier, fileno, director, amount, pay, termday,sumall as exchange from t where cnt=1
voucher billdate                supplier fileno      director      amount                                  pay                                     termday                 exchange
------- ----------------------- -------- ----------- ------------- --------------------------------------- --------------------------------------- ----------------------- ---------------------------------------
0000001 2010-12-09 16:51:00     01030    20100808000 Salin         1880.00                                 800.00                                  2010-12-11 11:38:00     980.00
0000002 2010-12-09 16:51:00     01030    20100808001 Sunny         915.00                                  500.00                                  2010-12-14 11:39:00     515.00
0000003 2011-01-09 10:01:00     DFG01    20110108001 Sammy         800.00                                  0.00                                    1900-01-01 00:00:00     0.00(3 row(s) affected)

Answer 5

SELECT A.voucher ,A.billdate ,A.supplier ,A.fileno,A.director ,A.amount ,b.pay,a.termday,SUM(a.pay) as finish
FROM payrecord A where not exists( select 1 from payrecord b where b.voucher =a.voucher and b.billdate =a.billdate and b.supplier =a.supplier ... and b.termday >a.termday)
GROUP BY A.voucher ,A.billdate ,A.supplier ,A.fileno,A.director ,A.amount 

Answer 6

查询的finish 应该是sum(pay)的值