select * from test group by name,place having count(1)=(select max(cou) from (select count(1) as cou from test group by name,place )t)
create table test ( name varchar(10), place int ) insert test select 'aa', 1 union all select 'aa', 1 union all select 'aa', 2 union all select 'bb', 1 union all select 'bb', 2 union all select 'bb', 2 union all select 'bb', 3 select * from testselect * from test group by name,place having count(1)=(select max(cou) from (select count(1) as cou from test group by name,place )t)name place ---------- ----------- aa 1 bb 2(所影响的行数为 2 行)
不知为何,name只有aa、bb时正确,增加cc时结果还是只有aa、和bb ?????
create table test ( name varchar(10), place int ) insert test select 'aa', 1 union all select 'aa', 1 union all select 'aa', 2 union all select 'bb', 1 union all select 'bb', 2 union all select 'bb', 2 union all select 'bb', 3 union all select 'cc',1 select * from testselect name,max([count]) [count] from (select max(name) name,count(*) [count] from test group by name,place)t group by t.name
虾米你要把place也选出来的嘛
我想死,没什么简单的方法嘛?这个可以找到,但是也太繁琐了吧... select a.name,b.place,b.count from (select name,max(count)maxCount from (select name,place,sum(1)count from test group by name,place)a group by name)a join (select name,place,sum(1)count from test group by name,place)b on a.name=b.name and a.maxCount=b.count
create table test ( name varchar(10), place int ) insert test select 'aa', 1 union all select 'aa', 1 union all select 'aa', 2 union all select 'bb', 1 union all select 'bb', 2 union all select 'bb', 2 union all select 'bb', 3 union all select 'cc',1 select * from testselect ttt.name,place from (select name,place,count(*) [count] from test group by name,place)ttt right join (select name,max([count]) [count] from (select name,count(*) [count] from test group by name,place)t group by t.name)tt on ttt.name=tt.name and ttt.[count]=tt.[count]
多谢指点,楼上两位的方法都通过。 但是有个新问题,能否同时列出另一字段的累计值?而且是要包含被过滤记录。如: name place money aa 1 10 aa 1 30 aa 2 20 bb 1 100 bb 2 30 bb 2 20 bb 3 20 得到: aa 1 60 bb 2 170还请再费心,加100分。
这样试试: declare @t table(name varchar(10),place int,[money] int) insert @t select 'aa',1,10 union all select 'aa',1,30 union all select 'aa',2,20 union all select 'bb',1,100 union all select 'bb',2,30 union all select 'bb',2,20 union all select 'bb',3,20 SELECT a.name,a.place,moneys = (select sum([money]) from @t where name = a.name) FROM (select name,place,count(*) as counts from @t group by name,place) a WHERE not exists (select 1 from (select name,place,count(*) as counts from @t group by name,place) b where b.name = a.name and b.counts > a.counts )
create table test ( name varchar(10), place int, money int ) insert test select 'aa', 1,10 union all select 'aa', 1,30 union all select 'aa', 2,20 union all select 'bb', 1,100 union all select 'bb', 2,30 union all select 'bb', 2,20 union all select 'bb', 3,20 union all select 'cc',1,200 select * from testselect ttt.name,place,[sum]=(select sum(money) from test where name=ttt.name) from (select name,place,count(*) [count] from test group by name,place)ttt right join (select name,max([count]) [count] from (select name,count(*) [count] from test group by name,place)t group by t.name)tt on ttt.name=tt.name and ttt.[count]=tt.[count]
(
name varchar(10),
place int
)
insert test
select 'aa', 1 union all
select 'aa', 1 union all
select 'aa', 2 union all
select 'bb', 1 union all
select 'bb', 2 union all
select 'bb', 2 union all
select 'bb', 3
select * from testselect * from test group by name,place having count(1)=(select max(cou) from (select count(1) as cou from test group by name,place )t)name place
---------- -----------
aa 1
bb 2(所影响的行数为 2 行)
?????
(
name varchar(10),
place int
)
insert test
select 'aa', 1 union all
select 'aa', 1 union all
select 'aa', 2 union all
select 'bb', 1 union all
select 'bb', 2 union all
select 'bb', 2 union all
select 'bb', 3 union all
select 'cc',1
select * from testselect name,max([count]) [count] from (select max(name) name,count(*) [count] from test group by name,place)t group by t.name
select a.name,b.place,b.count from
(select name,max(count)maxCount from
(select name,place,sum(1)count
from test
group by name,place)a
group by name)a
join
(select name,place,sum(1)count
from test
group by name,place)b
on a.name=b.name and a.maxCount=b.count
(
name varchar(10),
place int
)
insert test
select 'aa', 1 union all
select 'aa', 1 union all
select 'aa', 2 union all
select 'bb', 1 union all
select 'bb', 2 union all
select 'bb', 2 union all
select 'bb', 3 union all
select 'cc',1
select * from testselect ttt.name,place from (select name,place,count(*) [count] from test group by name,place)ttt
right join (select name,max([count]) [count] from (select name,count(*) [count] from test group by name,place)t group by t.name)tt
on ttt.name=tt.name and ttt.[count]=tt.[count]
但是有个新问题,能否同时列出另一字段的累计值?而且是要包含被过滤记录。如:
name place money
aa 1 10
aa 1 30
aa 2 20
bb 1 100
bb 2 30
bb 2 20
bb 3 20
得到:
aa 1 60
bb 2 170还请再费心,加100分。
declare @t table(name varchar(10),place int,[money] int)
insert @t
select 'aa',1,10 union all
select 'aa',1,30 union all
select 'aa',2,20 union all
select 'bb',1,100 union all
select 'bb',2,30 union all
select 'bb',2,20 union all
select 'bb',3,20
SELECT a.name,a.place,moneys = (select sum([money]) from @t where name = a.name)
FROM (select name,place,count(*) as counts from @t group by name,place) a
WHERE not exists
(select 1 from (select name,place,count(*) as counts from @t group by name,place) b
where b.name = a.name and b.counts > a.counts
)
(
name varchar(10),
place int,
money int
)
insert test
select 'aa', 1,10 union all
select 'aa', 1,30 union all
select 'aa', 2,20 union all
select 'bb', 1,100 union all
select 'bb', 2,30 union all
select 'bb', 2,20 union all
select 'bb', 3,20 union all
select 'cc',1,200
select * from testselect ttt.name,place,[sum]=(select sum(money) from test where name=ttt.name) from (select name,place,count(*) [count] from test group by name,place)ttt
right join (select name,max([count]) [count] from (select name,count(*) [count] from test group by name,place)t group by t.name)tt
on ttt.name=tt.name and ttt.[count]=tt.[count]