请教下这样的SQL要怎样来写

Question

需要查询出红色部分的内容，在相同合同编号下添加时间最早那条记录的上期累计为0.00
小弟SQL实在太烂。虚心请教下，先谢谢了
-------------------------表里的数据-----------------------------
ID       合同名称                发生金额        添加时间
1 FAJY       200.00 2010-06-18 14:56:50
2 FAJY       50.00 2010-06-18 15:26:20
3 LXJY       600.00 2010-06-18 16:15:53
4 LXJY       450.00 2010-06-18 17:10:53
----------------------------------------------------------------------

-------------------------需要得到的结果-----------------------------
ID       合同名称     上期累计           发生金额        本期累计                                添加时间
1 FAJY         0.00          200.00    200.00              2010-06-18 14:56:50
2 FAJY         200.00        50.00    250.00              2010-06-18 15:26:20
3 LXJY         0.00    600.00    600.00              2010-06-18 16:15:53
4 LXJY         600.00        450.00    1050.00             2010-06-18 17:10:53
5 FAJY         250.00        150.00    400.00              2010-06-18 15:26:20
----------------------------------------------------------------------
------------------------------表结构-----------------------------------------
USE [CPSDB]
GO
/****** 对象:  Table [dbo].[CESHI]    脚本日期: 06/18/2010 16:33:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CESHI](
[id] [int] IDENTITY(1,1) NOT NULL,
[HTBH] [char](10) COLLATE Chinese_PRC_CI_AS NULL,
[Price] [decimal](18, 2) NULL,
[datetime] [datetime] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
[id] 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
SET ANSI_PADDING OFF

Answer 1

数据脚本
---------------------------------------------------
if exists (select * from sysobjects where id = OBJECT_ID('[XCGL_DKFY]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [XCGL_DKFY]CREATE TABLE [XCGL_DKFY] (
[DKID] [int]  IDENTITY (1, 1)  NOT NULL,
[XMBH] [char]  (6) NOT NULL,
[HTBH] [char]  (20) NOT NULL,
[SZXM] [char]  (5) NOT NULL,
[FSJE] [decimal]  (18,2) NOT NULL,
[IsBL] [char]  (1) NULL,
[BLJE] [decimal]  (18,2) NULL,
[JDFK] [decimal]  (18,2) NULL,
[XMMS] [nchar]  (1000) NULL,
[CZY] [nchar]  (10) NOT NULL,
[TJSJ] [datetime]  NOT NULL,
[XGSJ] [datetime]  NULL,
[BZ] [nvarchar]  (500) NULL)ALTER TABLE [XCGL_DKFY] WITH NOCHECK ADD  CONSTRAINT [PK_XCGL_DKFY] PRIMARY KEY  NONCLUSTERED ( [DKID] )
SET IDENTITY_INSERT [XCGL_DKFY] ONINSERT [XCGL_DKFY] ([DKID],[XMBH],[HTBH],[SZXM],[FSJE],[IsBL],[BLJE],[XMMS],[CZY],[TJSJ]) VALUES ( 1,'DAJY','00435','00003',123.00,'0',50.00,'爱的说法是大方','邹鹤','2010-06-18 14:56:50')
INSERT [XCGL_DKFY] ([DKID],[XMBH],[HTBH],[SZXM],[FSJE],[IsBL],[BLJE],[XMMS],[CZY],[TJSJ]) VALUES ( 2,'DAJY','00435','00003',200.00,'0',100.00,'爱的说法是大方','邹鹤','2010-06-18 15:26:20')
INSERT [XCGL_DKFY] ([DKID],[XMBH],[HTBH],[SZXM],[FSJE],[IsBL],[BLJE],[XMMS],[CZY],[TJSJ]) VALUES ( 3,'FAJY','00477','00003',222.00,'0',333.00,'444','邹鹤','2010-06-18 16:15:53')
INSERT [XCGL_DKFY] ([DKID],[XMBH],[HTBH],[SZXM],[FSJE],[IsBL],[BLJE],[XMMS],[CZY],[TJSJ]) VALUES ( 4,'FAJY','00477','00003',122.00,'0',0.00,'444','邹鹤','2010-06-18 16:15:53')
INSERT [XCGL_DKFY] ([DKID],[XMBH],[HTBH],[SZXM],[FSJE],[IsBL],[BLJE],[XMMS],[CZY],[TJSJ]) VALUES ( 5,'DAJY','00435','00003',150.00,'0',100.00,'爱的说法是大方','邹鹤','2010-06-18 19:26:20')SET IDENTITY_INSERT [XCGL_DKFY] OFF

Answer 2

if exists (select * from sysobjects where id = OBJECT_ID('[CESHI]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [CESHI]CREATE TABLE [CESHI] (
[id] [int]  IDENTITY (1, 1)  NOT NULL,
[HTBH] [char]  (10) NULL,
[Price] [decimal]  (18,2) NULL,
[datetime] [datetime]  NULL)ALTER TABLE [CESHI] WITH NOCHECK ADD  CONSTRAINT [PK_CESHI] PRIMARY KEY  NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [CESHI] ONINSERT [CESHI] ([id],[HTBH],[Price],[datetime]) VALUES ( 1,'FAJY',200.00,'2010-06-18 14:56:50')
INSERT [CESHI] ([id],[HTBH],[Price],[datetime]) VALUES ( 2,'FAJY',50.00,'2010-06-18 15:26:20')
INSERT [CESHI] ([id],[HTBH],[Price],[datetime]) VALUES ( 3,'LXJY',600.00,'2010-06-18 16:15:53')
INSERT [CESHI] ([id],[HTBH],[Price],[datetime]) VALUES ( 4,'LXJY',450.00,'2010-06-18 17:10:53')
INSERT [CESHI] ([id],[HTBH],[Price],[datetime]) VALUES ( 2,'FAJY',150.00,'2010-06-18 16:26:20')SET IDENTITY_INSERT [CESHI] OFF

Answer 3

lz的例子有点乱乱的
select DKID as ID, XMBH as 合同名称 ,
上期累计=(select isnull(sum(FSJE),0) from XCGL_DKFY where XMBH=t.XMBH and TJSJ<t.TJSJ),
FSJE as 发生金额,
本期累计=(select sum(FSJE) from XCGL_DKFY where XMBH=t.XMBH and TJSJ<=t.TJSJ),
TJSJ 添加时间
from XCGL_DKFY t
/*
ID          合同名称   上期累计                                     发生金额                 本期累计                                     添加时间                                                   
----------- ------ ---------------------------------------- -------------------- ---------------------------------------- ------------------------------------------------------ 
1           DAJY   .00                                      123.00               123.00                                   2010-06-18 14:56:50.000
2           DAJY   123.00                                   200.00               323.00                                   2010-06-18 15:26:20.000
3           FAJY   .00                                      222.00               344.00                                   2010-06-18 16:15:53.000
4           FAJY   .00                                      122.00               344.00                                   2010-06-18 16:15:53.000
5           DAJY   323.00                                   150.00               473.00                                   2010-06-18 19:26:20.000（所影响的行数为 5 行）
*/ 

Answer 4

怪不得，原来lz给错了
select ID, HTBH as 合同名称 ,
上期累计=(select isnull(sum(Price),0) from CESHI where HTBH=t.HTBH and datetime<t.datetime),
Price as 发生金额,
本期累计=(select sum(Price) from CESHI where HTBH=t.HTBH and datetime<=t.datetime),
datetime 添加时间
from CESHI t
/*
ID          合同名称       上期累计                                     发生金额                 本期累计                                     添加时间                                                   
----------- ---------- ---------------------------------------- -------------------- ---------------------------------------- ------------------------------------------------------ 
1           FAJY       .00                                      200.00               200.00                                   2010-06-18 14:56:50.000
2           FAJY       200.00                                   50.00                250.00                                   2010-06-18 15:26:20.000
3           LXJY       .00                                      600.00               600.00                                   2010-06-18 16:15:53.000
4           LXJY       600.00                                   450.00               1050.00                                  2010-06-18 17:10:53.000
5           FAJY       250.00                                   150.00               400.00                                   2010-06-18 16:26:20.000（所影响的行数为 5 行）
*/