select *
from #tmp3
得到如下数据 #tmp3

order_date 2006-1-4	startdate 2006-1-1	enddate 2006-1-4
2006-1-5	2006-1-1	2006-1-5
2006-1-6	2006-1-1	2006-1-6
2006-1-9	2006-1-1	2006-1-9
2006-1-10	2006-1-1	2006-1-10
2006-1-11	2006-1-1	2006-1-11

select *
from #tmp2
得到如下

order_date 2006-1-4	type china	family_code hid	damount 415615.3
2006-1-5	china	hid	2052-1-4
2006-1-6	ulc	el	148597.1
2006-1-9	ulc	el	229007.5
2006-1-10	china	el	102882.5

2006-1-11

china

el

113491.4

我要做的就是分类汇总，根据type，family_code
我用你上面的查询的话，加上group by type,family_code 他会报错
查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。那我要实现分类汇总的话要如何做呢？？

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
)
group by type,family_code
) as Tamount
from #tmp3 a

[此贴子已经被作者于2006-5-31 13:33:28编辑过]

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=#temp2.type
and a.family_code=#temp2.family_code
) as Tamount
from #tmp2 a
group by order_date,type,family_code

没有测试，直接在坛子上改的，只不过验证过一下语法没有问题。你自己试试看好了。

写查询的时候要知道自己写的每一句的目的是什么，不是盲目拼凑就能得到结果的

用这个查询的话所有的都是空值

[此贴子已经被作者于2006-5-31 14:28:42编辑过]

刚才有事要出去，回得急了

你最初给出的例子并不能完全概括你实际的案例（没有包含Group）

你拿你11楼的数据说说看你最后究竟想要获得什么样子的数据？

select *
from #tmp3
得到如下数据 #tmp3

order_date 2006-1-4	startdate 2006-1-1	enddate 2006-1-4
2006-1-5	2006-1-1	2006-1-5
2006-1-6	2006-1-1	2006-1-6
2006-1-9	2006-1-1	2006-1-9
2006-1-10	2006-1-1	2006-1-10
2006-1-11	2006-1-1	2006-1-11

select *
from #tmp2
得到如下

order_date 2006-1-4	type china	family_code hid	damount 2
2006-1-4 2006-1-5	ulc china	el hid	3 4
2006-1-5 2006-1-6	china china	el el	5 6
2006-1-9	ulc	el	7
2006-1-10	china	el	8

2006-1-11

china

el

9

我想要得到如下
order_date type family_code Tamount
2006-1-4 china hid 2
2006-1-4 china el 0
2006-1-4 ulc el 3
2006-1-5 china hid 2+4=6
2006-1-5 china el 0+5=5
2006-1-5 ulc el 3+0=3
2006-1-6 china hid 2+4+0=6
2006-1-6 china el 0+5+6=11
2006-1-6 ulc el 3+3+0=6
2006-1-9 china hid 2+4+0+0=6
2006-1-9 china el 0+5+6+0=11
2006-1-9 ulc el 3+3+0+7=13
......
就是这样了

其实我要的跟一开始差不多，唯一的区别就是

select order_date,
(
******* 我想能否把这里的求和做成分类求和呢？也就是按#tmp2中的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
)

) as Tamount
from #tmp3 a

2006-1-4 china el 0

这个原数据中没有你也要列出？

如果不需要的话用下面这个就可以（其实就是我12楼发的，表名改了一下而已，而且不改的话应该是无法执行，而不是你13楼所说的都是空值）

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

实际中是不会有空值的，不过，我用你查询试了，Tamount的值全是Null阿？是算不出来的阿？

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 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
and a.order_date=#tmp2.order_date
) as Tamount
from #tmp2 a
group by order_date,type,family_code

rollback tran

这是全测试语句，你再去试试

大哥，我用你的语句试了试如下效果
2006-01-04 00:00:00.000 china hid 2.00000
2006-01-04 00:00:00.000 ulc el 3.00000
2006-01-05 00:00:00.000 china el 5.00000
2006-01-05 00:00:00.000 china hid 4.00000
2006-01-06 00:00:00.000 china el 6.00000
2006-01-09 00:00:00.000 ulc el 7.00000
2006-01-10 00:00:00.000 china el 8.00000
2006-01-11 00:00:00.000 china el 9.00000
跟我要的
order_date type family_code Tamount
2006-1-4 china hid 2
2006-1-4 china el 0
2006-1-4 ulc el 3
2006-1-5 china hid 2+4=6
2006-1-5 china el 0+5=5
2006-1-5 ulc el 3+0=3
2006-1-6 china hid 2+4+0=6
2006-1-6 china el 0+5+6=11
2006-1-6 ulc el 3+3+0=6
2006-1-9 china hid 2+4+0+0=6
2006-1-9 china el 0+5+6+0=11
2006-1-9 ulc el 3+3+0+7=13
不一样啊，你上面的语句只把对应日期有的type和family_code求和算出来了。
数据不全面阿