表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
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
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
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
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')
goselect name from A where time between '2008-01-01' and '2008-01-06' group by name having sum(buy) < 20drop table A/*
name
----------
x3
x5(所影响的行数为 2 行)
*/
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
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')
goselect 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 行)
*/