谢谢呀.再把问题延深一下..表一名称: table1 有3个字段分别为是 a b c d dateandtime(日期类型)现要求 dateandtime大于2007-7-31 并且 a字段的总为不大于100 dateandtime小于2007-7-21 并且 b字段总和不大于20
上面的描述不对..重新描述表一名称: table1 有4个字段分别为是 a b c d dateandtime(日期类型)现要求 a字段的在2007-7-31之前总和不大于100 b字段的在2007-7-11之前总和不大于20
select sum(a) as a,sum(b) as b,c from table1 where dateandtime > '2007-7-31' group by c /*假设按c列分组*/ having sum(a) <= 100 UNION ALL select sum(a) as a,sum(b) as b,c from table1 where dateandtime < '2007-7-21' group by c /*假设按c列分组*/ having sum(b) <= 20
这样试试: select * from table1 as t where (select sum(a) from table1 where dateandtime = t.dateandtime and dateandtime < '2007-7-31') <= 100 and (select sum(b) from table1 where dateandtime = t.dateandtime and dateandtime < '2007-7-11') <= 20
测试数据表结构如下:CREATE TABLE [test_table] ( [DateAndTime] [datetime] NULL , [field1] [money] NULL , [field2] [money] NULL ) insert into test_table select '2007-7-31',120,10 union all select '2007-7-30',110,11 union all select '2007-7-29',90,12 union all select '2007-7-28',101,32 union all select '2007-7-27',53,32 union all select '2007-7-26',32,134 drop table test_table 条件: 2007-7-30 之前 field1字段总和大于100 且 2007-7-28 之前 field1字段总和不大于400 希望的结果: 满足以上条件输出field1 ,field2两字段的SUM值
declare @test_table table ( [DateAndTime] [datetime] NULL , [field1] [money] NULL , [field2] [money] NULL ) insert into @test_table select '2007-7-31',120,10 union all select '2007-7-30',110,11 union all select '2007-7-29',90,12 union all select '2007-7-28',101,32 union all select '2007-7-27',53,32 union all select '2007-7-26',32,134----查询 SELECT SUM(field1) as field1,SUM(field2) as field2 FROM @test_table as t WHERE (DateAndTime <= '2007-7-30' and (select sum(field1) from @test_table where DateAndTime <= t.DateAndTime) > 100) AND (DateAndTime <= '2007-7-28' and (select sum(field1) from @test_table where DateAndTime <= t.DateAndTime ) <= 400)/*结果 field1 field2 --------------------------------- 101.0000 32.0000 */
谢谢 一两清风 经过你的帮助,我已经做好了.用的就是以下的方法 SELECT SUM(field1) as field1,SUM(field2) as field2 FROM @test_table as t WHERE (DateAndTime <= '2007-7-30' and (select sum(field1) from @test_table where DateAndTime <= t.DateAndTime) > 100) AND (DateAndTime <= '2007-7-28' and (select sum(field1) from @test_table where DateAndTime <= t.DateAndTime ) <= 400)
有3个字段分别为是 a b c d dateandtime(日期类型)现要求 dateandtime大于2007-7-31 并且 a字段的总为不大于100
dateandtime小于2007-7-21 并且 b字段总和不大于20
有4个字段分别为是 a b c d dateandtime(日期类型)现要求 a字段的在2007-7-31之前总和不大于100
b字段的在2007-7-11之前总和不大于20
where dateandtime > '2007-7-31'
group by c /*假设按c列分组*/
having sum(a) <= 100
UNION ALL
select sum(a) as a,sum(b) as b,c from table1
where dateandtime < '2007-7-21'
group by c /*假设按c列分组*/
having sum(b) <= 20
select * from table1 as t where
(select sum(a) from table1 where dateandtime = t.dateandtime and dateandtime < '2007-7-31') <= 100
and
(select sum(b) from table1 where dateandtime = t.dateandtime and dateandtime < '2007-7-11') <= 20
[DateAndTime] [datetime] NULL ,
[field1] [money] NULL ,
[field2] [money] NULL
)
insert into test_table
select '2007-7-31',120,10
union all select '2007-7-30',110,11
union all select '2007-7-29',90,12
union all select '2007-7-28',101,32
union all select '2007-7-27',53,32
union all select '2007-7-26',32,134
drop table test_table 条件: 2007-7-30 之前 field1字段总和大于100
且
2007-7-28 之前 field1字段总和不大于400 希望的结果:
满足以上条件输出field1 ,field2两字段的SUM值
[DateAndTime] [datetime] NULL ,
[field1] [money] NULL ,
[field2] [money] NULL
)
insert into @test_table
select '2007-7-31',120,10
union all select '2007-7-30',110,11
union all select '2007-7-29',90,12
union all select '2007-7-28',101,32
union all select '2007-7-27',53,32
union all select '2007-7-26',32,134----查询
SELECT SUM(field1) as field1,SUM(field2) as field2 FROM @test_table as t WHERE
(DateAndTime <= '2007-7-30' and
(select sum(field1) from @test_table where DateAndTime <= t.DateAndTime) > 100)
AND
(DateAndTime <= '2007-7-28' and
(select sum(field1) from @test_table where DateAndTime <= t.DateAndTime ) <= 400)/*结果
field1 field2
---------------------------------
101.0000 32.0000
*/
SELECT SUM(field1) as field1,SUM(field2) as field2 FROM @test_table as t WHERE
(DateAndTime <= '2007-7-30' and
(select sum(field1) from @test_table where DateAndTime <= t.DateAndTime) > 100)
AND
(DateAndTime <= '2007-7-28' and
(select sum(field1) from @test_table where DateAndTime <= t.DateAndTime ) <= 400)