1、按id分别计算总费用select id,sum(price*num) 总费用 where date between '2003-2-20' and '2003-3-20' group by id
2、一条记录的费用:当前记录和后一条记录的时间差×price×num,如:datediff(day,2003-2-20,2003-3-1)×0.2×2select id,isnull(datediff(day,(select max(date) from 表 aa where aa.时间<表.时间),时间),1)*price*num 总费用 from 表 where date between '2003-2-20' and '2003-3-20'
2、一条记录的费用:当前记录和后一条记录的时间差×price×num,如:datediff(day,2003-2-20,2003-3-1)×0.2×2select id,isnull(datediff(day,(select max(date) from 表 aa where aa.时间<表.时间),时间),1)*price*num 总费用 from 表 where date between '2003-2-20' and '2003-3-20'
解决方案 »
- 求一条SQL SERVER导出文本的语句!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- sql server?
- 一个存储过程的问题 请求帮助
- 对于执行一个比较复杂的数据库操作(多步验证、操作等)是使用一个存储过程实现还是在程序中分多个步骤执行多步对数据库操作好呢?
- 怎样修改默认SCHEMA
- 有关sql server clr 的问题,
- 如何在二个表里的找出两个同名字段的唯一值(很简单的)急,在线等待????????
- 怎样从混合验证转换为SQL验证呀!
- 数据库建表的问题...................
- 菜鸟求一个简单查询
- select into 的问题(在线等)
- 高分求解,关于结果集的问题
datediff(day,A.date,(select min(date) from yourtable where date > A.date and id = A.id))×A.price*A.num as 总费用
from yourtable as A
select A.*,
datediff(day,A.date,
isnull((select min(date) from yourtable where date > A.date and id = A.id),
cast('2003-3-20' as datetime)) *A.price*A.num as 总费用
from yourtable as A
where date betwennt '2003-2-20' and '2003-3-20'
1.
select id, sum(总费用) from
(select A.*,
datediff(day,A.date,
isnull((select min(date) from yourtable where date > A.date and id = A.id),
cast('2003-3-20' as datetime)) *A.price*A.num as 总费用
from yourtable as A
where date betwennt '2003-2-20' and '2003-3-20') as B
group by B.id
from #temp a left join #temp b on a.id=b.id and a.iid=b.iid+1
try:select identity(int,1,1)as iid,* into #temp from table
select id,sum(totalamount) as totalamount from (
select a.id,cast(cast(isnull(b.[date],a.[date]) as datetime)-cast(a.[date] as datetime)as int)*0.2*2 as TotalAmount
from #temp a left join #temp b on a.id=b.id and a.iid=b.iid+1)aaa
group by id
select id,sum(price*num) 总费用 where date between '2003-2-20' and '2003-3-20' group by id
2:
select A.*,
datediff(day,A.date,
isnull((select min(date) from yourtable where date > A.date and id = A.id),
cast('2003-3-20' as datetime)) *A.price*A.num as 总费用
from yourtable as A
where date betwennt '2003-2-20' and '2003-3-20'
我自己看了
谢谢