单一查询无法达到你的要求

如果你不忌惮会给系统造成过大负担，可以考虑用游标

你执行一下下面的语句

参考修改吧。如果你能理解的话，由于用到了游标，对于系统的负担比较大

begin tran
create table #tmp3
(
order_date datetime,
startdate datetime,
enddate datetime
)

insert into #tmp3
select '2006-1-4','2006-1-1','2006-1-4'
union
select '2006-1-5','2006-1-1','2006-1-5'
union
select '2006-1-6','2006-1-1','2006-1-6'
union
select '2006-1-9','2006-1-1','2006-1-9'
union
select '2006-1-10','2006-1-1','2006-1-10'
union
select '2006-1-11','2006-1-1','2006-1-11'

create table #tmp2
(
order_date datetime,
type nvarchar(10),
family_code nvarchar(10),
damount numeric(10,5)
)

insert into #tmp2
select '2006-1-4','china','hid',2
union
select '2006-1-4','ulc','el',3
union
select '2006-1-5','china','hid',4
union
select '2006-1-5','china','el',5
union
select '2006-1-6','china','el',6
union
select '2006-1-9','ulc','el',7
union
select '2006-1-10','china','el',8
union
select '2006-1-11','china','el',9

-- select *
-- from #tmp3
--
-- select *
-- from #tmp2

select aa.order_date,aa.type,aa.family_code,bb.Tamount
into #tmp4
from
(
select distinct *
from
(
(
select order_date
from #tmp2
group by order_date
) a
cross join
(
select type,family_code
from #tmp2
group by type,family_code
) b
)

) aa
left outer join
(
select order_date,type,family_code,
(
select sum(Damount)
from #tmp2
where order_date between
(
select startdate from #tmp3
where a.order_date=#tmp3.order_date
)
and
(
select enddate from #tmp3
where a.order_date=#tmp3.order_date
)
and a.type=#tmp2.type
and a.family_code=#tmp2.family_code
) as Tamount
from #tmp2 a
group by order_date,type,family_code
) bb
on aa.order_date=bb.order_date
and aa.type=bb.type
and aa.family_code=bb.family_code
order by aa.order_date

-- select *
-- from #tmp4

declare @odate datetime
declare @type varchar(10)
declare @fcode varchar(10)
declare @amount numeric(10,5)
declare updatecursor cursor
for select order_date,type,family_code,Tamount
from #tmp4
order by Tamount asc
open updatecursor
fetch next from updatecursor
into @odate,@type,@fcode,@amount
while @@fetch_status=0 and @amount is null
begin
update #tmp4
set Tamount=
(
select Tamount
from #tmp4
where
order_date=(select max(order_date) from #tmp4 where order_date<@odate and type=@type and family_code=@fcode)
and type=@type
and family_code=@fcode
)
where order_date=@odate
and type=@type
and family_code=@fcode
fetch next from updatecursor
into @odate,@type,@fcode,@amount
end

close updatecursor
deallocate updatecursor

select order_date,type,family_code,isnull(Tamount,0) Tamount
from #tmp4
order by order_date,type,family_code desc
rollback tran

-- drop table #tmp4
-- drop table #tmp3
-- drop table #tmp2

[此贴子已经被作者于2006-6-1 14:24:12编辑过]

可不可以不用group by，而是直接用分类的方法
select order_date,
(

select sum(Damount)
from #tmp2
where order_date between
(
select startdate from #tmp3
where a.order_date=#tmp3.order_date
)
and
(
select enddate from #tmp3
where a.order_date=#tmp3.order_date
)
and
#tmp2.type='china'
and
#tmp2.family_code='el'
) as Tceamount,
(

select sum(Damount)
from #tmp2
where order_date between
(
select startdate from #tmp3
where a.order_date=#tmp3.order_date
)
and
(
select enddate from #tmp3
where a.order_date=#tmp3.order_date
)
and
#tmp2.type='china'
and
#tmp2.family_code='hid'
) as Tchamount,
(

select sum(Damount)
from #tmp2
where order_date between
(
select startdate from #tmp3
where a.order_date=#tmp3.order_date
)
and
(
select enddate from #tmp3
where a.order_date=#tmp3.order_date
)
and
#tmp2.type='ulc'

) as Tuamount
from #tmp3 a
因为我的类型就只有 china-el，china-hid，ulc-el三种类型，所以我直接作为三个字段来查询，不过我这样查询出来的结果不对啊？不知道哪里有问题~~帮忙看看吧，谢谢!

不对在哪里？

此外这样的视图跟你开始要求的视图不同，更改也无所谓？

我要的只是 对应日期order_date的三个分类汇总值，至于是不是分为3个字段来显示无所谓的，这是我执行后得到的数据，肯定是不对的阿~ 可是不知道怎么改

2006-01-04 00:00:00.000 NULL 2.00000 3.00000
2006-01-05 00:00:00.000 5.00000 6.00000 3.00000
2006-01-06 00:00:00.000 11.00000 6.00000 3.00000
2006-01-09 00:00:00.000 11.00000 6.00000 10.00000
2006-01-10 00:00:00.000 19.00000 6.00000 10.00000
2006-01-11 00:00:00.000 28.00000 6.00000 10.00000

你给出的数据得到这样的结果是正常的，

你认为应该得到什么样的数据？重新验证一下你希望得到的数据是不是自己计算错误（单纯的计算能力人脑是永远比不上电脑的）

就是这样了，呵呵~~ 谢谢，计算错误，不好意思！要的就是这样的效果，不过你说的游标的话，有空再研究一下。