这么晚了还问数据查询的问题

[sxr520cwx]

表A
id name buy time
1 x1 10 2008-01-01
2 x2 20 2008-01-02
3 x3 10 2008-01-03
4 x4 10 2008-01-04
5 x5 10 2008-01-05
6 x4 10 2008-01-05
7 x1 10 2008-01-06


我想查询2008-01-01 到2008-01-06之间的购买量(buy)少于20的用户
X1：20；X2：20；X3：10 ；X4：20；X5：10；
结果应该为x5,x3

[-狙击手-]

select name
from
(select name ,sum(buy) as buy
 from ta
 where time between '2008-01-01 ' and '2008-01-06'
 group by name) a
where buy < 20

[dawugui]

[Quote=引用楼主 sxr520cwx 的帖子:]
表A
id name buy time
1 x1 10 2008-01-01
2 x2 20 2008-01-02
3 x3 10 2008-01-03
4 x4 10 2008-01-04
5 x5 10 2008-01-05
6 x4 10 2008-01-05
7 x1 10 2008-01-06


我想查询2008-01-01 到2008-01-06之间的购买量(buy)少于20的用户
X1：20；X2：20；X3：10 ；X4：20；X5：10；
结果应该为x5,x3
[/Quote]
select name from A where time between '2008-01-01' and '2008-01-06' group by buy having sum(buy) < 20

[liangCK]

select name
from
(select name ,sum(buy) as buy
from ta
where time between '2008-01-01 ' and '2008-01-06'
group by name) a
where buy < 20

[dawugui]

create table A(id int, name varchar(10) , buy int, time datetime)
insert into A values(1 , 'x1' , 10 , '2008-01-01') 
insert into A values(2 , 'x2' , 20 , '2008-01-02') 
insert into A values(3 , 'x3' , 10 , '2008-01-03') 
insert into A values(4 , 'x4' , 10 , '2008-01-04') 
insert into A values(5 , 'x5' , 10 , '2008-01-05') 
insert into A values(6 , 'x4' , 10 , '2008-01-05') 
insert into A values(7 , 'x1' , 10 , '2008-01-06') 
go

select name from A where time between '2008-01-01' and '2008-01-06' group by name having sum(buy)  < 20

drop table A

/*
name       
---------- 
x3
x5

（所影响的行数为 2 行）
*/

[dl110]

潇洒老乌龟(爱新觉罗.毓华)同志正解.

[lican990602]

create table tb(id int identity(1,1),[name] varchar(10),buy int,[time] datetime)
insert into tb values('x1' ,10 ,'2008-01-01')
insert into tb values('x2' ,20 ,'2008-01-02')
insert into tb values('x3' ,10 ,'2008-01-03')
insert into tb values('x4' ,10 ,'2008-01-04')
insert into tb values('x5' ,10 ,'2008-01-05')
insert into tb values('x4' ,10 ,'2008-01-05')
insert into tb values('x1' ,10 ,'2008-01-06')

select [name] from tb where [time] between '2008-01-01' and '2008-01-06' group by [name] having sum(buy)<20

drop table tb

[-狙击手-]

create table A(id int, name varchar(10) , buy int, time datetime)
insert into A values(1 , 'x1' , 10 , '2008-01-01') 
insert into A values(2 , 'x2' , 20 , '2008-01-02') 
insert into A values(3 , 'x3' , 10 , '2008-01-03') 
insert into A values(4 , 'x4' , 10 , '2008-01-04') 
insert into A values(5 , 'x5' , 10 , '2008-01-05') 
insert into A values(6 , 'x4' , 10 , '2008-01-05') 
insert into A values(7 , 'x1' , 10 , '2008-01-06') 
go



select name
from
(select name ,sum(buy) as buy
 from a
 where time between '2008-01-01 ' and '2008-01-06'
 group by name) a
where buy < 20


drop table a

/*
name       
---------- 
x3
x5

（所影响的行数为 2 行）
*/

[-狙击手-]

老龟的方法如果设计不得当 会 造成表缺少统计信息，成本加大，