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

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


...全文
83 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
-狙击手- 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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧