• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

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

sxr520cwx 2008-04-02 10:25:01
表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


...全文
49 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
-狙击手- 2008-04-02
老龟的方法如果设计不得当 会 造成表缺少统计信息,成本加大,
回复
-狙击手- 2008-04-02
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 行)
*/
回复
lican990602 2008-04-02
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
回复
dl110 2008-04-02
潇洒老乌龟(爱新觉罗.毓华)同志正解.
回复
dawugui 2008-04-02
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 行)
*/

回复
liangCK 2008-04-02
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 2008-04-02
[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
回复
-狙击手- 2008-04-02
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
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-02 10:25
社区公告
暂无公告