“表”结构 账号 vchar 时间 datetime 余额 intselect min(余额),[账号],时间 from 表 group by 时间,账号不知行不行?
select 日期,账户, ( select min(金额) from 表名 where 账户=别名.账户 and 日期 between 一百天前 and 别名.日期 ) 余额 from 表名 别名 where 日期 between 一百天前 and 一百天后 order by 日期,账户 group by 日期,账户
假设表是这样的: fdate,facctid,balance 并且期初余额在此表中有记录,且每天一个账户最多一条记录。并且不考虑日期不连续的问题,可以这样写:select t1.fdate,t2.facctid, isnull(bal.balance, (select balance from bal t3 where facctid=t2.facctid and fdate=(select max(fdate) from bal where fdate<=t1.fdate)) ) as balance from bal, (select distinct fdate from bal) t1 , (select distinct facctid from bal) t2 where bal.fdate=*t1.fdate and bal.facctid=*t2.facctid order by t1.fdate,t2.facctidMS出这样的题吗?这么不明确,有什么意思啊???
to icevi(按钮工厂)(女) 做存储过程?那花一百万追你也一定可以 你多大啦
写错了:( 上面的语句改成: select t1.fdate,t2.facctid, isnull(bal.balance, (select balance from bal t3 where facctid=t2.facctid and fdate=(select max(fdate) from bal t4 where t4.facctid=t2.facctid and fdate<=t1.fdate))) as balance from bal, (select distinct fdate from bal) t1 , (select distinct facctid from bal) t2 where bal.fdate=*t1.fdate and bal.facctid=*t2.facctid order by t1.fdate,t2.facctid
按microyzy(毛毛叉)的表结构: select min(余额),[账号],max(时间n) from 表 group by 时间,账号
请教:supine(懒人) 为什么要用 max()? 并且还是解决不了账号某一天没有支出得情况。
CREATE TABLE [dbo].[TABLE2] ( [username] [varchar] (50) NOT NULL , --用户名 [outdate] [datetime] NOT NULL , --日期 [cash] [float] NOT NULL --余额 ) ON [PRIMARY]由于每天每个用户可能有多条纪录,我们需要用户每天余额最小的那条纪录: select username,outdate,min(cash) from table2 group by outdate,username order by outdate,username
是个问题,100天中可能有很多天都是没有纪录的。 Thinking...
关键是怎样产生一百天内完整的日期 icevi(按钮工厂)(女) 你不是说可以查询生成吗,SQL呢
能不能这样啊select IDENTITY(smallint, 1, 1) AS id,DATEADD(day, id, minoutdate) as outdate ..................
IDENTITY函数只能在 Select into语句中用.
用一个表保存每天的日期,用Job对象在每天23:59秒里插入一条数据,以这个日期表为驱动表,运用left Join 和 decode()(this is oracle Function, I think MS has this function function too),再加上group by ,应该能解决这个问题。
现在是些用sql表达问题,如果用程序或过程,什么都可以实现他
select 查询.日期,账户, ( select min(金额) from 表名 where 账户=别名.账户 and 日期 between 一百天前 and 查询.日期 ) 余额 from (progame的查询) 查询,表名 别名 where 查询.日期=别名.日期(+) and 查询.日期 between 一百天前 and 一百天后 order by 查询.日期,账户 group by 查询.日期,账户 如果progame的查询可以的话,这样应该也行
一条语句应该不行,象 progame 说的,用identity可以做的,但是要生成临时表才行: 假设从2001-10-1开始 select top 100 identity(int,1,1) as id into #temp from bal select t1.fdate,t2.facctid, isnull(bal.balance, (select balance from bal t3 where facctid=t2.facctid and fdate=(select max(fdate) from bal t4 where t4.facctid=t2.facctid and fdate<=t1.fdate))) as balance from bal, (select cast(id+cast('2001-10-1' as int)-1 as datetime) as fdate from #temp) t1 , (select distinct facctid from bal) t2 where bal.fdate=*t1.fdate and bal.facctid=*t2.facctid order by t1.fdate,t2.facctid
按钮,如果bal中没有100条记录呢?
progame的查询,用Oracle可以这样 Select 日期+RowNum From (Select Distinct 帐号 From 表名);
progame:肯定有100条记录的,因为有100个账户的期初余额啊!
大不了 (Select 日期1) Union (Select 日期2) Union (Select 日期3) Union (Select 日期4)
账号 vchar
时间 datetime
余额 intselect min(余额),[账号],时间 from 表 group by 时间,账号不知行不行?
(
select min(金额)
from 表名
where 账户=别名.账户 and 日期 between 一百天前 and 别名.日期
) 余额
from 表名 别名
where 日期 between 一百天前 and 一百天后
order by 日期,账户
group by 日期,账户
fdate,facctid,balance
并且期初余额在此表中有记录,且每天一个账户最多一条记录。并且不考虑日期不连续的问题,可以这样写:select t1.fdate,t2.facctid,
isnull(bal.balance,
(select balance from bal t3 where facctid=t2.facctid and
fdate=(select max(fdate) from bal where fdate<=t1.fdate))
) as balance
from bal,
(select distinct fdate from bal) t1 ,
(select distinct facctid from bal) t2
where bal.fdate=*t1.fdate and bal.facctid=*t2.facctid
order by t1.fdate,t2.facctidMS出这样的题吗?这么不明确,有什么意思啊???
做存储过程?那花一百万追你也一定可以
你多大啦
上面的语句改成:
select t1.fdate,t2.facctid,
isnull(bal.balance,
(select balance from bal t3 where facctid=t2.facctid and
fdate=(select max(fdate) from bal t4 where t4.facctid=t2.facctid and fdate<=t1.fdate))) as balance
from bal,
(select distinct fdate from bal) t1 ,
(select distinct facctid from bal) t2
where bal.fdate=*t1.fdate and bal.facctid=*t2.facctid
order by t1.fdate,t2.facctid
select min(余额),[账号],max(时间n) from 表 group by 时间,账号
为什么要用 max()?
并且还是解决不了账号某一天没有支出得情况。
[username] [varchar] (50) NOT NULL , --用户名
[outdate] [datetime] NOT NULL , --日期
[cash] [float] NOT NULL --余额
) ON [PRIMARY]由于每天每个用户可能有多条纪录,我们需要用户每天余额最小的那条纪录:
select username,outdate,min(cash) from table2
group by outdate,username
order by outdate,username
Thinking...
icevi(按钮工厂)(女) 你不是说可以查询生成吗,SQL呢
IDENTITY函数只能在
Select into语句中用.
(
select min(金额)
from 表名
where 账户=别名.账户 and 日期 between 一百天前 and 查询.日期
) 余额
from (progame的查询) 查询,表名 别名
where 查询.日期=别名.日期(+) and 查询.日期 between 一百天前 and 一百天后
order by 查询.日期,账户
group by 查询.日期,账户
如果progame的查询可以的话,这样应该也行
假设从2001-10-1开始
select top 100 identity(int,1,1) as id into #temp from bal select t1.fdate,t2.facctid,
isnull(bal.balance,
(select balance from bal t3 where facctid=t2.facctid and
fdate=(select max(fdate) from bal t4 where t4.facctid=t2.facctid and fdate<=t1.fdate))) as balance
from bal,
(select cast(id+cast('2001-10-1' as int)-1 as datetime) as fdate from #temp) t1 ,
(select distinct facctid from bal) t2
where bal.fdate=*t1.fdate and bal.facctid=*t2.facctid
order by t1.fdate,t2.facctid
Select 日期+RowNum From (Select Distinct 帐号 From 表名);
(Select 日期1)
Union
(Select 日期2)
Union
(Select 日期3)
Union
(Select 日期4)
一万天,我就一万个,哈哈