急急急！~~求条查询SQL

[ass_cjy]

一个表table_里数据有：
ID name num time shopp_id
1 aa 50 2010-03 1
2 bb 50 2010-02 2
3 cc 100 2010-03 1
4 aa 50 2010-03 1
5 bb 60 2010-03 1
6 dd 70 2010-02 2
7 qq 110 2010-03 2


找出num总和最大的name 根据条件查询 （time = '2010-03' and shopp_id='1'）
这里应该有两条数据 aa和cc


select * from (select sum(num) as num,[name] from table_ group by [name] )as bb
where bb.num=(select max(num) from(select sum(num) as num,[name] from table_ group by [name] ) as aa)

这SQL语句根据 name 来分组就查得出 但是。。。
后面加上条件（time = '2010-03' and shopp_id='1'）查不到任何数据
该怎么改？？？急急急 急！！！！！！！！！！！！

[ass_cjy]

.... 刚刚发完 就知道怎么改了...

来个人进来 接分

[丰云]

(select * from table where time = '2010-03' and shopp_id='1')
作为基础表，再查

[托大人]

group by

[CalvinDo]

[丰云]

[ws_hgo]

create table #PP
(
 ID int identity(1,1) primary key,
 name varchar(20),
 num int,
 time Datetime,
 shopp_id int
)
insert into #PP select 'aa',50,'2010-03-01',1
insert into #PP select 'bb',50,'2010-02-01',2
insert into #PP select 'cc',100,'2010-03-01',1
insert into #PP select 'aa',50,'2010-03-01',1
insert into #PP select 'bb',60,'2010-03-01',1
insert into #PP select 'dd',70,'2010-02-01',2
insert into #PP select 'qq',110,'2010-03-01',2

select A.name from
(
select name,sum(num) num from 
(
select * from #PP where time='2010-03-01' and shopp_id=1
) tt
 group by name
) A
join
(
select max(num) num from
(
select name,sum(num) num from 
(
select * from #PP where time='2010-03-01' and shopp_id=1
) tt
 group by name
) tt
) B
on A.num=B.num

name
--------------------
aa
cc

(2 行受影响)

[NO_STUFF]

这么快就写出来了

[jack15850798154]

[Quote=引用 6 楼 ws_hgo 的回复:]
SQL code
create table #PP
(
ID int identity(1,1) primary key,
name varchar(20),
num int,
time Datetime,
shopp_id int
)
insert into #PP select 'aa',50,'2010-03-01',1
insert into #PP sele……
[/Quote]
sql 版的牛人。